How are these tables related?


Say I run an online business where you can order products from my website, and I have a database with two tables:

Table order with the fields order_number, customer_ID, address

Table customer with the fields customer_ID, first_name, last_name

To get a full, detailed 'report' of the order, I would perform a LEFT JOIN to concatenate data from the order table to include the customer's first and last names along with their address and order number.

My question is, how are these tables related? Are they at all? What would an entity relationship diagram look like? Separately they don't seem to interact and act more like lookup tables for each other.


Answers:


An order would always have a customer, no? So it is not a left but inner join.

What links them is the customer_id. So your SQL is simply:

select o.order_number, o.customer_ID, o.address, 
    c.first_name, c.last_name
from orders o
inner join customer c on o.customer_ID = c.customer_ID;

Entity relationship:

Order Customer Customer_Id 0...N >---+ 1 Customer_Id ... ...

Customer-Orders Entity Diagram

This EF relation is from MS SQL Server's sample database Northwind. In that sample database, just like yours, there are Customers and Orders. Customers and Orders tables are related via the CustomerId fields in both tables (it is the primary key in Customers, and foreign key in Orders table). When you model that as an Entity relation than you have the above diagram. Customer entity have an "Orders" navigation property (via customerId) that points to a particular Customer's Orders. And Order entity have a navigation property that points to its Customer (again via CustomerId). The relation is 1 to 0 or many (1 - *), meaning a Customer might have 0 or more Orders.

When you do the join from Customer's side, you use a LEFT join "if you want to see all Customers regardless they have Order(s) or not" - 0 or more Order(s). If you want to see only those with Order(s) then you use an inner join.

When you do the join from Orders' side, then an Order must have a Customer so it can't be a LEFT join. It is an INNER join.

You can check the relation from both sides using the connecting CustomerId field.

You wouldn't have a separate table for "OrderId, CustomerId" as it is not many-to-many relation (it would be pure redundancy and would create normalization anomalies).

Hope it is more clear now.