2

I'm new in programming and database. Currently working on an inventory project which involves Department (Physics, Chemistry etc), Category (Physics -> Heat, Chemistry -> Organic) and actual Lab Items (Physics -> Heat -> Match Sticks, Chemistry -> Organic -> Hexane Solution). How should my database diagram look like such that I can do a search of list of items based on Department and Category and while adding item, they are classified under the correct Department and Category. I'm taking of creating a Junction table (Department-Category-Item) linking to Department, Category and Item Details(doesn't contain DeptID and CatID) table.

Am i on the right track??

Hope someone can help to clarify.

Much thanks in advance.

Chris

chrisf
  • 23
  • 2

1 Answers1

0

Yes you are on the right track. If it's right that:

  • One departmant can have a couple of categories and one category is always connected to one department
  • One Category can have a couple of items and one item is always connected to one category.

Then you should do some model like this:
Department

Id | Name
---|-------
1  | Physics
2  | Chemistry

Category

Id | DepartmentId |Name
---|--------------|-----
1  | 1            |Heat
2  | 1            |...

Item

Id | CategoryId | Name
---|------------|------
1  | 2          | Match Sticks
2  | 1

You'll get your "junction" with the foreign keys (DepartmentId, CategoryId). You'll add items corrctly by entering the respective foreign key.

P.S.: If one of your relations is n:n (like a item can be in a couple of categories) then you'll need a new entity/table between those.

timbmg
  • 3,192
  • 7
  • 34
  • 52
  • Thanks for the quick reply and the hypothesis is right! Glad I'm on the right track. For Item table, can i replace it with ItemTransaction instead as it will hold information pertaining to the qty, supplier etc. Is there a need to break down this ItemTransaction table to Item and ItemDetails instead? What will be the benefit? – chrisf Jan 26 '15 at 03:34
  • If you want to save information about suppliers you should have an additional table. Because one supplier can have several items. – timbmg Jan 26 '15 at 07:21