feelgood

I'm in the middle of a major school project that has been running from previous semester. We're in the design stage of a information systems which is probably a mickey mouse compared to the ones in the "real" world.

Anyways, my group and I are having disagreement regarding normalization of several database tables in MySQL.

Basically, what we have right now is 2 tables from the Conceptual ERD. They are Job Order and Product.

Job Order contains information about Products that are being made in the production line while Product contains information about products being sold as well parts needed to make the product. Parts can also be sold as a product.

Initially, after I reviewed the requirements regarding the Job Orders, I realized that there was no way for the user of the system to select part from a list of parts in the system and add it to the job order. Obviously, you need to have parts in order to make a complete product.

The original table looks like

{Job Order} >o-------------|- {Products}

This table was made based on that the Job Order table would contain the Product ID which is linked to Products.

When we factor in the fact that we need to have parts to be used for Job Orders. The table will look like this

{Job Order} >o-------------o< {Products}

This works fine in conceptual ERD but when we moved to implementation. The table would look like

{Job Order} >o------|- {Parts} -|-------o< {Products}

The justification for this was that parts table would contain Job Order ID, Product ID and Part ID. There is no product ID information in the job order table. The part id is actually the same information as product ID in the product table. I disagree with this.

I believe that, we don't actually need the product id in the parts table, but instead, put it in the Job Order table and have a Many to One relationship to the products table and keep everything else the same.

So, my idea will look like this:

{Job Order} >o------|- {Parts} -|-------o< {Products}
>o--------------------------|-

My main justification is that, if I wanted to know what the job order is making, why would I need to go through 2 different table (Parts -> Products) to find that out where I could simply just make a link directly to the products table.

My group member and course instructor is against me while I have my database instructor on my side.

What's your opinion?

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war

Rawb

At first, I agreed with you, but now I think I may have either seen the light, or misunderstood the question. I'm gonna assume that I understood you perfectly, for the sake of argument. So, here goes.

I'm assuming that a Product is something that is reusable, or that has a quantity. I'm gonna assume that you guys are making furniture, so I will say that a product is a 3/4" steel caster, product id #1, part number "XYZZY4".

Now, if you have a job that requires you to build, a couch, you need the part table to link to the product table. Because that caster is going to have to be used on multiple jobs, and you are going to need to have multiple parts linked to that job.

Think of it this way, the jobOrder is the invoice, the parts table is the list of line items on the invoice (for the parts, although, you could theoretically make a Product for 1hr of labor and throw it in there too.) or the list of stuff in your shopping cart. The Products table is the catalogue of what you sell.

So, here's the quick example
jobOrder :
{
jobId = 1, jobName = "Couch for Joe"
}

Parts {
partId = 1, jobId = 1, productId = 1, qty = 4
partId = 2, jobId = 1, productId = 4, qty = 1
partId = 3, jobId = 1, productId = 3, qty = 8
partId = 4, jobId = 1, productId = 2, qty = 1
}

Products {
productId = 1, productName = "3/4 inch steel caster"
productId = 2, productName = "Fluffy stuffing"
productId = 3, productName = "Framing wood"
productId = 4, productName = "Cloth covering"
}


Am I making any sense?

feelgood

Yeah, you're on the right track. Except, I think that the Product ID in the Parts table should be in the Job Order table. It should reduce the redundency of the data in the parts table.

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war

Rawb

Now, the proper question here is: How?

How exactly are you going to link the jobOrder table to the product table? Just remember, you are going to have multiple products per jobOrder, and products are going to have multiple jobOrders. I would tell you the answer, but then that would be cheating.

feelgood

jobOrder :
{
jobId - primary key
jobName
productid - foreign key refering product id in product table
}

Parts {
partId - primary key, forieng key refering productid in the product table
jobId - primary key, foreign key refering jobid in job order table
qty
}

Products {
productId - primary key
productName
}

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war

Rawb

Okay, since I have found new lost faith in college and it's ability to teach I'm gonna go straight to the hunt.

