none
Relationships and such RRS feed

  • Question

  • General question.

    I have two tables.. Orders and Order Details. The information inside seems ambiguous to each other. Can I use the Orders table for just the Order Number and the Order Details as a comprehensive table? I'm worried about relationship issues and "related record is required in table [such-and-such]" errors.

    Also, would any one be able to point me to an "advanced" relationships link on the "inter-web"? All Im finding is videos and forums explaining the basics. I feel if I took it up a notch I might understand a deeper meaning. Maybe im just crazy.

    Many Thanks,

    VR

    -jshot

    Friday, June 15, 2018 4:46 PM

Answers

  • Hi jshot,

    Are you familiar with the concept of "Normalization?" If not, may I suggest doing your "search" on this topic instead of "relationships."

    As I have mentioned before, the Orders table should contain information about the specific transaction minus the details. For example, an order information might include the date of the transaction and the customer. The Order Details table might contain information such as the items purchased during the transaction, their quantities, maybe the price too, any discounts applied. Product descriptions don't have to be in the Order Details table but can be included in invoice reports if desired, of course, this information will come from the Products table - not the Order Details table.

    The main idea is to store a piece of information in your database at only one location (read: one table only).

    Just my 2 cents...

    • Marked as answer by jshot Friday, June 15, 2018 4:59 PM
    Friday, June 15, 2018 4:55 PM
  • Oh, yes sorry. Your essentially ?mating? the two fields. Establishing a connection between the them. And I think a join is basically the direction information/data can flow?

    Close enough. Here's what I meant, and possibly a good enough example to differentiate the two (relationship vs join).

    So, a "constraint" is a rule (or maybe we can call it a law) established at the table level (by design). For example, if we hypothetically have Parents and Children table, the relationship is each parent (let's simplify it to say "mother") can have many children and each child can only have one mother. Also, if have referential integrity (RI) turned on, a child cannot exist without a parent/mother (we call this an orphan record - apropos, isn't it?).

    So, I can enter data in the Parents table all day long but entering data in the Children table requires some care because if I enter a child data without a parent connection, I'll get an error. This is "relationship."

    Now, let's say we want to create a query to find out all the siblings (children of the same parent). The correct way is to "join" the Parent ID field from both tables, which is similar to how the relationship was created. However, if we assume all children have the same last name as their parent(s), then we can also "join" the tables using the LastName field (although it would be wrong assumption). In any case, there is no "rule" preventing us from creating this join. For all we know, it might actually work in this scenario.

    I'm sure there are plenty of other cases where joining two tables not on the PK=FK relationship would make sense, I just can imagine one for right now. However, what I am trying to say is these are not "relationships" because they are (sort of) temporary because it's only created/needed as the situation dictates when you needed to pull data from your tables.

    I hope I didn't confuse you even more.

    Cheers!

    • Marked as answer by jshot Monday, June 18, 2018 2:32 PM
    Friday, June 15, 2018 7:32 PM

All replies

  • Hi jshot,

    Are you familiar with the concept of "Normalization?" If not, may I suggest doing your "search" on this topic instead of "relationships."

    As I have mentioned before, the Orders table should contain information about the specific transaction minus the details. For example, an order information might include the date of the transaction and the customer. The Order Details table might contain information such as the items purchased during the transaction, their quantities, maybe the price too, any discounts applied. Product descriptions don't have to be in the Order Details table but can be included in invoice reports if desired, of course, this information will come from the Products table - not the Order Details table.

    The main idea is to store a piece of information in your database at only one location (read: one table only).

    Just my 2 cents...

    • Marked as answer by jshot Friday, June 15, 2018 4:59 PM
    Friday, June 15, 2018 4:55 PM
  • Or maybe the Orders table with.... Primary key, Order Number and all pertinent Orders foreign keys? And the Order Details will only be for all other fields.
    Friday, June 15, 2018 4:56 PM
  • Perfect. This makes sense. Thanks. Ill check it out.
    Friday, June 15, 2018 4:59 PM
  • Hi jshot,

    You're welcome, as always. I think once you get the idea behind Normalization, you will be able to manage table relationships without any issues.

    Good luck!

    Friday, June 15, 2018 5:09 PM
  • Also, thinking about it, correct me if im wrong, relationships that you assign between tables are completely separate from joins... One compliments the other?!?!
    Friday, June 15, 2018 5:24 PM
  • Also, thinking about it, correct me if im wrong, relationships that you assign between tables are completely separate from joins... One compliments the other?!?!

    Right. As I said before, "relationships" are constraints applied to tables to indicate how the data are related. Whereas, "joins" are just a way to link tables to retrieve information based on a particular scenario.

    You can think of it this way: Relationships are created when designing the table structure and Joins are used mostly in queries.

    Let me think of a good example and come back to you...

    Friday, June 15, 2018 5:39 PM
  • Makes sense.

    Ok thanks. Ill be clocking out in the next half hour or so, so no rush.

    Friday, June 15, 2018 5:52 PM
  • Makes sense.

    Ok thanks. Ill be clocking out in the next half hour or so, so no rush.

    Hi,

    Did you understand what I meant by saying "constraint?"

    Just curious...

    Friday, June 15, 2018 5:55 PM
  • Oh, yes sorry. Your essentially ?mating? the two fields. Establishing a connection between the them. And I think a join is basically the direction information/data can flow?

    Friday, June 15, 2018 6:41 PM
  • The issue that im running across is not fully understanding them. When I go to "read/walk" my way through the flow it makes sense to me but is wrong. Which is fine, I just have to grind through it.
    Friday, June 15, 2018 6:49 PM
  • Oh, yes sorry. Your essentially ?mating? the two fields. Establishing a connection between the them. And I think a join is basically the direction information/data can flow?

    Close enough. Here's what I meant, and possibly a good enough example to differentiate the two (relationship vs join).

    So, a "constraint" is a rule (or maybe we can call it a law) established at the table level (by design). For example, if we hypothetically have Parents and Children table, the relationship is each parent (let's simplify it to say "mother") can have many children and each child can only have one mother. Also, if have referential integrity (RI) turned on, a child cannot exist without a parent/mother (we call this an orphan record - apropos, isn't it?).

    So, I can enter data in the Parents table all day long but entering data in the Children table requires some care because if I enter a child data without a parent connection, I'll get an error. This is "relationship."

    Now, let's say we want to create a query to find out all the siblings (children of the same parent). The correct way is to "join" the Parent ID field from both tables, which is similar to how the relationship was created. However, if we assume all children have the same last name as their parent(s), then we can also "join" the tables using the LastName field (although it would be wrong assumption). In any case, there is no "rule" preventing us from creating this join. For all we know, it might actually work in this scenario.

    I'm sure there are plenty of other cases where joining two tables not on the PK=FK relationship would make sense, I just can imagine one for right now. However, what I am trying to say is these are not "relationships" because they are (sort of) temporary because it's only created/needed as the situation dictates when you needed to pull data from your tables.

    I hope I didn't confuse you even more.

    Cheers!

    • Marked as answer by jshot Monday, June 18, 2018 2:32 PM
    Friday, June 15, 2018 7:32 PM
  • DBGuy,

    No you didn't confuse me at all. This actually drives it home for me. I thought I was in the ball park but to what accuracy. Thank you for taking the time to explain. I'm now going to embark on the "Normalization" quest...gain that understanding.

    fair winds

    ~jshot

    Monday, June 18, 2018 2:36 PM
  • Hi,

    Glad to hear I made some sense (although rereading what I wrote I see some typos). Good luck with your learning and let us know if you need more clarification.

    Cheers!

    Monday, June 18, 2018 5:29 PM