SQL Should I use a connection table or not?

Question

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

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 

Each brand will have at least one model, and each model will belong to only one brand.

The question is should I create a connection table such as

  BRANDS_MODELS // table name RecordID // pk BrandID ModelID 

Or do I need to modify the MODELS table to include a BrandID like this

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

Thanks!

Answer 1

If the model belongs to only one brand, you can put FK in the brand on the model table (your second approach). The first way, using the conversion table, is with a many-to-many relationship.

Answer 2

Based on what you said so far, I will not leave the connection table and use the usual foreign key in the MODELS table.

But if the model can move brands, and you need to maintain the current transition and history, the connection table has advantages over saving the history of the entire MODELS line when only the foreign key is changed. Also, if there are other things that may be related to the entity relation more than the MODEL object, it might make sense to have a join table. You can always make a unique restriction on ModelID in the connection table to make sure that the same model is not associated with several brands. Therefore, although a join table is required to effectively implement a many-to-many relationship, it can also be useful for a one-to-many relationship in which the relationship itself has attributes.

Answer 3

Connection tables are used for many-to-many relationships that do not seem to be suitable for this.

For example, you do not want to include the creation of the Honda Civic and Toyota Civic. This is an example of a car / model relationship, but should match your brand / model relationship.