The productId column in your jobOrder table can only store one entry. It can only be a foreign key to link to one other row. Not multiple. It's still really just an int on your side, just happens to (unless you are in mysql) have some constraints wrapped around it so that you can't set that int to anything you want, just stuff that actually exists in the other table.

So what you need when there are multiple-multiple links on both ends of the linkage is a glue table. A glue table is a table that sits inbetween two tables and provides links between them because a direct linkage isn't possible. Say for example if you were building a database of livejournal entries and a table that had possible emotions for those livejournal entries. Now, people can feel multiple emotions at one time (well, people with feelings can...) you could be happy and excited, angry and sad, angsty (I made this one up, can you tell?) and everything.

So what your schema would look like is this:
journalEntry {
journalId - PK
personId - FK to person table (not shown)
journalDate - Date
journalEntry - Text
}

emotion {
emotionId - PK
emotionName - Text
}

journalEmotionGlue {
journalEmotionGlueId - PK (I have a love/hate relationship with these PK's)
journalId - FK to journal table
emotionId - FK to emotion table
}

So that glue table in the middle is the one that allows you to link up multiple emotions to multiple journal entries. Without it, you couldn't have multiple emotions linked to your livejournal posting, so it would just be angst, angst, angst post after post, because a post could only do a link via a FK to a single emotion.

feelgood

I just realized now, in your example of the table in your first post. I didn't realized this until your latest post but, here's the table what it should look like which is what the group wanted to go with:

jobOrder :
{
jobId = 1, jobName = "Couch for Joe"
}

Parts {
partId = 1, jobId = 1, productId = 2, qty = 4
partId = 2, jobId = 1, productId = 2, qty = 1
partId = 3, jobId = 1, productId = 2, qty = 8
partId = 4, jobId = 1, productId = 2, qty = 1
}

Products {
productId = 1, productName = "3/4 inch steel caster"
productId = 2, productName = "Fluffy stuffing"
productId = 3, productName = "Framing wood"
productId = 4, productName = "Cloth covering"
}

The differences is that the productID in the parts table is all the same for each job order. That's too much redundency. I wanted to move the productID column into the job order table which will reduce the redundency. The business rule regarding job orders is that there is 1 product per job order, there can be more than 1 part per job order.

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war

welshbyte

This is telling me that the same product is listed against one job order several times with different qty's. This wouldnt happen would it? More likely this would happen:

Parts {
partId = 1, jobId = 1, productId = 2, qty = 14
partId = 2, jobId = 1, productId = 4, qty = 5
partId = 2, jobId = 2, productId = 3, qty = 1
partId = 2, jobId = 3, productId = 2, qty = 12
}

..etc

What i'm saying is, a quantity of one product would only be listed for one job in this table once.

Surely this is how it would work in practice? Or am i not getting the way the system works.

__________________
There are only two industries that refer to their customers as "users". - Edward Tufte

feelgood

It's a manufacturing company. A job order is required in order to start producing a particular product.

The different quantity is referring to the different part used to make the product for the job order.

Don't ask me, I didn't make up the case study

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war

Rawb

Okay. Now I understand more clearly the problem.

So definitely add productId to the jobOrder, and remove it from the part table.

Now, the real question is do you use the same parts for the product every time, if you do, you could link them to the product instead, like a list of ingredients that are used to make a product. Now if parts can be used to make multiple products, you will need a glue table.

So basically it boils down to this now:
Product <--- JobOrder (forgive my messy semantics, it means that joborder has a fk to Product)
Product <--- Glue? <--- Part
-- or --
Product <--- JobOrder
JobOrder <--- Glue <--- Part


Again, I'm probably misunderstanding the project, but that's my solution from how I see it.

feelgood

Yeah, I was just wondering if my view on the whole part,job order, and product table was valid...

I have been thinking about the making a table that contains a list of ingredients.

Probably something like

Product_Ingredient {
PartID - PK, FK to Product
ProductID - PK, FK to Product
qty (quantity needed for each quantity of product?)
}

__________________
Looking out the window, that's an act of war. Staring at my shoes, that's an act of war. Committing an act of war? Oh you better believe that's an act of war