i m creating a database using sql server 2005
primary idea of our side would be to import xml feeds from providers
different supplier might have different representation of information
the issue is i have to design table to keep imported information
a few of the posts are fixed means all supplier items should have similar data from the feed like , title, code, cost, status, etc
however, many product have optional particulars like
one product have might color property other might dont.
what's the easiest method to store these type of scenario in to the database.
must i produce a table for mandatory posts along with other tables to carry optional column.
or i ought to i list lower all of the column first and set them in to the one table. (there may lots of null values)
there'll 1000's of items and database speed is extremely essential .
we are doing lots of product comparison from different supplier
our database is going to be something similar to www.pricerunner.co.united kingdom
i really hope i explain the idea well
Is dependent how you need to can get on.
While you say, speed is essential - but what exactly are you going t use individuals extra, optional, items of information? Must you store them whatsoever? Presuming you need to do, how frequently must you access them?
Basically, for a moment always have to a minimum of see if they are there, most likely easier to place them into one table. If you want to check anyway, may as well have it over and done with included in the initial query.
If, however, you are able to usually run without disturbing to check on of these extra pieces, and only have to bother when specilly asked for, then it may be easier to place them right into a different table. The join (or subsequent research) is going to be costly - a lot more costly than tugging nulls for empty posts - but when it is extremely infrequent, would most likely are less expensive in runtime execution over time.
Also keep in mind the tradeoff kept in storage and transport terms - storing plenty of empty fields takes some space, and delivering back plenty of empty fields takes network bandwidth.
If disk space isn't a concern, but bandwidth is, result in the application is carfully made to minimse unecessary searches, after which with tight queries you are able to keep extra (optional) data, although not pass it back unless of course it's asked for.
So, it truly all is dependent on what's vital that you you. Once guess what happens your overriding design concerns are, you will be aware which compromises to create to deal with individuals concerns at the fee for others. A balanced exercise.
1000's of items (so 1000's of rows.) Thats not really many whatsoever, which means you could normalize the the not compulsory data to some couple of separate tables without getting a dramatic impact on query time.
I'd say place your indexes within the correct place, optimize your queries, make certain you've filegroups separate nicely, etc (only the usual regular old database stuff) and you ought to be great.