Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - Should I use a junction table or not?

I am creating a new SQL Server 2008 database. I have two two tables that are related.

The first table looks like this:

 BRANDS // table name
 BrandID // pk
 BrandName // varchar

The second table looks like this:

 MODELS // table name
 ModelID // pk
 ModelDescription // varchar

Every brand will have at least one model and every model will belong to only one brand.

The question is, should I create a junction table like this

 BRANDS_MODELS // table name
 RecordID // pk
 BrandID
 ModelID

Or should I modify the MODELS table to include the BrandID like this

 MODELS // table name
 BrandID // 
 ModelID // pk
 ModelDescription // varchar

Thanks!

like image 733
Evik James Avatar asked Dec 09 '22 06:12

Evik James


2 Answers

If a model belongs to only one brand then you can put the FK to brand on the model table (your second approach). The first way, with the junction table, is for a many-to-many relation.

like image 60
Nathan Hughes Avatar answered Dec 29 '22 00:12

Nathan Hughes


Based on what you've said so far, I would leave out the junction table and use an ordinary foreign key in the MODELS table.

But if a model could move brands and you needed to maintain a current junction and history, a junction table has advantages over keeping history of the entire MODELS row when just a foreign key changes. Also if other things exist which might be associated with the relationship "entity" more than the MODEL entity it might make more sense to have a junction table. You can always make a unique constraint on ModelID in the junction table to ensure that the same model is not linked to multiple brands. So although a junction table is required to effectively implement a many-to-many relationship, it can also be useful for one-to-many relationships where that relationship itself has attributes.

like image 33
Cade Roux Avatar answered Dec 29 '22 00:12

Cade Roux