File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help on Database Design

 
Timothy Sam
Ranch Hand
Posts: 751
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33689
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Brian Smith
Ranch Hand
Posts: 63
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8791
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Timothy Sam
Ranch Hand
Posts: 751
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks guys! I thought that was the smartest thing to do!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic