-3

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
PassingThru
  • 1
  • 2
  • 1
    1. *Every **single** time*. Why do you think Scenario 2 is normalised? It breaks 1NF. – Thom A Aug 18 '23 at 13:32
  • ^ agreed. [Bad Habits to Kick : Putting an IDENTITY column on every table](https://sqlblog.org/2010/02/08/bad-habits-to-kick-putting-an-identity-column-on-every-table). – Aaron Bertrand Aug 18 '23 at 13:44
  • What is your 1 (specific researched non-duplicate) question? Where & how are you 1st stuck following what design method? [ask] [Help][How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Aug 18 '23 at 14:51
  • [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/3404097) – philipxy Aug 18 '23 at 14:51

1 Answers1

0

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).

philipxy
  • 14,867
  • 6
  • 39
  • 83
PassingThru
  • 1
  • 2