How to handle multiple statuses for a table entry, for example an item table may have an active, inactive, fast moving, and/or batch statuses. And I wanted to handle them in single column with VARCHAR type. Also I might set each of those attributes as a boolean with different columns. But I am not sure what consequences this might lead to. So if you have experienced such situations which one would be the best way to handle it?
Here are the 4 possible solutions I can think of
encode everything in one column, using a number type as bitset
smallest amount of memory needed
easily extendable to the point where you reach n flags, where 2^n is the maximum value of the number type choosen
queries for all items with a specific status are hard, since most sql dialects don't support bit operations
no indexing for a single flag, a query for all items with s specific state will always result in a full table scan
encode everything in one column, using a string of "0" and "1"
- still very compact in memory
- easily extendable, like the first approach, but no inherent limit n
- queries are not so hard as above, but will need string operations
- still no indexing
use a different column for each status
- indexed queries are easy
- query results are easily readable / don't need specific decoding
- only extendable by changing the schema
- needs more storage
use a link table (Michael Durrant's solution)
- can be extended at run time, without any schema change
- indexed queries are possible
- queries are more complicated / need a join
- inserts and updates are more complicated, instead of one record you now have to add two or more records for storing the same information
So all of these solutions have different trade-offs, and you should make your decision based on the use cases / queries you expect, and the amount of extensibilty you need.
For a relational database I would suggest you store these in separate
item_statuses tables that have their own primary keys.
The 'join' table between
item_statuses has the keys from each of the two tables, e.g.
status_id. It also has its own primary key ('
id') as my experience says always have a primary key that has no business meaning and both of the other keys have business meaning so the composite of them is not a good primary key.
Storing literal values as
varchar can easily leads to dupes, typos and other issues.
If you have a status that can be multiple different states, but never more than one at a time, then have one column. Otherwise, separate columns is a must.
You never want to store multiple things in a single column. Querying against it would be nightmare. You may only be thinking about loading the data in one particular way now, but in 3 years time when somebody asks you to run a report in a specific way and you realize there is no real way to do this because there are 10 things all stored in one concatenated column, then you have a heavy redesign on your hands.
Using a single column sounds appropriate, as long as the different status values are legitimately different states of the same thing. This is basically what razethestray has said as well.
If you create a separate status lookup table like Michael Durrant suggests, then you could optionally add your various Boolean columns to that table.
Adding additional flag columns to a lookup table is not a common technique, in my experience, but I've tried it a bit and like how it lets me keep my application code from magically knowing that status "In Progress" means "cannot add items", for example. Instead, I could have an AllowsAddingItems column on my status table, and the value in that column for the "In Progress" status would be false. This way, my application code doesn't have to be as aware of what all of the different possible statuses are; it just checks that AllowsAddingItems attribute associated with the current status.