[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

Table Relationships

I have an inventory database in progress.  I have 2 tables: assemblies and raw materials.  The assemblies table and the raw materials tables could be related via a junction table (because any number of assemblies can share the same raw material).  I understand this far.

BUT, I need to build functionality to allow assemblies to contain other assemblies plus raw materials.  For example, a raw material might be a bolt.  An assembly might be a nut/bolt set.  Another assembly might be a bolt-nut assembly plus something else (either raw material or another sub assembly).

I know that I need assemblies to contain assemblies due to the cascading nature of how we update our parts--and Quickbooks Manufacturing edition does this and it's way more efficient than disallowing assemblies to be built with raw materials plus other assemblies.

What is the best way to do structure this in MySQL?  If an assembly can contain other assemblies AND raw materials, how do I create the proper table structure for this to work?  If it were just assemblies and raw materials--the junction table would work--but I don't know how to do it this way when it can be raw materials AND/OR other  assemblies contained in an assembly.

PLEASE no generic links to database design websites.

Thanks.
0
rowejd
Asked:
rowejd
  • 2
  • 2
1 Solution
 
Steve BinkCommented:
As much as you don't want links, Google is your friend.  Use it.

The concept you are describing is a self-join, and requires a slight paradigm shift in how you look at your data.  In this sense, you are not dealing with assemblies and raw materials.  You are dealing with parts.  Everything is a part.  Even finished assemblies, as you pointed out in your post, can be a part.

tblParts
--------------------
PartID
PartName

tblPartsRequired
---------------------
PartReqID
PartID
PartOfID

Say you have an 'assembly' with PartID=1.  To get all the parts required for that assembly:

SELECT * FROM tblPartsRequired WHERE PartOfID=1

Imagine that query returns 3 parts, PartID's 2, 3, and 4.  Parts 2 and 4 are the bolt and the but, but part 3 is a circuit board which must be produced.  PartID=3 will have entries in tblPartsRequired, so you have to cascade to a second query:

SELECT * FROM tblPartsRequired WHERE PartOfID=3

As you can see, this can lead to a virtually limitless cascade.  Your parts 'tree' can be as small or as large as necessary to detail every part required for a project.  To find all the parts, follow this flowchart:

 - select parts required for PartID=x (yields PartID's y and z)
 - for every returned required part:
     if PartID=y has sub-parts,
         go back to step 1 using part y,
     else
         add 'final' part to the list
0
 
rowejdAuthor Commented:
I did and do search google--but I've found that experts can usually give me much better guidance esp. when I don't know exactly how to phrase my search.  And, a moderator here on EE told me to say "no links" because I was getting lots of people responding with generic links to certain questions that had nothing to do with what I asked.  THis prevented actual experts such as yourself from even addressing the topic, so he suggested I start saying "no links".

Thanks for taking the time to respond...I posted a follow up question related to this same issue here if you're interested:
http://www.experts-exchange.com/Database/MySQL/Q_22897345.html

So in your solution above, the only unanswered question I have is how to structure the keys?  Is PartReqID the primary key and then PartID and PartOfID foreign keys?
0
 
Steve BinkCommented:
I understand, re:links.  I use that response as a matter of course because too many people don't try to read.  I'm glad to hear you're not one of them.  :)

You're on target with the keys.  The odd thing about this setup is that both foreign keys point to the same field in the same table.  One key tells you where to find the description for this part, and the other tells you which other part/assembly is using it in this instance.  To decrease duplicate data, I would add a quantity field:

tblPartsRequired
---------------------
PartReqID
PartID
PartOfID
QtyReq

Now you can know you have 16 of bolt 'a' without listing the entry 16 times.  Implementing a unique key on PartID-PartOfID will provide a handy index and safety net for your app.

0
 
rowejdAuthor Commented:
you rule.  thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now