Multiple component ID's - Oracle table

Question

Assumptions

Stock table contains:
STOCK_ID
STOCK_QTY

Component table contains:
COMPONENT_ID
COMPONENT_QTY
STOCK_ID (FK)

sandwich table contains:
SANDWICH_ID
SANDWICH_PRICE
COMPONENT_ID(FK)

I have three sandwiches, bacon, sausage and bacon and sausage. STOCK_QTY contains the quantity of stock for the entire shop. COMPONENT_ID contains the quantity of the component used per sandwich.

Now the problem I am having is that I require multiple components per sandwich (to make a complete sandwich, ie bread, butter, sausage) which in turn will be removed from the overall stock quantity (bread 200, butter 200, sausage 300) by the value specified in the component quantity field (bread 2, butter 1 sausage 2). I am unable to figure out how to design/implement this. Why/how do I assign multiple COMPONENT_IDs to a SANDWICH_ID or am I going about this all wrong?

TABLES

MSD_COMPONENT:

COMPONENT_ID        VARCHAR2(8)     No       -       1
COMPONENT_TYPE      VARCHAR2(8)     Yes      -       -
COMPONENT_QTY       NUMBER          Yes      -       -
COMPONENT_DECSR     VARCHAR2(25)    Yes      -       -
STOCK_ID            VARCHAR2(8)     Yes      -       -


COMPONENT_ID  COMPONENT_TYPE  COMPONENT_QTY  COMPONENT_DECSR     STOCK_ID
  CID0001        BREAD            2          WHITE BREAD SLICES   SID0001
  CID0002        BREAD            2          BROWN BREAD SLICES   SID0002
  CID0003        BREAD            1          WHITE BREAD BAP      SID0003
  CID0004        BREAD            1          BROWN BREAD BAP      SID0004
  CID0005        BREAD            2          WHOLEMEAL SLICES     SID0005
  CID0015        MEAT           200          BACON RASHERS        SID0006
  CID0006        BREAD            1          WHOLEMEAL BAPS       SID0007
  CID0007        VEG            100          TOMATOES             SID0008
  CID0008        VEG             15          ONIONS               SID0009
  CID0009        VEG             40          PEPPERS              SID0010
  CID0010        VEG             25          CARROTS              SID0011
  CID0011        MEAT            50          BEEF                 SID0012
  CID0012        MEAT           100          CHICKEN BREAST       SID0013
  CID0013        MEAT            50          HAM                  SID0014
  CID0014        MEAT           100          PORK SAUSAGE         SID0015

MSD_SANDWICH:

SANDWICH_ID         VARCHAR2(8)     No       -       1
SANDWHICH_TYPE      VARCHAR2(8)     Yes      -       -
SANDWICH_DESCRIP    VARCHAR2(20)    Yes      -       -
SANDWICH_PRICE      VARCHAR2(8)     Yes      -       -
COMPONENT_ID        VARCHAR2(8)     Yes      -       -

MSD_STOCK:

STOCK_ID    VARCHAR2(8)     No       -       1
STOCK_TYPE  VARCHAR2(15)    Yes      -       -
SHOP_ID     VARCHAR2(6)     Yes      -       -
STOCK_QTY   NUMBER          No       -       -

While this sounds delicious, can you provide some demonstration of these tables containing data, and what you want to operate on them, so to make the question more clear? — Passerby, May 07 '13 at 09:21
Sorry! edit to include tables. As you see I can only have one component ID in the sandwich table but a sandwich will contain multiple components. I'm positively baffled! — oldmanpete, May 07 '13 at 09:43
Possible duplicate of [How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) — Stephen Kennedy, Mar 12 '18 at 11:49

Passerby · Accepted Answer · 2013-05-07T11:37:55.147

Spent some time reading your question...

how do I assign multiple COMPONENT_IDs to a SANDWICH_ID

This looks like a typical one-to-many relationship. This can be generally done by adding a third "relation" table:

MSD_RECIPE:

SANDWICH_ID (FK) COMPONENT_ID (FK)
  SID0001          CID0001
  SID0001          CID0015
  SID0001          CID0007 /* white sandwich made with bacon and tomato */
  SID0002          CID0002
  SID0002          CID0013
  SID0002          CID0008 /* brown sandwich made with ham and onion */

AND,

now that you have a relation table RECIPE, you don't have to "fix" quantity in COMPONENT (and technically I think that's logical incorrect), instead you can move quantity to RECIPE to make things more flexible:

MSD_INGREDIENTS (replaces MSD_COMPONENT):

INGREDIENT_ID  INGREDIENT_TYPE  INGREDIENT_DECSR     STOCK_ID
  CID0001        BREAD          WHITE BREAD SLICES   SID0001
  CID0002        BREAD          BROWN BREAD SLICES   SID0002
  CID0005        BREAD          WHOLEMEAL SLICES     SID0005
  CID0015        MEAT           BACON RASHERS        SID0006
  CID0006        BREAD          WHOLEMEAL BAPS       SID0007
  CID0007        VEG            TOMATOES             SID0008
  CID0008        VEG            ONIONS               SID0009
  CID0010        VEG            CARROTS              SID0011
  CID0013        MEAT           HAM                  SID0014

MSD_RECIPE:

SANDWICH_ID (FK)  INGREDIENT_ID (FK)  QUANTITY
  SID0001           CID0001             2
  SID0001           CID0015             200
  SID0001           CID0007             100 /* old white sandwich */
  SID0003           CID0002             2   /* two brown bread */
  SID0003           CID0005             1   /* one wholeweat bread */
  SID0003           CID0013             100 /* one ham */
  SID0003           CID0015             100 /* one bacon */
  SID0003           CID0008             30  /* new 2-level sandwich! */

I had to read that a couple of times to make it sink in but that's done it. Many thanks Mr Passerby :) — oldmanpete, May 07 '13 at 22:56

Multiple component ID's - Oracle table

1 Answers1