Database normalization for 1 to many, many to many, many to 1

Question

For both scenarios I'm using the same Color [lookup] table but storing the data differently. Here's the spec for the records to store:

• Car with Id 1 is available in 3 colors: Blue, Red, White.
• Car with Id 2 is available in 2 colors: Blue, Red

Color

Id	Name
1	Blue
2	Red
3	White

Scenario 1

Car

Id
1
2

CarColor

Id	CarId	ColorId
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2

Scenario 2

Car

Id	ColorIds
1	1,2,3
2	1,2

Which is preferred for storing the data?

I've used the structure in both scenarios. Scenario 1 seems to be the most flexible. Needing advice on what is preferred moving forward.

Answer 1

David Browne's comment

For 1) Remove the ID column in CarColor, and make (CarId,ColorId) the primary key. The ID is useless, and you need a unique index on (CarId,ColorId) anyway.

lead me to this article and has convinced me to use that approach. Specifically, strive for a Natural key when possible, and don't store multiple keys in a column (like scenario 2).