Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fpbaum
Starting Member
2 Posts |
 Posted - 2008-09-04 : 12:33:16
|
| I'm designing a db for spec sheet data from a product catalog and would like some opinions on the design, or links to other discussions on this.Each part in the catalog is uniquely identifiable by part number and each part fits into a category (i.e. laptop, monitor, printer, cable, etc.) though it doesn't necessarily fit neatly into its category so I can't rely on static spec definition where a single table is for a single part category and a single row contains all the spec info for a specific part.This leaves me looking for a design utilizing attribute/value pairs in 1 table with columns...ID, ATTRIBUTE, VALUE(varchar), TYPE...where the values are stored as the same data type (varchar, requiring casting upon retrieval), or attribute/value pairs in multiple tables...Table1 = ID, ATTRIBUTE, VALUE(int)Table2 = ID, ATTRIBUTE, VALUE(float)Table3 = ID, ATTRIBUTE, VALUE(varchar)Table4 = ID, ATTRIBUTE, VALUE(bit)...where each table has values of a unique data type.The first option sounds inefficient due to the need to keep track of data type and then cast. The second option sounds perhaps equally inefficient since multiple tables must be queried and either unioned in the query (which doesn't work so well with float and varchar) or unioned and sorted in server side code.Can anyone shed some light on what you think the best approach is, not limited to the options I spelled out? I'm sure this is a common task that perhaps has a standard solution? Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 12:57:43
|
| i think first option is better. you could store all values as varchar in a single table. |
 |
|
|
fpbaum
Starting Member
2 Posts |
Posted - 2008-09-04 : 14:09:04
|
| I didn't really want to use that option at first because I wanted to avoid potential fragmentation (most data is ints and small char strings) and casting, but the more I look at it I think fragmentation will be minimal since the db will be updated infrequently and casting from varchar is mostly implicit and so not too big a deal. I think I just needed a nudge. thanks for the opinion. |
|
|
|
|
|
|
|
|
Suggestions, store attribute/value with mixed type