Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    web designer getting confused with db design

    I'm comfortable pulling data from an existing db but this is the first time that I'm creating a db that has more than 1 or 2 tables.

    The tables are listed below along with a blurb on the thought process. To anybody that has time to review this -- thank you very much.

    I'm creating a db for a small store a friend is opening. We're keeping track of

    1. products -- many products are one of a kind, but not all.
    2. vendors
    3. sales
    4. customers

    The thought process began with the product: identify it and its price.

    PRODUCT
    #id
    name
    purchase_price
    qty
    purchase_id [foreign key to PURCHASE table]

    To keep track of the various purchase activities (sometimes products are bought wholesale, sometimes retail, sometimes from the web) It is necessary to keep track of when and type of purchase

    PURCHASE
    #id
    product_id [foreign key to PRODUCT table]
    purchase_date
    purchase_person [if business expands will be more than one buyer]
    vendor_id [ foreign key to VENDOR table]

    VENDOR
    #id
    business_name
    address
    ...
    type_id [ foreign key to TYPE table]

    As said before there are different types of vendors -- wholesale, etc...

    TYPE
    #id
    type


    Then looked at tables from the point of sale perspective:

    SALE
    #id
    sale_date
    salesperson
    sale_items [foreign key to SALE_ITEMS talbe]
    customer_id [foreign key to CUSTOMER table]

    SALE_ITEMS
    #id
    sale_id [foreign key to SALE table]
    product_id [foreign key to PRODUCT table]
    qty
    sale_price

    CUSTOMER
    #id
    fname
    ....


    tia for any feedback.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: web designer getting confused with db design

    You need to remove the redundant foreign keys you have going from master to detail, i.e.:

    PRODUCT
    purchase_id [foreign key to PURCHASE table]

    SALE
    sale_items [foreign key to SALE_ITEMS talbe]

    Foreign keys can only be defined in one direction: on the detail table, referencing the master table. Otherwise, your design will enforce that each PRODUCT is purchased only once, and each SALE has exactly one SALE_ITEM.

Posting Permissions

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