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 |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2009-10-19 : 16:05:23
|
| I am hoping for some input on optimal data model for a particular project. Any thoughts you have are most welcome. One primary goal is scalability without significant data model changes through time.I'm building a database to store an inventory of items.Each item represents a real-world thing, and is of a specific Item Type. So an item might be of type 'car' and a different item might be of type 'wristwatch'.The types will be stored in two tables: itemtype and itemtype_characteristics.One Item Type might have 4 characteristics, each characteristic having a data type 'Manufacture Date'(a date);owner name (a string); dial_count (an integer); 'Design Date' (a date).Depending on the item type, there will be a different set of characteristics, the data types will be limited to a subset of the valid SQL table column data types.The Items table will contain a row for each item, with an unique primary key, and a datatype key, (foreign key to the datatypes table).There will eventually be thousands of item types, and I definitely don't want to make a table for each type.One idea I have is having a separate table for each characteristic data type. Each table having an Item key (foreign key to items table); a characteristics key (foreign key to characteristics table) and a value column to store the date/integer/string/etc. This would result in my having a set of tables, one for each data type ItemIntegers; ItemStrings; ItemDates; ItemMoney etc.It would allow me to only have to update the data model if I need a new data type. I would have a set of procedures add/update/delete the items.Another option would be to have a single table to store the Item Characterstic Values. It would have a column for the Item key (foreign key to items table); a Characteristics key (foreign key constraint to the characterstics table) and a set of columns, one for each of the data types I need. This table would be wide, and more wasteful of space, but might be faster than the separate table for datatype model.Thanks again for your thoughts/insights and any other ideas that might also meet my needs in an elegant/scalable manner. |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
|
|
|
|
|
|
|
separate table for each data type?