October 15, 2006 at 4:31 am
Hey guys, Sorry in advance for a wordy post but Im hoping some of you db guru's can help.
Im new here and this is my first post. Did a bit of hunting for this over the internet and didnt find anything. I am tasked with thinking up a design for a database that will hold inventory of 'ANY' sort.
The database could have vehicles, food, clothing, or any sort of item in it basially and many specialized items often require specialized fields.
For example if the client is selling vehicles, the people using the db would want to be able to search by year, model, make, color etc, whereas a food item may be searched for type, expiry data, price etc. Similarly the idea is that the client should be able to specify at run time on the application what a new product is gonna have as its key fields, and the db should then store them in the inventory table.
So my idea so far for this was to have an inventory table with 10-20 generalized columns named c1 through c20. Then next is a lookup table which holds the names for those columns for each inventory type...so it could have three fields,
Inventory type, columnName, columnDescription. This way if price of Clothing is to be displayed then the app will go to the lookup table, retrieve all rows where inventoryType = clothing, and then use the columnName and columnDescription to figure out which column it needs to retrieve from the inventory table.
I hope I described the problem at hand well enough. Im new at database design so im not sure if this is the 'best' way to do it. Since i`ll be coding this application with minimal help from start to finish I wanna make sure the database is designed as well as possible right from the start, so I dont need to go back and change it all the time.
Any advice ? is that a good way to do it ? Are there any better ways ? Any links I can browse that would give me advice or tips on such database design ?
Regards,
Abdullah
October 16, 2006 at 3:15 am
Hi
Is this a SQL2005 Database? Coz if it is you could use the new xml data type. It has been added to solve this sort of problem.
Regards
Mike
October 16, 2006 at 4:40 am
If you didn't want to go down the XML route, instead of having twenty (or however many) generalized fields on one table, which may or may not be used for any particular object type, normalize the design out so you have a top level table (InventoryItems) which stores information used by ALL objects (eg name, id) and have another table, called something like InventoryItemAttributes, where you'd store one row for each of those attributes such as Color, Year etc which are unique to a particular type of inventory. Your lookup table would be foreign keyed on the InventoryItemAttributes table so that each row in it has an identifiable purpose. Your table design would look something like this:
Table: InventoryItems
ItemID
Name
etc.
Table: InventoryItemsAttributes
ItemId -- to link back to the InventoryItems table entry
AttributeId -- to allow any number of attributes per Inventory Item. You don't need this if there can only be one entry for each attribute type
AttributeTypeId -- to link to the lookup table
AttributeValue
Table: lookup
AttributeTypeId
AttributeName
etc
This design will stop you using unnecessary space (you'll only be storing say 2 rows for inventory items which only have 2 attributes) and also remove the arbitrary limit of 20 attributes you're currently imposing (you'll be able to store say 200 rows for inventory items which need that many attributes). Look up 'database normalization' on Google for some background on database design and what normalization is all about, and why it's critically important.
October 16, 2006 at 9:53 am
Thanks for the replies. I will definetly look into the XML datatype and see If i can pick that up relatively fast.
Ian, your solution is also much better than mine. Thanks for the help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy