Relationships question?

[pepenino]

If I've created a one-to-many relationship between Table 1 and Table 2, and a one-to-many relationship between Table 2 and Table 3, is a one-to-many relationship automatically created between Table 1 and Table 3, or do I still need to create that relationship physically?

[orange]

You would not normally have this 1 to many from tbl3 to tbl1.

Usual set up
tbl1--->tbl2--->tbl3

Tell us about your tables and the business issue involved.

[pepenino]

Table 1 is "Authors". Table 2 is "Titles". Table 3 is "Orders". I want to reference both "Author" and "Title" on the order.

There is one author to many titles.

It's a book business. I want to be able to have both author and title appear on the order.

If I've created a relationship between the author and title tables, and a relationship between the title and order tables, does the relationship between author and title carry over to the order table automatically so the author will appear on the order, too, or do I have to draw the connections directly between the author table and the order table?

[orange]

Here is a draft model of your issue, as I understand it.

[pepenino]

Orange,

You've made the problem much more complex than it is.

Ultimately, when I create an order, the order form will have a search drop down box to allow me to choose the author's name, which would provide a list of titles by that author to choose from. As I've mentioned, I have the relationship between authors and titles set, and the relationship between the titles and the order tables set. My question is whether I need to set a relationship between the authors and orders tables, or will it happen automatically because the author and title tables already have a relationship set set?

[orange]

Hmm. Perhaps.
How many Titles can a Book have?

Is the Order for a Title or a Book?

Can someone Order 5 Books on the same Order? How do you handle that?

My guess is that Customers order Book(s) not Titles as such.
No need for a relationship between Book and Title --unless you know of some special circumstance.

Not trying to make things complex. Just trying to ensure your data model and database match your requirements.

You can search by Author to find all the Titles/Books by that Author, but when you make a selection, you have now identified a Book.

[pepenino]

Orange,

Sorry, but you're just not getting it. The only time I've referenced the word "book" is to describe the type of business I'm doing. The table "Titles" could just as well be named "Books". They're the same thing. I'm sorry, but I must not be making myself clear, because I'm just asking an "if A= B, and B=C, then does A=C-type question. I'll find some more literature on the subject and read up. Thanks for trying to help.

[orange]

Sorry, but you're just not getting it

.
Well it wouldn't be the first time.

When you build a database to support your business, the relationships match your business rules.
You said it was a Book business and you mentioned Order. I just tried to relate the pieces.

It is the joins between tables that allows you to get Author and Title on the OrderDetail, via a query.

You may get some info from this link on circular relationships.
https://www.codeproject.com/articles...atabase-design

If you have some time, here are some videos that will help with relational database concepts and modelling.

Intro to Database
The Relational Model
Data Modelling and the ER Model

Good luck.

[ssanfu]

You have said
tbl1-------->tbl2--->tbl3
Authors--->Titles--->Orders

Then you ask

Table 1 is "Authors". Table 2 is "Titles". Table 3 is "Orders". I want to reference both "Author" and "Title" on the order.

There is one author to many titles.

It's a book business. I want to be able to have both author and title appear on the order.

If I've created a relationship between the author and title tables, and a relationship between the title and order tables, does the relationship between author and title carry over to the order table automatically so the author will appear on the order, too, or do I have to draw the connections directly between the author table and the order table?

You said you want both the title and author on the order. The answer would depend on the "Orders" table structure.
If you stipulate that 1 title has only 1 author and you have fields in the orders table for the author PK and the title PK, you could have both on the order.
If you want to only select the title, you could concatenate the author and title to appear on the order.

You state that you will have cascading combo boxes; to me that implies a different relationship:


You can still have cascading combo boxes to select the author/title.
Obviously, there would be other fields...


Again, it all depends on the Orders table.
Maybe you would post your dB???