Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Help needed on a database design

    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:

    workers ('workers_id', 'workers_name', 'workers_sort')
    items ('items_id', 'items_name', 'items_sort')
    customers ('customers_id', 'customers_name', 'customers_address', 'customers_city', 'customers_zip', 'customers_phone', 'customers_code', 'customers_comment')
    orders ('orders_id', 'customers_id', 'items_id', 'items_number', 'orders_details', 'orders_time', 'orders_collection_time')
    money ('money_id', 'orders_id', 'money_total', 'money_deposit', 'money_rest', 'money_done')
    workers_job ('workers_job_id', 'orders_id')




    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by baris22
    Do i need to add or change anything else and does it look ok.
    almost ok

    orders is wrong, you need two tables, orders and orderitems

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    what about this one?

    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the difference between order_id and order_unique_id?

    what is order_items for?

    why does detail have customer_id?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Quote Originally Posted by r937
    what is the difference between order_id and order_unique_id?
    unique_id will be the one i will give to customer as a reference number for their order. Customer reference number must not be higher than 999. it is the way they want me to do. so i am going to reset order_unique_id when it comes to 999.

    Quote Originally Posted by r937
    what is order_items for?
    On customer receipt i need to show what items they gave to shop. I thought it will be easy if i add that field on order.

    Quote Originally Posted by r937
    why does detail have customer_id?
    I do not really need that one. I added for just incase.

Posting Permissions

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