3

I'd like to get people's opinions on best practise.

Is it better practise to use foreign key to definition table or use constants in code?

The scenario is, There is a table called 'car' The 'car' has a column called 'size' There are only 2 possible values for 'size'. 'BIG' and 'SMALL' It is very unlikely that there will be new values of size. e.g. 'MEDIUM' will not be required. Hence no administration of size is required.

The two ways to go forward are: 1) make a table called 'car_types' and have BIG and SMALL as two rows. Then have a foreign key to the 'car' table.

2) have a const CAR_SIZE_BIG = 'BIG' const CAR_SIZE_SMALL = 'SMALL' in a class. Then, the 'size' column in the 'car' table is of type VARCHAR(10). And it stores either 'BIG' or 'SMALL'

I understand that option 2 is not Normalised. However which is the perferred or better method?

Thanks. Opened for your feedback.

edster
  • 116
  • 2
  • 10
  • 1
    Related question: [Foreign key vs check constraint for integrity](http://stackoverflow.com/questions/9347756/foreign-key-vs-check-constraint-for-integrity/) – ypercubeᵀᴹ Apr 19 '12 at 06:08

2 Answers2

3

Option 1, which keeps all of the data definitions in the database layer.

hkf
  • 4,440
  • 1
  • 30
  • 44
3

Option one is better since the problem with option 2 is that this is perfectly legal, giving a car the size of "edster"

INSERT INTO  car
 (Car_size, color)
Values
 ('edster', 'red')

With option 1 you couldn't do that (without first inserting into the car_size table)

This insert would fail to do anything

INSERT INTO car
 (Car_size_id, color)
SELECT 
    car_size_id , 'red'
FROM
    car_size 
WHERE 
    car_size.name = 'edster'

And assuming your car_size table only contained two records this would fail with a contraint error

INSERT INTO Car
 (Car_size_id, color)
Values  
 (3,'red')

There are other advantages to using a FK, (e.g. easy to change a label BIG to GRANDE, a clearly defined set of possible values from database alone, etc), but they're probably inconsequential in this case.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • I like your answer. So you are confirming suggestion of option 1? Could you clarify what is wrong with the INSERT that you write? Just to give some reasons why that is wrong? Then I will select your anwwer :) – edster Apr 19 '12 at 07:09
  • I hope that clarifies why I perfer option 1 in this situation. However if you really only had two car_types and went with a Varchar(10) it probably wouldn't be the end of the world – Conrad Frix Apr 19 '12 at 14:43