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?
taylort2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfsamuelCommented:
Here is what I would do.

tlbParts
========
id (PK)
name
cost
qty_produced
description
type  <== This can be type Part, Required, Raw

tblRequired
========
id (PK)
part_id (FK)
required_part_id (FK)

This is all the structure you need.  You can query and insert into this with far less trouble.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mfsamuelCommented:
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

0
mfsamuelCommented:
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.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

taylort2Author Commented:
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.
0
taylort2Author Commented:
Is the required_part_id (FK) also link to the Parts id or to a Required id?
0
mfsamuelCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.