Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Location
    Fort Lauderdale, Florida
    Posts
    2

    Unanswered: Subtype/supertype help

    Hi,

    I have so far created 2 tables:

    ITEM
    ---------
    ITEM_ID ( PK )
    ITEM_NAME
    ITEM_DESCRIPTION
    ITEM_COST
    ITEM_CATEGORY ( 1=package, 2=product, 3=service )


    PACKAGE
    -----------
    ITEM_ID ( PK, FK ITEM.ITEM_ID )
    SUBITEM_ID ( PK, FK ITEM.ITEM_ID )
    QUANTITY

    This way, I can have a table PACKAGE where I can record packages that can contain 0 or more services and/or 0 or more products from the ITEM table.

    Is this a good solution? My next suggestion was to create table for each entity: PRODUCT, SERVICE, PACKAGE.

    But how would I create the table PACKAGE so that it stores 0 or more PRODUCT(S) and/or 0 or more SERVICE(S).

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by photo312
    Is this a good solution?
    i think so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question

    There is no reason to split the ITEM table, the ITEM_CETAGORY column should do fine.

    However, your description was a bit hard to follow... Are you trying to describe that packages can each have multiple items? If so, you want :

    Package (PACKAGE_ID (PK), QUANTITY….)
    Item (ITEM_ID (PK), PACKAGE_ID (FK), ITEM_NAME, ITEM_DESCRIPTION, ITEM_COST...)

    Hence for every single package you can add multiple items (or no items at all). I may have very well misunderstood your requirements though.

  4. #4
    Join Date
    Jul 2006
    Location
    Fort Lauderdale, Florida
    Posts
    2
    Item table cannot have package id in there, because most items will not be part of any package....

Posting Permissions

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