Geek asks and answers

Use foreign key in definition table or use constants in code?

I would like to get people's opinions on best practice.

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

Scenario, There is a table called "car", "Car" has a column "size", For "size" there are only 2 possible values. 'Large and small' It is very unlikely that there will be new size values. for example, "MID" is not required. Therefore, size administration is not required.

Two ways to move forward: 1) make a table called "car_types" and get BIG and SMALL as two rows. Then enter the foreign key into the car table.

2) have const CAR_SIZE_BIG = 'BIG' const CAR_SIZE_SMALL = 'SMALL' in the 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 normalized. However, which one is a perceptual or better method?

Thank. Open for feedback.

+3
source share
2 answers

Option one is better, since the problem with option 2 is that it is completely legal, giving the machine the size of "edster"

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

With option 1, you could not do this (without first inserting into the car_size table)

This insert will do nothing

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

And if your car_size table contains only two records, this will result in an error with an error

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

FK (, BIG GRANDE, ..), , , .

+3

1, .

+3

More articles:


All Articles