0

Hi I am currently trying to build a database for a project.

Currently all orders must have a customer, customers have an address and then they have a shipto address. Up to 10 different shipto addresses can be added. And each order could be shipping to a different address of that customer.

Would anyone know how I should model this?

Orders
------
ID
customerID (referencing ID from customers)
other rubbish

Customers
---------
ID
Address
Email
Phone 
other rubbish

Shipto
------
ID
CustomerID (referencing ID from customers)
Address
other address related stuff

How should all of these be linked?

*edit Was just thinking, should customers belong to order? Or should orders belong to customers?

Ofir Winegarten
  • 9,215
  • 2
  • 21
  • 27
Butterflycode
  • 759
  • 2
  • 10
  • 23
  • 3
    Whenever you have problems determining the relationships between tables, state the relationships in English (or your native language). Customers make orders. Customers have zero to many orders. Customers have addresses. Customers have one to many addresses. Orders are shipped to an address. Orders and address have a one to one relationship. Now, go forth and normalize, so help you Codd. – Gilbert Le Blanc Mar 19 '13 at 13:01

2 Answers2

2

It seems you have a straightforward 1:N relationship between customers and their shipping addresses, which can be represented by a FOREIGN KEY in the latter, referencing the former (as you already noted).

But whether you had multiple addresses or not, you'd still need to remember the shipping address and product prices at the time of shipping, even if user later changes her address or there is a change in price. So just adding a FK from Order to Shipto is not viable.

To get some ideas on how to cope with these kinds of problems, please take a look at:

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

Your model looks fine to me, I would add a shipto_id to the Orders table.
This way you know which order was sent to where.

Ofir Winegarten
  • 9,215
  • 2
  • 21
  • 27