Junction tables

[Siegfried]

Dear experts,

Can anyone explain me the diffference between following two tables?

tblSupplierProducts
PK SupplierFK (number)
PK ProductFK (number)
PK BrandFK (number)

tblSupplierProducts
PK SupplierProductsPK (autonumber)
SupplierFK (number)
ProductFK (number)
BrandFK(number)

Somehow it confuses.

Thanks.

 

[plog]

I can only read what you wrote, so its your fault if I come off as a smart ass:

The second table has a primary key field.


Now allow me to try to read into what you wrote:

The purpose of that primary key in the second table is to allow another table to use that primary key as a foreign key. That way a specific record could be referenced. Without a primary key it wouldn't be possible to reference a record inside it without that outside table storing all 3 values again.

 

[jdraw]

You haven't explicitly told us the structure of the tables.Junction tables can have a compound Primary Key.
Or they can have a distinct Primary key, and use the pk fields of the each of the related tables in a composite unique index.

tblLandOwners
LandOwnerID PK
PeopleID FK to tblPeople
LandID FK to tblLand

The fields in purple are used together to make a unique composite index to prevent duplicate entries.

 

[Siegfried]

Thanks Plog & Jdaw for your reply and feedback, appreciate your help with my question.

I started doubting on when to use a junction table with a distinct primary key and when to use one with composite keys. I got confused about this and thought that only one of both options would be correct instead of both being possible in Access.

Do would like to ask your furhter advice though if you allow:

In my example I have this junction table based on following three tables:

tblSupplier
PK SupplierPK(autonumber)
SupplierName (tekst)
City (tekst)
CountryFK (number)

tblProducts
PK ProductPK (autonumber)
ProductName (tekst)

tblBrands
PK BrandPK (autonumber)
BrandName (tekst)

Junction table:

tblSupplierProducts
PK SupplierFK (number)
PK ProductFK (number)
PK BrandFK (number)

The database holds the data of suppliers and the products they are offering, some products have a brandname. Though not every product Always has a brand!
For instance:
supplier Fuji sells winter clothing withoug brand
supplier Sinwa sells winter clothing (without brand) but also winter clothing of brand The North Face.
supplier RMS sells pyrotechnics
supplier Fuji sells pyrotechnics brand Viking.

In my junction table? How do I enter a supplier and product that doesn't have a brand? To avoid duplicates, records are unique and I have to enter a value in brand...? I could enter a ' - ' in brand for any supplier product that doesn't have a brand but that's not the correct approach.
Would appreciate your input.

Thanks.

 

[jdraw]

No part of a Primary Key can be Null.
A compound Primary Key tends to be cumbersome/confusing if the table is involved in other relationships(additional junction..)

I you use a single PK in a junction table, you should create a composite unique index on the Foreign keys of the original tables to prevent duplicate entries.

Good video on Many to Many and junction table is here

 

[plog]

Though not every product Always has a brand!

That maybe true, but 'No Brand' can still be a brand value, thus giving every permutation 3 non-null values:

Fuji, Ski Hat, No Brand
Sinwa, Parka, No Brand
Sinwa, Parka, The North Face
RMS, Launch Tube, No Brand
Fuji, Shell #34, Viking


By making 'No Brand' a brand value, you can ensure every permutation has 3 non-null values and use that as a compound key.