Hi guys, I don't know where place this question... But I'm posting it anyway coz I'm in a real hurry... Well... I'm a doing an inventory system - like application... It functions a little different though... Now I categorize my items as Hardware/Software/References. You get the picture? This is an inventory system for an IT department. I need to design my database as such, if it's
a hardware, I capture the following information...
�Item Description �Manufacturer �Model �Serial No. �Remarks �Month / Day Purchased / Acquired �Year Purchased / Acquired �Date / Time Created �Created By �Date / Time Modified �Modified By
and if it's software...
�Item Description (required) (ex. Microsoft Visual Studio 2005) �Publisher (required) (ex. Microsoft, IBM,�, Other publisher) �Media Type (CD, DVD, Diskette, etc.) (required) �Quantity (required) �Remarks (optional) �Month / Day Purchased / Acquired (optional) �Year Purchased / Acquired (required) �Date / Time Created �Created By
so far... The Hardware, Software and Reference entities are in a single table... I'm thinking if I should have them in separate tables... But that would rathe turn to be hard to maintain... If you need to see the screenshot of my database relationship (in ms access), just tell me... Thanks!
I would create a table that holds the common information and then create three other tables to hold the fields that are different. You can then join these three tables to the common table using a 1:1 relationship.
Things like the ItemDescription and Publisher/Manufacturer would go into the Common table.
I would also consider using a using a surrogate primary key rather than a natural key or a combination of fields to form the primary key. In Access this would be an Autonumber or for Oracle use a Sequence.
When you have similar but not identical entities (often through inheritance in the object world) there are three common choices in the data model:
1) Put all the fields for all the entities in one table. Every row will have some empty columns for the types which this row isn't.
2) Put the common fields in one table, the unique ones in a new table per type, just as Brian suggested. You have to deal with two tables to get any entity.
3) Make a table per type, period. You have tables with duplicate columns.
Option 2 is the most "normal" for a relational model, and is probably a good choice until you find some compelling reason not to.
My team is big into surrogate keys. We have a Java key generator that is based on UDB sequence numbers plus a per-server component to make sure things are unique across the cluster with a minimum of database hits. [ December 29, 2005: Message edited by: Stan James ]
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Joined: Sep 18, 2005
Thanks guys! I thought that was the smartest thing to do!