Simple question on table design

[Johnny]

Regarding 3 tables, A, B and C.

If A is linked to B, and C is linked to B, does it mean A is linked to C and vice versa?

 

[John Big Booty]

Tables A and C will be link indirectly through table B. Beyond that it would depend on the structure of your tables, but it would be unlikely they can be directly linked if you are linking them via a junction table (table B).

 

[Johnny]

Yes B is a junction table in my case, I'm just new at this and not sure the limit of 'indirectly" is as it applies to creating queries and forms.

In my inexperienced head it would seem I could request data into C from A because it's linked to B, but sometimes it works, other times it doesn't and I'm trying to wrap my head around the real world difference.

 

[Rabbie]

Using a junction table means you have set up a many to Many relationship between A and C so you should be able to find all records in A that relate to single record in C or vice versa

 

[gemma-the-husky]

two concepts here

CHAIN

lets say you have a Sales Account
sales account is owned by a "Rep"
Reps are in "areas"

so sales account ---> rep -----> area

the area is uniquely defined by the rep, and you only need to store the rep within the sales record - so this does have the relationship you mentioned


----------------
JUNCTION TABLE

However

Say you have a "Students" table
and a "Courses" Table

so a course can consist of several students, and a student can enrol in several course

You need an extra table to store the "Enrolments"

Students <====== Enrolled ======> Courses

And in this scenario, your hypothesis doesn't follow. It's the DIRECTION of the links (the 1 to many) that makes the difference

 

[Johnny]

Thanks you guys that does make more sense, especially thinking of the direction rather then 3 tables are linked.

 

[dportas]

Regarding 3 tables, A, B and C.

If A is linked to B, and C is linked to B, does it mean A is linked to C and vice versa?

The idea of any tables being "linked" other than in the mind of the user is entirely contrary to the relational model of data. As I expect you might know, Codd's relational model was designed to abolish from the database any kind of structures linking tables together and to remove the need for any predefined navigational paths between tables. So in relational terms the answer to your question is obviously No.

In conceptual modelling it all depends on what you want to represent. In ER modelling for example you typically pick some convenient subset of relationships (a relationship being an "association among things") that define the concept you want to model. There are no absolute rules as to which subset of relationships you must include in your model to make it correct.

The MS Access user interface uses the term "relationship" in a different way. An Access "relationship" is a predefined specification of a join between tables - in other words exactly the sort of thing that does not exist in a relational database. I expect that's what you are referring to as "linking". Don't bother about it too much because it isn't important from a database design point of view. What's important is that you implement the tables and constraints that accurately represent your domain of discourse. "Linking" tables is no part of doing that.

 

[gemma-the-husky]

I was certainly thinking in terms of logical links rather than physical links. I find it helps to think in terms of the direction of the putative 1-many "link"

its all pointers, and memory locations isn't it.


But thinking about it, how can it possibly be managed WITHOUT a REAL data structure to do that. How can a "link" beteeen two tables be managed without some structure relating absolute file/memory locations. I just don't think it can.

When you get an "related record is required" error, surely that can only be happening because a pointer has a null value (or some other appropriate physical memory error)

 

[dportas]

But thinking about it, how can it possibly be managed WITHOUT a REAL data structure to do that. How can a "link" beteeen two tables be managed without some structure relating absolute file/memory locations. I just don't think it can.

When you get an "related record is required" error, surely that can only be happening because a pointer has a null value (or some other appropriate physical memory error)

That's irrelevant though. I was talking about the database at the logical level. The point of Physical Data Independence is that the internals of the database in memory and on disk are hidden from the user and are not relevant to a discussion about logical database design.

Strange as it seems, there are people who have the misconception that a relational database means "a database with relationships in it" or "a database with referential integrity constraints in it". Talk of "linking" tables might fuel that kind of confusion, which is why I thought it worth trying to clarify the point.

 

[gemma-the-husky]

I see what you mean. (said the blind man!)

 

[BobMcClellan]

Regarding 3 tables, A, B and C.

If A is linked to B, and C is linked to B, does it mean A is linked to C and vice versa?

No..
Table A
id,
Color

Table B
tblA.id,
tblC.id

Table C
id,
Item


A
1, Red
2, Green
3, Blue

C
1, Hat
2, Shoes
3, Shirt

B
1,1
1,2
1,3

If Table B is the "junction" table then you could have one row from A
that "links" to each row in C... As dportas pointed out... the "links" are not
the point.
...Normalizing tables in a database allows you to minimize or eliminate
redundant data and redundant columns in tables. When designing... One approach.. On a spreadsheet or even paper...Establish the tables you will need to accomplish the objective, then start adding columns to each table. once all the tables are filled, see where there is overlap and remove columns where you can and move columns to other tables where it makes sense to do so. Then Create the Primary Key for each table. Then create the Foreign Keys in each table that will allow you to access what you need.

hth,
..bob

 

[Johnny]

Dportas you have turned my Access worldview upside down, I was indeed thinking that relationships as in the access term and operation that "links" or joins tables through PK/FK. I now suspect my difficulty, which mostly lies with forms and specifically subforms, is a syntax problem with control source definition rather then my lurking suspicion my project isnt normalized or that I have joined tables in the wrong way.

Of course the latter could be true too!

Thanks for the example Bob that does clarify the theory.

 

[gemma-the-husky]

To tale your original point though

If A implies B and B implies C, then certainly A implies C

if A ----> B ----C>

A is a sales account
B is orders for those sales accounts
C is order lines for those orders

but in mormal circumstances, you cannot use the relation A---> C directly, as there should be no common link. ie the FK in the order lines table OUGHT to be the PK or the order table. You could, of course store the PK of the sales account in the order lines table as well, and then you would have a direct realtion ship between the two, although this is de-normalising the data.

What are you trying to do exactly?

 

[Johnny]

Well the easiest example I guess would be when I have a form based on a query that has two cascading subforms. For readability it has become best to have information from Table A to be in the subform from Table C essentially. But as you pointed out in the earlier post A--> B <---C I run into issues since C essentially knows nothing about A. Formatting that into the subform can become a syntax nightmare for a newbie )

When using the subform based on the junction table pulling data from A and C was "easy", it just didn't work the other way and until you guys educated me I didnt quite grok the difference. Now that I am no longer thinking of the information as one way streets with access joins as the policemen I think my self education can move forward.