Results 1 to 5 of 5

Thread: Design issues!

  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: Design issues!

    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 compenent 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. I have been pondering over this for two days and getting no further.

    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 - -


    EDIT 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 - -

    EDIT: Clarity and formatting
    Last edited by oldmanpete; 05-07-13 at 07:13.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what is difference between STOCK & COMPONENT tables?
    One sandwich can be comprised of many components. Right?
    How/where is that One To Many relationship maintained?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2013
    Posts
    2
    Stock is the quantity of any given component
    Component is the quantity used in any given sandwich
    For example, a bacon sausage sandwich may will have the components bread x2 sausage x2. Bacon x2 and butter x1 as defined in the component table. (Assuming the components will have a static value, any sandwich using bread will use 2 bread any sandwich using bacon will use 2 bacon etc.

    The one to many relation is maintained as such
    1 Sandwich ID may have many components.
    Many components can belong to one stock

    Hopefully this makes sense.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >1 Sandwich ID may have many components.
    post INSERT statements that show above for a single sandwich.

    >Many components can belong to one stock
    post INSERT statements that show above as an example
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You need another table. call it (for example) SALES. It would contain the sales of your sandwiches. Do you also want to include the price of the sandwiches and possibly the whole sale price of the ingredients? Just a thought
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •