CODEWITHSUNDEEP
mysqldatabasedatabase-schema
redGREENblue
redGREENblue

Reputation: 3126

Suggest a schema/table design for my requirement

Here's a Item table that stores basic item information like name, price, color etc.

Now, I need to keep track of materials used in an item like say, cotton, gems, rhinestone, leather etc. One required feature is to identify the items that is made of one or more partiuclar materials. For example, given the materials cotton and rhinestone, I should be able to output a list of all the items that is made of cotton and rhinestone (among other possible materials).

So, what's the best way to design the tables for this particular requirement taking performance into account?

Upvotes: 0

Views: 203

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29649

It all depends on the requirements (as usual); this kind of solution is commonly referred to a "Bill of Materials", and can get pretty complex. There's a decent write up of the BoM concepts here.

The most common complication is that you realize that an Item needs other Items - for instance, a Car requires a wheel, but a wheel requires a hubcap, rim, brake assembly; the brake assembly in turn requires pads and hydrolics etc.

If this is not your requirement (and you don't say it is, so maybe I'm overthinking this), the hierarchy design still works conceptually, but may not be the most efficient.

Upvotes: 0

StevieG
StevieG

Reputation: 8729

You should have a table for item and one for material, and then use a junction table to define which items contain which materials and vice-versa.. Something like this would be best:

Item

Id
Name
Price
colour

Material

Id
Name
Description
...

Item_Material

Item_Id (FK to Item.Id)
Material_Id (FX to Material.Id)

Then, if you want to get all the items with cotton in them, you'd write a query like:

SELECT ITEM.*
FROM ITEM INNER JOIN ITEM_MATERIAL ON ITEM.ID = ITEM_MATERIAL.ITEM_ID
INNER JOIN MATERIAL ON MATERIAL.ID = ITEM_MATERIAL.MATERIAL_ID
WHERE MATERIAL.NAME = 'cotton'

Or for all materials making up an item:

SELECT MATERIAL.*
FROM ITEM INNER JOIN ITEM_MATERIAL ON ITEM.ID = ITEM_MATERIAL.ITEM_ID
INNER JOIN MATERIAL ON MATERIAL.ID = ITEM_MATERIAL.MATERIAL_ID
WHERE ITEM.NAME = 'handbag'

Upvotes: 3

Related Questions