CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-databasedatabase-schema
Brian Leach
Brian Leach

Reputation: 4154

database schema one column entry references many rows from another table

Let's say we have a table called Workorders and another table called Parts. I would like to have a column in Workorders called parts_required. This column would contain a single item that tells me what parts were required for that workorder. Ideally, this would contain the quantities as well, but a second column could contain the quantity information if needed.

Workorders looks like 

WorkorderID   date   parts_required
1             2/24      ?
2             2/25      ?
3             3/16      ?
4             4/20      ?
5             5/13      ?
6             5/14      ?
7             7/8       ?

Parts looks like 

PartID    name           cost
1         engine         100
2         belt           5
3         big bolt       1
4         little bolt    0.5
5         quart oil      8 
6         Band-aid       0.1

Idea 1: create a string like '1-1:2-3:4-5:5-4'. My application would parse this string and show that I need --> 1 engine, 3 belts, 5 little bolts, and 4 quarts of oil.

Pros - simple enough to create and understand.

Cons - will make deep introspection into our data much more difficult. (costs over time, etc)

Idea 2: use a binary number. For example, to reference the above list (engine, belt, little bolts, oil) using an 8-bit integer would be 54, because 54 in binary representation is 110110.

Pros - datatype is optimal concerning size. Also, I am guessing there are tricky math tricks I could use in my queries to search for parts used (don't know what those are, correct me if I'm in the clouds here).

Cons - I do not know how to handle quantity using this method. Also, Even with a 64-bit BIGINT still only gives me 64 parts that can be in my table. I expect many hundreds.

Any ideas? I am using MySQL. I may be able to use PostgreSQL, and I understand that they have more flexible datatypes like JSON and arrays, but I am not familiar with how querying those would perform. Also it would be much easier to stay with MySQL

Upvotes: 1

Views: 659

Answers (1)

Solano
Solano

Reputation: 550

Why not create a Relationship table?

You can create a table named Workorders_Parts with the following content:

|workorderId, partId|

So when you want to get all parts from a specific workorder you just type:

select p.name 
from parts p inner join workorders_parts wp on wp.partId = p.partId
where wp.workorderId = x;

what the query says is:

Give me the name of parts that belongs to workorderId=x and are listed in table workorders_parts

Remembering that INNER JOIN means "INTERSECTION" in other words: data i'm looking for should exist (generally the id) in both tables

inner join image

IT will give you all part names that are used to build workorder x.

Lets say we have workorderId = 1 with partID = 1,2,3, it will be represented in our relationship table as:

workorderId | partId
1 | 1
1 | 2
1 | 3

Upvotes: 1

Related Questions