CODEWITHSUNDEEP
sqldatabasedatabase-design
jayelm
jayelm

Reputation: 7667

db best practices: data + boolean column or just data?

I'm designing a table in my Postgres database for a set of games. Each game can either be maintained by an administrator or have no administrator. If the game has an administrator, there will be an admin_id field which contains the id of the administrator.

My first thought is having an admin_id data field which is NULL if the game has no administrator. If the game does have an admin, admin_id will contain a user id of type integer. Like such:

admin_id
--------
15282    -- admin
9283     -- admin
NULL     -- no admin
81234    -- admin

The other alternative is an additional boolean column, i.e. has_admin, which is true and false for obvious reasons:

has_admin | admin_id 
----------|----------
true      | 15282     
true      | 9283     
false     | NULL
true      | 81234

My question is, is the has_admin column just unnecessary fluff? Or are there valid reasons to keep it there, if, for example, I'll be querying the database frequently to find whether games have admins?

Upvotes: 1

Views: 173

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Adding has_admin is a kind of de-normalisation, and you therefore risk introducing anomalies such as:

true      | NULL     
false     | 9283

It is sometimes necessary to do such things due to efficiency reasons, but in this case you can easily derive has_admin as:

admin_id is not null

so there really is no reason to add such attribute

Upvotes: 1

nicom974
nicom974

Reputation: 176

Well, a Boolean does not really take much space, so this would not cause any issue size-wise. However, it is cumbersome to have has_admin, because like you said:

if ($obj->admin_id){
    // do stuff
}

is the same as:

if ($obj->has_admin === true){
    // do stuff
}

And since it is necessary to always check what you get from your db

if ($obj->has_admin === true && $obj->admin_id){
    // do stuff
}

which is totally useless :)

Hope this helps

Upvotes: 1

Related Questions