CODEWITHSUNDEEP
mysqldatabase-designdatabase-schema
ProgrammerGirl
ProgrammerGirl

Reputation: 3223

Should "binary flags" for each user be placed in the main Users table or in its own "binary flags" table?

On one of my sites, I have a main Users table with each user's unique user id, e-mail address, password, etc.

I need to start keeping track of a lot of binary flags related to each user, such as whether they have confirmed their e-mail, whether they have posted a message, whether they have upgraded their account, whether they have done X, whether they have done Y, etc.

Each of these flags is a simple "0" (false) or "1" (true), and based on these flags, my site shows the user or does different things.

My question is, does it make more sense to add these binary flags to the main Users table or to create a separate table for the binary flags or something else?

Please try to explain your reasoning (and the advantages of your approach) so that I understand where you're coming from.

Upvotes: 2

Views: 151

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Do all these flags need to be stored or they can be calculated? For example, if user hasn't posted any message, this can be easily determined by querying the MESSAGE table.

Physically storing "calculable" flags is redundant and opens the possibility for data inconsistencies. For example what if user adds a message but a bug in your application prevents the flag update? Such "denormalization" may be justified for performance reasons, but only make this decision after you have measured the performance on realistic amounts of data and representative workloads.

OTOH, some flags may be "real" (e.g. whether the user has confirmed the e-mail). If such flags are relatively static (i.e. you know them in advance, at the time you are designing your data model), store them directly as simple boolean (or equivalent) fields in the USER table itself.

Only if you need to have a considerable run-time flexibility, consider using a separate FLAG table that is in N:1 relationship with USER table. This is a kind of EAV.

Upvotes: 3

Cynical
Cynical

Reputation: 9578

You have advantages in keeping them together and advantages in separating them: if you put the flags in the Users table, with a simple query on the user ID you have all the informations about that user, instead of using a join to retrieve them.

On the other side, having them on a separate table makes them "logically" separated from the data you have on the Users table, which might be completely unrelated (even if they talk both about the user), thus having a clearer database structure.

Another thing to take into account is how often you have to change and retrieve such datas: if, for example, you just need them on login, then you might want to keep them on the same table and get all the login data at once; instead, if you have to change them repeatedly, then your choice should be going on a different table.

That said, I would go for the two tables solution in any case, but that's just how I like to see them in the DB schema.

Upvotes: 0

Related Questions