aspose file tools*
The moose likes JDBC and the fly likes Help on Database Design Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help on Database Design" Watch "Help on Database Design" New topic
Author

Help on Database Design

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
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


and finally... If it's a reference material...



�Item Description (required) (ex. Oracle DBA Survival Guide)
�Publisher (optional)
�ISBN No. (optional)
�Type (required) (ex. Book, magazine, VCD, DVD, audio tape)
�Remarks (optional)
�Month / Day Purchased / Acquired (optional)
�Year Purchased / Acquired (required)
�Date / Time Created (required)
�Created By (required)



I have the following list of tables so far that relates to my problem...

items_tbl
items_list_tbl
item_type_tbl
departments_tbl
users_tbl

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!


SCJP 1.5
http://devpinoy.org/blogs/lamia/ - http://everypesocounts.com/
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30948
    
158

Timothy,
I agree that you should have them in multiple tables. You need to know different things based on the type.

You could have an item table with the common fields and the type. Fields specific to a type would go in the separate tables and then you do a join.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Brian Smith
Ranch Hand

Joined: May 20, 2005
Posts: 63
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.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
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
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Thanks guys! I thought that was the smartest thing to do!
 
 
subject: Help on Database Design