I'm developing a new SQL Server 2008 database. I've two two tables which are related.
The very first table appears like this:
BRANDS // table name BrandID // pk BrandName // varchar
The 2nd table appears like this:
MODELS // table name ModelID // pk ModelDescription // varchar
Every brand may have a minumum of one model and each model will fit in with just one brand.
Now you ask ,, must i produce a junction table such as this
BRANDS_MODELS // table name RecordID // pk BrandID ModelID
Or must i customize the MODELS table to incorporate the BrandID such as this
MODELS // table name BrandID // ModelID // pk ModelDescription // varchar
If your model goes to simply one brand you'll be able to place the FK to brand around the model table (your next approach). The very first way, using the junction table, is perfect for a many-to-many relation.
According to what you've stated to date, I'd omit the junction table and employ an regular foreign type in the MODELS table.
But when one could move brands and also you required to conserve a current junction and history, a junction table has advantages over keeping good reputation for the whole MODELS row when only a foreign key changes. And if other activities exist which can be connected using the relationship "entity" a lot more than the MODEL entity it could be preferable to possess a junction table. You could create a unique constraint on ModelID within the junction table to make sure that exactly the same model isn't associated with multiple brands. So although a junction table is needed to effectively implement a many-to-many relationship, it is also helpful for just one-to-many associations where that relationship itself has characteristics.
Junction tables can be used for many-to-many associations which doesn't appear to become a good fit here.
For instance, you wouldn't wish to enable the development of a Honda Social along with a Toyota Social. That's a good example of car's make/model relationship but should match your brand/model relationship.