2

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?

0

Here are the 4 possible solutions I can think of

  1. encode everything in one column, using a number type as bitset

    pro:

    • 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

    con:

    • 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

  2. encode everything in one column, using a string of "0" and "1"

    pro:

    • still very compact in memory
    • easily extendable, like the first approach, but no inherent limit n

    con:

    • queries are not so hard as above, but will need string operations
    • still no indexing
  3. use a different column for each status

    pro:

    • indexed queries are easy
    • query results are easily readable / don't need specific decoding

    con:

    • only extendable by changing the schema
    • needs more storage
  4. use a link table (Michael Durrant's solution)

    pro:

    • can be extended at run time, without any schema change
    • indexed queries are possible

    con:

    • 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.

5

For a relational database I would suggest you store these in separate status and item_statuses tables that have their own primary keys.

The 'join' table between item and status, e.g. item_statuses has the keys from each of the two tables, e.g. item_id and 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.

3

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.

  • -1, your answer is IMHO much too single-edged (I guess it could be improved by choosing your words a little bit more moderate). Though you are right that using different columns is "best practice" under normalization aspects, it is only one possible solution among at least 4 coming directly to my mind, and each one with different trade-offs. And if querying for a status in a bitfield is really "a nightmare" depends on the SQL dialect and if it supports bit operations, and how the status flags are encoded. – Doc Brown Oct 19 '13 at 18:38
  • I think key point here is " if the status can be more than one at a time" andd the status given active inactive fast moving and batch do look like that to me. – manocha_ak Jun 14 '18 at 18:08
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.