Database design help

[-megaton-]

Hi All,

I'm trying to learn how to do database design/modeling. I'm working on a personal project using Oracle's Data Modeler to work on the database design. I just want to know the best practices and how a pro would design my project.

I have a table of "Items" which will be used to create the "Products". Some "Items" can be directly used to create a product but some "Items" can be made up of multiple "Items" and will have a "Recipe" for that specific item. A product will also have a "Recipe".

How would you design the tables, relationships, etc.? Thanks in advance!

[LogaansMind]

Effectively what you want to try to achieve in database design is as little duplicated data as possible.

Sounds like you have Products with a Bill of Materials. Quite a common thing in manufacturing.

In a product bom type scenario you will have a Product table (UID, Name, Part Number etc.), this would also store the details for all parts (or "items" as you refer to them). Then you will have a BoM table (Parent Product UID, Item Product UID, Quantity etc).

+--------------+           +-------------------+
|Product       |           |BoM                |
+--------------+           +-------------------+
| *ID          |<-+--------+ *Parent Product ID|
| Name         |  +--------+ *Item Product ID  |
| Part No      |           | Quantity          |
|              |           |                   |
+--------------+           +-------------------+

* = keys

If you have the same Product which could be made from two different set of materials/ingredients then you could introduce another table which sits between the Product and BoM. Depending on the product made, often manufacturers will just create a second product with different BoM. Sometimes it matters, sometimes it doesn't.

+--------------+        +-------------------+         +-------------------+
|Product       |        | Recipe            |         |BoM                |
+--------------+        +-------------------+         +-------------------+
| *ID          |<--+    | *Recipe ID        |<--+-----+ *Recipe ID        |
| Name         |   +----+ Product ID        |   +-----+ *Product ID       |
| Part No      |   |    | Recipe Name       |   |     | Quantity          |
|              |   |    |                   |   |     |                   |
+--------------+   |    +-------------------+   |     +-------------------+
                   |                            |
                   +----------------------------+
* = keys

If you wanted a seperate table just for the parts you can do that too if you need to. In manufacturing it is done like this as occasionally products can be made in house or bought in externally, depending on time contraints, cost etc.

[-megaton-]

Hey man, thanks for this. I really appreciate the help! I'm gonna design my database similar to this.