Database design

Discussion in 'Databases' started by baris22, Jul 18, 2009.

  1. #1
    Hello all,

    I am trying to design a database for a tailor. I am not sure if my design is right. Do i need to add or change anything else and does it look ok.

    Story is:

    Customer comes to tailor shop. Tailor takes customers name, address, phone number, the items names (jeans, skirt...) and what needs to be done for the items. Tailor also tells the amount which customer needs to pay. Tailor records if customer paid the full amount or any deposit. Tailor also records when he took the job and when customer going to pick the items. Tailor prints the order. There are 3 bits. Customer gets the bit which has got order id, collection date and item names.

    There are workers for tailors. When the customer is away tailor needs to select a worker for the job and he also needs to record it.

    This is my database:

    worker
    # worker_id
    # worker_name
    # worker_sort

    customer
    # customer_id
    # customer_name
    # customer_address
    # customer_city
    # customer_zip
    # customer_phone
    # customer_code
    # customer_comment

    order
    # order_id
    # order_unique_id
    # order_items
    # order_time
    # order_collect_time
    # order_collected
    # order_total_to_pay
    # order_deposit
    # order_rest_to_pay
    # order_paid
    # customer_id

    detail
    # detail_id
    # detail_item
    # detail_fee
    # detail_item_amount
    # detail_detail
    # order_id
    # customer_id
    # worker_id



    Thanks
     
    baris22, Jul 18, 2009 IP
  2. theweekendchef

    theweekendchef Peon

    Messages:
    69
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    customercomments (new)
    # customer_id
    # comment_date
    # customer_comment

    Zip (new)
    # city
    # zip

    order (remove)
    # order_id
    # order_unique_id
    # order_items
    # order_time
    # order_collect_time
    # order_collected
    # order_total_to_pay
    # order_deposit
    # order_rest_to_pay
    # order_paid
    # customer_id

    order (new)
    # order_id
    # order_unique_id
    # order_items
    # customer_id

    orderbilling (new)
    #order_id
    # order_time
    # order_collect_time
    # order_collected
    # order_total_to_pay
    # order_deposit
    # order_rest_to_pay
    # order_paid

    detail (remove)
    # detail_id
    # detail_item
    # detail_fee
    # detail_item_amount
    # detail_detail
    # order_id
    # customer_id
    # worker_id

    detail (new)
    # order_id
    # detail_id
    # detail_item
    # detail_fee
    # detail_item_amount

    detailComments (new)
    # detail_id
    # worker_id
    # detail_detail

    Proper database design is to remove any and all duplicated items across the database. IE, a customer can have multiple phone numbers or addresses (work, home, vacation), orders can have multiple items worked on by multiple workers, multiple workers may work on different parts of the same item. Based on this, the design above will limit the duplication of information and improve performance. Queries will be more difficult to write, but this is about the user not the programmer :)

    Raymond Laubert
    MCT, MCDBA, MCITP:SQL2005, 2008, Oracle Certified Professional 10G
     
    theweekendchef, Jul 20, 2009 IP