1

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 (simplified):

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?

Improve this question

6 Answers 6

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Agree. I think enum is the best option to go for.
I was advocating the NULL in this case, and an Enum when you just want a general three options :)
2

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.

Improve this answer

2 Comments

You say handling NULL in code is 'bad practice'? And what happens to your code when you come across a 3 or 4?
@rdkleine it wouldn't. You are never setting 3 or 4. However, if you keep default as NULL, you will have to handle that in code.
1

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.

Improve this answer

2 Comments

Thanks for answer, that is just semantics though, I'm querying as to if using null in this way is going to cause problems later if it's bad design, or if it's OK as I don't have much experience with it. I'm aware of the alternatives.
Edited my answer to illustrate future usage
1

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.
Improve this answer

2 Comments

Sylvain, Your second sentence surely contradicts the first. Tom says he is using NULL to represent the fact that someone has not voted. Therefore it is a known fact that "hasVotedYes" should be false (or No). Null is not designed for this usage and using it this way is bound to lead to incorrect or ambiguous results.
Good point. If someone did not vote yet, he surely did not vote yes. I buy it.
1

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: three value logic

Improve this answer

1 Comment

This truth table is particularly instructive about some of the problems with nulls. It's also interesting because not all SQL DBMSs support it - many DBMSs don't even have a logical or "boolean" type and don't allow logical equality type of comparisons (the 5th column in the table) at all. The SQL 1999 standard does have such a type, which is misnamed as Boolean. It doesn't really make sense of the usual logical meanings of true, false and unknown. If proposition p is true and proposition q is unknown then p and q certainly have different logical values, so p=q should be false, not unknown.
0

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.

Improve this answer

2 Comments

The record IS the record of if they are allowed to vote, bundled with their voting data. I see this is probably not the best design, but essentially it's a small components so I opted to go for this solution.
You are right, it's a bad design, it's inaccurate and the results you get from it will probably be wrong. I don't know what "small components" means or why that would make you choose to do it this way.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.