July 24, 2009 at 12:44 am
Hi,
I have a peculiar requirement. We have a table that has around 20 fields specifying the characteristics of a product. Our client has come up with a requirement that the number of columns will be increased up to 100 columns. The client will be giving us a file to import and we will be importing the data to the table. If a new column is found in the file, then we need to add the column to the table and then import the data. There is another catch here. We have a search option, where a keyword would be entered. It can be any of the 100 characteristics of the product. So we need to search through all the 100 columns to find the text and then the field name. I feel this would affect the performance. Say if i have 100 records, so a search will happen in hundred columns in 100 rows which is equivalent to 100 * 100. I have my indexes all in place but still not happy in this model.
What is the ideal way of building dynamic columns. Any help would be greatly appreciated.
July 25, 2009 at 6:26 am
You might consider storing the characteristics in a separate table. This separate table would have a key and a value for each characteristic, linked back to the main table by a foreign key relationship. This approach also lends itself well to full-text indexing.
July 25, 2009 at 10:46 am
I tend to agree with Paul. Adding columns to support different characteristics might be breaking a normal form and repeating data for no reason. It is also harder to search across columns.
If you provide more information about what you mean about characteristics and some examples, perhaps we can help you model this better.
July 29, 2009 at 2:22 am
Hi Steve and Paul,
Thanks for your prompt reply and sorry for replying back late.
The table that i have stores the details of automobiles. There are lots of qualifiers / details that needs to stored. Currently we have around 15 fields like make, model, color, year, etc. The extra fields that will get added will be cab size, bed size, fuel capacity, mileage, seat color, etc. Just giving you few names out of the whole lot. Our client says that the number of qualifiers might exceed to 100 to 130 in total.
The part where i feel the challenge lies is the adding of columns and also during the search section.
In the search section, the user might only type Mercedes Benz and the query should first parse through all the coulmns and find the Mercedes Benz string and then its coulmn name, which i feel will slow down the performance very much. I also dont feel that this is the right design.
Your suggestions would help me
regards
krishna
July 29, 2009 at 2:45 am
Having read your reply, I'm not sure you read mine...?
July 29, 2009 at 8:23 am
I agree. It doesn't appear that you understand how SQL Server is optimized.
coding for:
ID Make Model Color
--- ------ ------- -----
1 MB 450 Black
2 Chevy 2500 White
and writing code to search those columns is not slower than.
ID Criteria Value
--- -------- -------
1 Make MB
2 Make Chevy
2 Model 2500
....
The code to search multiple values from the 2nd can be as quick, or quicker, than the first. And it's cleaner.
While there might be certain times that you add columns, or add other tables for lookup, name/value pairs can be very efficient in terms of storage and query speed. SQL Server's power is often in searching through lots of rows for matches.
July 29, 2009 at 10:01 am
Hi Paul & Steve,
Thanks again for your replies.
I have my table already designed as you have mentioned and I am following second example model that steve has mentioned.
I wanted to check if i am following the right model and a normalized one.
There is just one catch here. If the characteristics increase, then for every record, there will be as many inserts as the characteristics. Will this effect the performance ? Although most of the Insertions are dfone from an XLS file (import) and the insertions are not very frequent.
Thank you once again for your help and for wonderful explanation.
Paul --> hope you are not irritated by my previous reply. I just wnated to give an example so that you can help me understand better.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply