Can't get the relationships I want in Access-VBForums
Results 1 to 6 of 6

Thread: Can't get the relationships I want in Access

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    55

    Can't get the relationships I want in Access

    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

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,588

    Re: Can't get the relationships I want in Access

    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)
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    55

    Re: Can't get the relationships I want in Access

    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?

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,588

    Re: Can't get the relationships I want in Access

    Quote Originally Posted by magic_andrew View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Member
    Join Date
    Dec 2014
    Posts
    55

    Re: Can't get the relationships I want in Access

    Quote Originally Posted by jmcilhinney View Post
    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?

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,513

    Re: Can't get the relationships I want in Access

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.