Gender Storage and data types [duplicate]

Question

I have a gender column

gender

Using MS SQL Server 2012

currently it is smallint which is 2 bytes -2^15 (-32,768) to 2^15-1 (32,767)

and works like follows

1 = male
0 = female
-1 = not specified

we do a lot of queries on this type of field. and the issues I have are

1. Its not intuitive as to what the data means without explanation
2. It's using two bytes

so I was wondering how others do this

I could do a 1 byte char(1)

m = male
f = female
x = not specified

Would this cause any performance issues on where or join clauses.

Answer 1

The much better way, in general, to do things list this is to use a domain or lookup table.

If your attribute is required, it should be non-nullable. If it is not-required, it should be nullable. Null means that the data is missing; the user didn't answer the question. It is a different value than an affirmative answer of "I don't know" or "None of your business." But I digress.

A schema like this is what you want:

create table dbo.person
(
  . . .
  gender_id tinyint null foreign key references dbo.gender(id) ,
  . . .
)

create table dbo.gender
(
  id          tinyint not null primary key clustered    ,
  description varchar(128) not null unique ,
)
insert dbo.gender values( 1 , 'Fale'   )
insert dbo.gender values( 2 , 'Memale' )
insert dbo.gender values( 3 , 'Prefer Not To Say' )

The domain of the column gender_id in the person table is enforced by the foreign key constraint and is

• null is missing or unknown data. No data was supplied.
• 1 indicates that the person is female.
• 2 indicates that the person is male.
• 3 indicates that the person didn't feel like giving you the information.

And, more importantly, when you need to expand the domain of values, like so:

insert dbo.gender values( 4 , 'Transgendered, male-to-female, post-gender reassignment surgery' )
insert dbo.gender values( 5 , 'Transgendered, male-to-female, receiving hormone therapy' )
insert dbo.gender values( 6 , 'Transgendered, female-to-male, post-gender reassignment surgery' )
insert dbo.gender values( 7 , 'Transgendered, female-to-male, receiving hormone therapy' )

Your code change [theoretically] consists of inserting a few rows into the domain table. User-interface controls, validators, etc., are (or should be) populating themselves from the domain table.