Databases, bad practise to use null as ternary option?

I've never used null before really, I tend to avoid using it and having it existing in my data. However I recently designed this table (s开发者_如何学Cimplified):

tblPeopleWhoNeedToCastVotes
User |  hasVotedYes

In this scenario, hasVotedYes can either be null, true or false. Null indicates they have not yet cast their vote (useful information).

Is this bad practise or fine?

This is really what NULL is intended for - where the data is not available. From Wikipedia:

Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database.

I wouldn't recommend using it as a general "third option", I'd go with a tinyint and map it to an Enum in code instead. However, for Yes/No and an Unknown, I'd say a NULL is probably the best way, as reading 0/1/2 in the database would be less clear.

I would rather prefer a DEFAULT value instead of NULL. So, In database like MySQL I will create a column

hasVotedYes TINYINT(1) DEFAULT 0

when user votes "against" I will change to 1, if user votes "in favor", I will mark it as 2. However, NULL as default is NOT a bad practice, till you handle NULL object in your application code.

Thinking a bit more, I guess default values are even better idea. For example you want to filter users who voted in favor, voted against, or not voted -- you will create a prepared statement something like

 ... where hasVotedYes = ?;

While in case of NULL default, you will be writing two types of queries.

... where hasVotedYes = ?

This works for voted in favor or against case.

.... where hasVotedYes is NULL;

This for not voted case.

I would go for

tblPeopleWhoNeedToCastVote
User | Voted

Where votes is nullable bit: 1 = Yes, 0 = No, Null = not voted.

Otherwise (when not using null) you need to know:

A: Has the person voted

B: What did he vote.

By not using NULL B would be defaulted to 0. This could be annoying when wanting to create a query of all people who voted Yes (1) or No (0), then you must check if the person has voted.

When using NULL you can simple query for 1 or 0.

Null has been built for this usage. It (should) means : not shure, don't know.

The only drawback is when you want to know, say, the number of people who didn't vote Yes : you may have to convert null to something or the result will not be correct.

 select * from tblPeople as t where t.hasVotedYes <> 'Y'; -- Don't counts nulls

 select * from tblPeople as t where nvl(t.hasVotedYes, 'N') <> 'Y'; -- counts nulls.

I use NULL for UNKNOWN but you should also take three value logic into consideration if your going to be doing a lot of search clauses on the data to make sure you don't make a mistake, see this image:

Null gets used for all sorts of things but usually it leads to contradictions and incorrect results.

In this case it seems that your dilemma stems from trying to overload too much information into a single binary attribute - you are trying to record both whether a person voted and how they voted. This in itself is probably going to create problems for you.

I don't really see what you hope to gain by using a null to represent the case where a person hasn't voted. Why don't you just not store the information until they have voted. That way the absence of the row clearly indicates that a person hasn't voted yet.