Relationship Help (1 Viewer)

I am trying to set up a mailing list database and having 5 tables:
1. Customers with: customer_id, name, company, address, city, state_id (number), zip...
2. States with: state_id (autonumber), states_name
3. Services: services_id, services_name (Fedex, DHL, UPS...)
4. Shipping: ship_id, ship_date, ship_services, ship_tracking, good_type and good_quantity
My questions:
1. Someone please look at the above an tell me if the concept is right?

2.Theoretically, I understand this should be one-to-many, but I don't quite understand why when I open the relationship window, drag the state_id from table state to the state_id in customer, the relationship would not be what I expected, i.e. when I open the tables States, press the little plus and it shows no Customer data :banghead: What's wrong ? And how can I go around this situation?

Thanks a bunch

You are on the right track but as you realise something is missing.

tblStates and tblServices are called lookup tables. You have Customers receiving multiple orders which you cover in tblShipping. The latter I'd call tblOrders because the shipping details are just part of the orders.

What you are missing in your tblShipping is the CustomerID because every tblShipping record is related to a customer.

Your model limits the order to only one product (good_type and good_quantity)

A typical database I think you are looking at has the following table structure. (Note I have not included lookup tables.)

tblCustomers
CustomerID (Primary Key)
<other customer fields>

tblOrders
OrderID (PK)
CustomerID (Foreign Key)
OrderDate
ShippingDate
ServiceID (FK into tblServices)
<other relevant fields including delivery address if it can be different to the Customer's address>

tblOrderDetails
OrderDetailID (PK)
OrderID
Quantity
ProductID (FK into tblProducts)
ProductPrice

Thank you very much Cronk, you are a real saver!