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?
 
mfsamuelConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.