Circular reference?

[Guest]

I've come across this problem a few times in relational databases and never
quite known how to solve it. As an example, I have a website database. It has
[Subjects] and [Articles]. One subject can have many articles. But some
Subjects can also have associated Subjects (taken from the same table). Would
you overcome this by making a union table, joined to the [Subjects] table,
that lists all the other associated Subjects?

Hope this makes sense.

Yo Yo Ma.

 

[Craig Alexander Morrison]

Establish a many to many link between the Subjects table and the Subjects
table (not a typo).

In Access you do this by adding the Subjects table to the Relationships
window twice and link each instance to the new link table.

 

[BruceM]

I also have wondered about this sort of thing. In the instance you
describe, is the primary key from each instance of the Subjects table linked
to the same foreign key field in the junction table?

Establish a many to many link between the Subjects table and the Subjects
table (not a typo).

In Access you do this by adding the Subjects table to the Relationships
window twice and link each instance to the new link table.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

I've come across this problem a few times in relational databases and
never
quite known how to solve it. As an example, I have a website database. It
has
[Subjects] and [Articles]. One subject can have many articles. But some
Subjects can also have associated Subjects (taken from the same table).
Would
you overcome this by making a union table, joined to the [Subjects]
table,
that lists all the other associated Subjects?

Hope this makes sense.

Yo Yo Ma.

 

[Craig Alexander Morrison]

The primary key (PK) is a combination of the fields in the PKs of the
"Subjects" table.

If the PK of Subjects was SubjectTitle then you would need two fields called
say SubjectTitleA and SubjectTitleB and these togther would be the PK of the
linking table.

The linking table PK is exactly the same, in principle, as one between say
Product and Supplier when a Product can be supplied by many Suppliers and
Suppliers supply many products the ProductSupplier table has a PK
(SupplierName, ProductBarCode) assuming Supplier has a PK (SupplierName)
Product has a PK (ProductBarCode).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

I also have wondered about this sort of thing. In the instance you
describe, is the primary key from each instance of the Subjects table
linked to the same foreign key field in the junction table?

Establish a many to many link between the Subjects table and the Subjects
table (not a typo).

In Access you do this by adding the Subjects table to the Relationships
window twice and link each instance to the new link table.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

I've come across this problem a few times in relational databases and
never
quite known how to solve it. As an example, I have a website database.
It has
[Subjects] and [Articles]. One subject can have many articles. But some
Subjects can also have associated Subjects (taken from the same table).
Would
you overcome this by making a union table, joined to the [Subjects]
table,
that lists all the other associated Subjects?

Hope this makes sense.

Yo Yo Ma.

 

[BruceM]

Thanks for the reply. I think I understand you to say that the PK field in
the Subjects table relates one-to-many to two separate fields in the
junction table.

The primary key (PK) is a combination of the fields in the PKs of the
"Subjects" table.

If the PK of Subjects was SubjectTitle then you would need two fields
called say SubjectTitleA and SubjectTitleB and these togther would be the
PK of the linking table.

The linking table PK is exactly the same, in principle, as one between say
Product and Supplier when a Product can be supplied by many Suppliers and
Suppliers supply many products the ProductSupplier table has a PK
(SupplierName, ProductBarCode) assuming Supplier has a PK (SupplierName)
Product has a PK (ProductBarCode).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

I also have wondered about this sort of thing. In the instance you
describe, is the primary key from each instance of the Subjects table
linked to the same foreign key field in the junction table?

Establish a many to many link between the Subjects table and the
Subjects table (not a typo).

In Access you do this by adding the Subjects table to the Relationships
window twice and link each instance to the new link table.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
I've come across this problem a few times in relational databases and
never
quite known how to solve it. As an example, I have a website database.
It has
[Subjects] and [Articles]. One subject can have many articles. But some
Subjects can also have associated Subjects (taken from the same table).
Would
you overcome this by making a union table, joined to the [Subjects]
table,
that lists all the other associated Subjects?

Hope this makes sense.

Yo Yo Ma.

 

[Craig Alexander Morrison]

Yes.

BTW a PK is NOT a field it is an index. A PK (indeed any index) can be up to
10 fields in Jet and 16 in SQLS.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Thanks for the reply. I think I understand you to say that the PK field
in the Subjects table relates one-to-many to two separate fields in the
junction table.

The primary key (PK) is a combination of the fields in the PKs of the
"Subjects" table.

If the PK of Subjects was SubjectTitle then you would need two fields
called say SubjectTitleA and SubjectTitleB and these togther would be the
PK of the linking table.

The linking table PK is exactly the same, in principle, as one between
say Product and Supplier when a Product can be supplied by many Suppliers
and Suppliers supply many products the ProductSupplier table has a PK
(SupplierName, ProductBarCode) assuming Supplier has a PK (SupplierName)
Product has a PK (ProductBarCode).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

I also have wondered about this sort of thing. In the instance you
describe, is the primary key from each instance of the Subjects table
linked to the same foreign key field in the junction table?

in message Establish a many to many link between the Subjects table and the
Subjects table (not a typo).

In Access you do this by adding the Subjects table to the Relationships
window twice and link each instance to the new link table.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
I've come across this problem a few times in relational databases and
never
quite known how to solve it. As an example, I have a website database.
It has
[Subjects] and [Articles]. One subject can have many articles. But
some
Subjects can also have associated Subjects (taken from the same
table). Would
you overcome this by making a union table, joined to the [Subjects]
table,
that lists all the other associated Subjects?

Hope this makes sense.

Yo Yo Ma.

 

[BruceM]

I understand the distinction. I should have said "the field that is
designated as the PK".

Yes.

BTW a PK is NOT a field it is an index. A PK (indeed any index) can be up
to 10 fields in Jet and 16 in SQLS.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Thanks for the reply. I think I understand you to say that the PK field
in the Subjects table relates one-to-many to two separate fields in the
junction table.

The primary key (PK) is a combination of the fields in the PKs of the
"Subjects" table.

If the PK of Subjects was SubjectTitle then you would need two fields
called say SubjectTitleA and SubjectTitleB and these togther would be
the PK of the linking table.

The linking table PK is exactly the same, in principle, as one between
say Product and Supplier when a Product can be supplied by many
Suppliers and Suppliers supply many products the ProductSupplier table
has a PK (SupplierName, ProductBarCode) assuming Supplier has a PK
(SupplierName) Product has a PK (ProductBarCode).

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
I also have wondered about this sort of thing. In the instance you
describe, is the primary key from each instance of the Subjects table
linked to the same foreign key field in the junction table?

in message Establish a many to many link between the Subjects table and the
Subjects table (not a typo).

In Access you do this by adding the Subjects table to the
Relationships window twice and link each instance to the new link
table.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
I've come across this problem a few times in relational databases and
never
quite known how to solve it. As an example, I have a website
database. It has
[Subjects] and [Articles]. One subject can have many articles. But
some
Subjects can also have associated Subjects (taken from the same
table). Would
you overcome this by making a union table, joined to the [Subjects]
table,
that lists all the other associated Subjects?

Hope this makes sense.

Yo Yo Ma.