I agree with Maess - if you have distinct entities, each of those ought to have their own ID columns. But I have a strong objections against using GUID's for your primary keys - or more specifically - for your SQL Server's clustering keys.
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
You really need to keep two issues apart:
1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.
2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.
As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.
Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.
Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
Quick calculation - using INT vs. GUID as Primary and Clustering Key:
- Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
- 6 nonclustered indexes (22.89 MB vs. 91.55 MB)
TOTAL: 25 MB vs. 106 MB - and that's just on a single table!
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.
I would go with number 1. Having a separate table for each vendor or each product type will be a nightmare when trying to run reports that need data from multiple product tables. You don't want to have to create a new table each time you add a vendor or product type.
I understand your inclination coming from a spreadsheet, but with the ability to query the records easily, separating the products into multiple tables is not the way to go.
I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.
To prevent this, attributes should be in a separate table.
For example, a partial table definition may be as follows:
products
-- id
-- name
product_attributes
--product_id
--name
--value
attributes.product_id is a foreign key of products.id.
To further normalize it you could define the attributes in a separate table and have an attribute_id field instead of attribute_name:
attributes
--id
--name
product_attributes
--attribute_id (foreign key of attributes.id)
--value
(edited for spelling)
Best Answer
Congratulations, someone has reinvented EAV (Entity Attribute Value). Please study up on the subject with them.
The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them.
I would also ask: If someone wants to put everything in a single EAV, for what purpose does someone still want to use SQL Server? What benefits does SQL Server provide at that point over other approaches, particularly transactional databases (one example is a simple structure where you have "key columns" then "data columns" and you always look up data via the key).
Wikipedia has a reasonable entry.
A couple interesting notes from Wikipedia (the second of which applies in particular to the "only one Value column in the whole database" idea you listed someone as having proposed):
Tony Andrews covers EAV as well as OTLT with an example of an EAV query that gets a bit cumbersome.
Aaron Bertrand argues for the EAV approach in select cases.
Aaron Bertrand has perhaps the best bullet list:
Cons from Aaron:
Pros from Aaron: