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.

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.

more