A Simple Question of Designing a Foreign Key Database

Question

I have a simple question about creating a database ...

Let's say we have Table Customerwith some fields:

(PK) Id, 
Firstname, 
Lastname, 
Address, 
City, 
(FK) Sex_Id...

So...

It would be nice to have an extra table Table Sexwhere the data about Sex ('M', 'W') will be saved?

Sex_Id,
Value

or should the Sexvalues ​​('M' or 'W') be stored directly in the table Customer? What about request speed, etc.?

Thanks in advance, best regards.

Answer 1

Or you can use an existing standard. ISO 5218 covers four codes:

0 = Not Known
1 = Male
2 = Female
9 = Not applicable (lawful person such as corporation, organization etc)

ISO 5218 / .

, , , ( ), .

Joe Celko SQL - ( ) .

Answer 2

, : 2 , BOOL 0 = 1 = (, , ). ( , ), , , "", 0 , "", 1 .

Answer 3

Sex? , .

Answer 4

, "M" "W", ( ), ; / ( ) , - .

, Sex, , , , , .

Answer 5

.

@marc_s has the right idea here to add a nice CHECK CONSTRAINT to make sure that local values ​​are in the correct subset.

Now, if your example contains additional attributes of a related object, such as a "name" or "description" or additional links to other objects, such as an "alias" or some date range, then absolutely "yes", create another table.