I'm working on a project in which user can create their own web services from UI by simply selecting number of fields (Max 200) and defining datatype for each field. I'm considering following table structure for the same :
ServiceMaster +-----------+-------------+-------------+ | ServiceId | ServiceName | FieldsCount | +-----------+-------------+-------------+ | 1 | Service1 | 2 | +-----------+-------------+-------------+ | 2 | Service3 | 3 | +-----------+-------------+-------------+ ServiceDetails +------------------+-----------+--------+---------+-------+-------------+ | ServiceDetailsId | ServiceId | Field1 | Field2 | Field3| Field...200 | +------------------+-----------+--------+---------+-------+-------------+ | 1 | 1 | 5 | Active | NULL | NULL ... | +------------------+-----------+--------+---------+-------+-------------+ | 2 | 2 | High | 9.0 | 7 | NULL ... | +------------------+-----------+--------+---------+-------+-------------+ | 3 | 1 | 2 | Running | 7 | NULL ... | +------------------+-----------+--------+---------+-------+-------------+
I'm not adding DataTypeDetails table here for clean and simple code, but yes I have one table to hold datatype for fields. If you see above table structure, I can define Varchar or any string datatype for all Fields, but if I do that then while applying filters on table I have to CAST fields data with it's original datatype. Like, 9.0 (Float) or 7 (Int) etc. I think this will slowdown query if I will use TYPECASTING for all columns to apply filters. This able may have Billions of records or more.
So is there any alternate way to achieve the same with better performance.