asked on

How to create a mySQL advance schema?

I have created tables before and used joins but now I would like to expand on that a bit.

I am trying to develop a Parts DB. It has a "tblParts", "tblRequired_items" (to make the parts), and "tblRaw_materials" table (to make the items).

Example:

tlbParts
========
part_id
part_name
part_cost
part_qty_produced
part_description
part_required_items (FK?)

tblRequired_items
=================
required_id
required_name
required_cost
required_qty_produced
required_description
required_raw_materials (FK?)

tblRaw_materials
================
raw_id
raw_name
raw_cost
raw_description

A Part my require another Part and/or "Required Items" to make it.
Some of the "Required Items" may need other "Required Items" and/or "Raw Materials" to make it.

From what I understand, I am suppose to have intermediate tables. Is that correct?

i.e.

tblParts_required_items
=======================
id
part_id
required_id

tblRequired_items_raw_materials
===============================
id
required_id

How do I insert data into all those tables?
How do I query those tables? Example: How would I query to see what "required items" and/or "raw materials" make up a part?

ASKER CERTIFIED SOLUTION

mfsamuel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mfsamuel

by the way, tblRequired is a tree, and each tlbParts is a node on the tree. to retrieve this information you will need to use a recursive function to iterate through the tree.

So run a query to see all the parts under your current node. You will need a stored porcedure or some other language that can make recursive calls to get the entire tree.

SELECT tlbParts.* 
FROM tblRequired 
INNER JOIN tblParts ON tlbParts.id=tblRequired.required_part_id
WHERE tblRequired.id=[the id of the parent node]

Open in new window

mfsamuel

here is an alternate method of storing a tree...
http://www.sitepoint.com/article/hierarchical-data-database/2

this artical lists 2 methods, the first method is what i have above. if you want the alternate method, let me know and I can change the code. the second method requires some more code to build the left/right index values.

advantages to the method I have above is it makes inserting data, and quries simple to understand. the alternate method using left/right keys is faster to process on large trees.

taylort2

ASKER

Thanks for the link. I was thinking of doing it like the first method. I will have to adjust the code to work with the tables I already have though.

taylort2

ASKER

Is the required_part_id (FK) also link to the Parts id or to a Required id?

mfsamuel

technically everything is a part from a database perspective (even raw materials). You can have whatever combination you want.

the way this is stored, there is no required _id. required_part_id links to the id in the tblParts, so does part_id. both link the the same field, but one means a parent node the other means a child.

tblParts is a list of all parts/raw materials/required parts, just provides a single table to join against to pull the information you want.

tblRequired is a mapping table. All the parent nodes (part you are building) are in part_id, and all materials needed to make that are in required_part_id. multiple required_part can make a single part. hope that clears it up.