CODEWITHSUNDEEP
databasedatabase-designerd
Dan Dinu
Dan Dinu

Reputation: 33388

database design scenario

What's the right way to do this:

I have the following relationship between entities RAW_MATERIAL_PRODUCT and FINISHED_PRODUCT: A FINISHED PRODUCT has to be made of one ore more Raw Material Products and a Raw Material Product may be part of a Finished Product.( so a Many-Many). I have the intersection entity which i called ASSEMBLY that tells me exactly of what Raw Material Products is a Finished Product made of.

Good. Now i need to sell the Finished Products and compute the production cost. PRODUCT_OUT entity comes in, which can contain only one FINISHED PRODUCT and a FINISHED PRODUCT may be part of multiple PRODUCT_OUT.

It would be easy if, for example, Finished Product A was always made of 3 pieces of Raw Material Product a1, 2 of a2 etc. Problem is that the quantities may change.

The stock of a Raw Material Product is computed as 

      TotalIn - TotalOut

so i can't put quantity Attribute in ASSEMBLY because i would get incorrect data when calculating the Stock. (if quantites are changed)

My only idea is to give up to FINISHED_PRODUCT entity and make a join between PRODUCT_OUT and RAW_MATERIAL_PRODUCT with the intersection entity containing a quantity attribute. But this seems kind of stupid because almost all the time a FINISHED_PRODUCT is made of the same RAW_MATERIAL_PRODUCTS.

Is there a better way?

Upvotes: 0

Views: 161

Answers (1)

Phil Sandler
Phil Sandler

Reputation: 28016

I'm not 100% sure I understand, but it sound like essentially the recipe can change, and your model needs to account for this?

But this seems kind of stupid because almost all the time a FINISHED_PRODUCT is made of the same RAW_MATERIAL_PRODUCTS.

Almost all the time, or all the time? I think that's a pretty critical question.

It seems to me that when you change the recipe, you should create a new FINISHED_PRODUCT row, which has a different set of RAW_MATERIAL_PRODUCTS based on the association in the ASSEMBLY table.

If you want to group differnt recipies of the same FINISHED_PRODUCT together (kind of like versioning!), create a FINISHED_PRODUCT_TYPE table with a 1:m relationship to the FINISHED_PRODUCT table.

Edit (quote from comment):

I totally agree with you it should be a different product but if i add one screw to a product i can't really name it Product A with 1 extra screw. And it seems this can happen. I didn't quite get the use of creating a FINISHED_PRODUCT_TYPE table. Could you please explain?

Sure. So your FINISHED_PRODUCT_TYPE defines the name of the product, and possibly some other data (description, category, etc.). Then each row in FINISHED_PRODUCT is essentially a "version" of that product. So "Product A" would only exist in one place, a row in the FINISHED_PRODUCT_TYPE table, but there could be one or many versions of it in the FINISHED_PRODUCT table.

Upvotes: 1

Related Questions