Can't get the relationships I want in Access

[magic_andrew]

Hi all,
I've got a database with three tables; CustomerDetails, OrderDetails and OrderLines. I'll link it below.
I'm pretty sure the relationships I have thought should work in theory but when I try and implement them in Access, there's no way to change the relationships.
Just wondering if anyone can give me some guidance on how to get the following relationships in my database.
1) OrderDetails - One-to-One - OrderLines
2) OrderLines - Many-to-One - CustomerDetails

My thinking is this:
1) Each Order can relate to one OrderLine
Each OrderLine can relate to one Order
2) Each OrderLine can relate to one Customer
Each Customer can have many OrderLines

Any help would be really appreciated. I've been everywhere looking for help and no one is replying
Here's the link:
https://drive.google.com/file/d/0Byk...ew?usp=sharing

[jmcilhinney]

That doesn't seem to make sense. What exactly does an OrderDetails record and an OrderLines record represent? Logic seems to dictate that OrderDetails describes the order as a whole and OrderLines describes an item in an order. Surely each order is for one customer only and each order can contain multiple items. That would mean that the relationships should be one CustomerDetails to many OrderDetails and one OrderDetails to many OrderLines:

CustomerDetails: CustomerDetailsId (PK)
OrderDetails: OrderDetailsId (PK), CustomerDetailsId (FK, CustomerDetails)
OrderLines: OrderLinesId (PK), OrderDetailsId (FK, OrderDetails)

[magic_andrew]

Just to clarify, OrderDetails doesn't contain any any information about individual items, just the cost, ect.
OrderLines is used to link an order to a customer so that you may identify which customer an order belongs to.
Does that change anything?

[jmcilhinney]

Just to clarify, OrderDetails doesn't contain any any information about individual items, just the cost, ect.
OrderLines is used to link an order to a customer so that you may identify which customer an order belongs to.
Does that change anything?

What table actually represents an order? Surely it is OrderDetails. What does OrderLines represent? Is it an item in an order? Presumably an order can include an arbitrary number of items. If so then how can the relationship between OrderDetails and OrderLines possible be one-to-one? How many customers can be related to a single order? Presumably only one. The CustomerDetails table needs to be related to the table that represents an order then, not the table that represents an item included in an order.

Maybe you should try providing a FULL and CLEAR description of what each table represents. Listing the columns in each table might help to clarify that.

If it was me, I'd have a table that represented an order that was named Order. I'd also have a table that represented a customer named Customer. I prefer singular table names but some people do prefer plurals so that might be Orders and Customers. One customer can make multiple orders so obviously the relationship between Customer and Order is one-to-many, i.e. the Order table contains a foreign key column from the Customer table. Each order can consist of multiple items so I would also have a table named OrderItem or the like and it would contain a foreign key column from the Order table. It would also contain a foreign key column from the StockItem or similar table to indicate which item was included in the order.

[magic_andrew]

What table actually represents an order? Surely it is OrderDetails. What does OrderLines represent? Is it an item in an order? Presumably an order can include an arbitrary number of items. If so then how can the relationship between OrderDetails and OrderLines possible be one-to-one? How many customers can be related to a single order? Presumably only one. The CustomerDetails table needs to be related to the table that represents an order then, not the table that represents an item included in an order.

Maybe you should try providing a FULL and CLEAR description of what each table represents. Listing the columns in each table might help to clarify that.

If it was me, I'd have a table that represented an order that was named Order. I'd also have a table that represented a customer named Customer. I prefer singular table names but some people do prefer plurals so that might be Orders and Customers. One customer can make multiple orders so obviously the relationship between Customer and Order is one-to-many, i.e. the Order table contains a foreign key column from the Customer table. Each order can consist of multiple items so I would also have a table named OrderItem or the like and it would contain a foreign key column from the Order table. It would also contain a foreign key column from the StockItem or similar table to indicate which item was included in the order.

Sorry I musnt have explained it well.
There's no need to store data about individual items in the database I'm creating. From what I can gather, it seems that the OrderLines table would be redundant.
If I put a CustomerID foreign key within the OrderDetails table, then that should allow an order to be linked to a customer. Am I correct?

[FunkyDexter]

Am I correct?

Yes. As long as an entity has a foreign key to another entity then it's linked and establishes a many to one relationship. So if OrderDetails has a CustomerID field and that is set as a foreign key to the CustomerDetails table then many orders can relate to one customer, or to put it another way, one customer can have many orders.

Any time you find yourself trying to create a one to one relationship you should stop and consider why. From a purely logical point of view there is never a need to have one and a properly normalised design would simply combine the two tables into one. That said, there are times when you'd want to establish one for performance reasons or because, for example, you're extending a third party's database and don't want to affect the core tables.