Database design

[baris22]

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

 

[enlytend]

Looks almost right - a couple of questions :

1) Why are you putting "money" in a separate table? Can't you make it part of the "orders" table? It would seem to me there would be a one-to-one relationship between order and payment information so putting it in another table just needlessly complicates things...

2) What is the reason for "item_id" in the orders table? If you're trying to tie items (details) to an order, why not just use the order id in the items file?


items ('items_order_id', 'items_name', 'items_sort')

"items_id" seems unnecessary.

 

[baris22]

Looks almost right - a couple of questions :

1) Why are you putting "money" in a separate table? Can't you make it part of the "orders" table? It would seem to me there would be a one-to-one relationship between order and payment information so putting it in another table just needlessly complicates things...

2) What is the reason for "item_id" in the orders table? If you're trying to tie items (details) to an order, why not just use the order id in the items file?


items ('items_order_id', 'items_name', 'items_sort')

"items_id" seems unnecessary.

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

 

[Bruce_KD]

First of all, can I assume this is in mySQL, or is it something like Access or Oracle?

What is order_unique_id. I would hope the "id" is the primary key and cannot be a duplicate.
Can you also assume that when order_collect_time is full, the order has been collected (thus eliminating the need for order_collected)?
Isn't order_rest_to_pay = order_total_to_pay - order_deposit? You could get rid of order_rest_to_play in this case.

Instead of having a deposit field, you could just record the total amount and the amount paid. Then you could eliminate the order_paid and assume it has been paid when order_total = order_(amount_)paid

You can go two different ways with the details table with order_id and customer_id
The order already contains information about the customer, so it's a bit redundant to have it in the second table.
If you're concerned with speed of searching, you can include both in the details field, but you may want to do some testing with indexing to get a good balance.
If you're concerned about storage space, just reference the customer from the order table.

Also, what is worker_sort?


Bruce

 

[enlytend]

I see you've made some changes. Ditto what Bruce_KD said except:

Can you also assume that when order_collect_time is full, the order has been collected (thus eliminating the need for order_collected)?

I'm guessing order_collect_time is the target completion time for the work and order_collected is a flag marking whether or not the customer has picked up their order? Would you also need status flags to track if work on an order (and/or an individual items on that order) has been completed or not?

 

[baris22]

First of all, can I assume this is in mySQL, or is it something like Access or Oracle?

It is mysql

What is order_unique_id. I would hope the "id" is the primary key and cannot be a duplicate.

it is the number which customer will get and customer use that as a reference number for their order. They want reference number to be between 1-9999. When it comes to 9999 i need to reset it to 1.

Can you also assume that when order_collect_time is full, the order has been collected (thus eliminating the need for order_collected)?

Customer may not collect their order on collection time.

Isn't order_rest_to_pay = order_total_to_pay - order_deposit? You could get rid of order_rest_to_play in this case.

it is a good idea to get rid of it.

Also, what is worker_sort?

If tailor wants to sort workers he can change the number on that field and it would be displayed the way tailor wanted. I have got another 4 tables. i have got same field for them aswell.

Would you also need status flags to track if work on an order (and/or an individual items on that order) has been completed or not?

good idea. thanks