Hello all.

Suppose you have a set of tables, and one of them contains a field 'SEX',
lets say , the table Employee may have this field to identify the sex of an employee.
It is clear that this field will contain not much than 3 values - 'M" or 'male',
'F' or 'female', and NULL or 'unspecified'.

The main question is :

SHOULD WE ALWAYS CREATE A SEPARATE TABLE CONTAINING THESE VALUES AND
JOIN OUR TABLES ( that holds some info about sex ) WITH IT ?
[ create table sex (id int, sex char(1) null ); ]

It seems to me that according to database design and normalization rules, IT IS OBLIGING TO CREATE SUCH A TABLE. IS IT?

For something like gender I wouldn't bother creating a seperate table for it. Usually when I create seperate tables for something that belongs to another table (Gender belongs to Employee, ie employees have a gender) is when that item has attributes of its own that need to be organized. Employee's for example have resumes. It would kill your organization to put all of the fields corresponding to a resume into the employee table. So you create a seperate table for resumes and then under the resume field in employee you place information linking the employee to a particular resume. In this case you wouldn't even have to do that considering you can lookup to corresponding resume by the name of the employee. But in the case names were not unique you would have to develop a way to uniquly identify the row you need to find in the other table.

That's just my style by the way. Hope I helped.

Depends how you use it.

You might want to use a boolean value instead of a char(1) if you're worried about performance.

Thank you for answering.
But the truth is that if we dont create a separate table in this case, our database wouldn't follow the normalization rules.
In this particular case, if we use a boolean field, then we wont be able to break it ('cause we have only 3 possible values). But not all db systems have a boolean type. So, if we would use a tinyint or char or other 1-byte length type, there may be a situation when,
using just a field 'sex' (not a foreign key), we put into this field another value, different from ( '0', '1', null ) for the integer type or
('m', 'f', 'u' / null ) for char types.
You may say that "your application have to control this and not allow to do such things", but suppose you dont have any application (just sql tools) or the application have bugs and allow to insert any values into this field.

I would simply use an ENUM myself.

if your db system supports it
LOL

Just use a 1 char wide character column (m,f,n) and use CASE statements in your query

select fieldlist, case sex
	when 'n' then 'Not Specified'
	when 'm' then 'Male'
	when 'f' then 'Female'
	end as sex
from table

Originally Posted by Eugene_

But the truth is that if we dont create a separate table in this case, our database wouldn't follow the normalization rules.

Normalization suggestions. It's a best practice to normalize as much as possible, then denormalize for performance in many cases.

Whether you want a new field or a new table in the long run depends what you want to do. How much will joining another table cost, and how much will reading the extra column on every row will cost.

Originally Posted by Eugene_

You may say that "your application have to control this and not allow to do such things", but suppose you dont have any application (just sql tools) or the application have bugs and allow to insert any values into this field.

With just the SQL tools I would constrain the column to force a set of valid values.