It's not a secret anymore!
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "Help on Database Design" Watch "Help on Database Design" New topic

Help on Database Design

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 751
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
�Serial No.
�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...


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
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, 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: 751
Thanks guys! I thought that was the smartest thing to do!
I agree. Here's the link:
subject: Help on Database Design
It's not a secret anymore!