1

I need to extend my existing table with status column.
I am in doubt how to do that in most appropriate way regarding of future use of that status and performances of existing table.

My table is around 3,7 MIL records, and it is heavy read and write table. Table represent stock (warehouse) of items (goods). It holds floats for many kind of prices and amounts, primary key is composite by item_id and warehouse_id.

Here is how I see ways to implement adding status of an item.

To add three more columns for statuses, each one column will represent some of statues, Something like 'allow_sell','allow_orders','allow_returns' I like this way because it uses BITs, (is it faster than varchar, or integer ?) and easy to understand what each status represents and which combination of statuses are seted on an item. In this way

To add one integer column called status_id and to add one more table statuses_of_item where I can hold description of each status and add statues as many as I wish.

To add one varchar(10) column where status will be written as dresption ('denay_all','allow_all', 'allow_orders_wo_sels' etc)

Have in mind that status is part of business logic and it uses only internal, Representation of statues are not displayed in SIMPLE WAY, statues not shown to users, it is only way how system should handle some situation in different places.

I think that 70% of items inside table will have default (same) status. My database system is MS Sql Server 2008R2.

How I should design my table to include stautes ?

Improve this question
2
  • 1
    Perhaps a useful read: sqlperformance.com/2012/08/t-sql-queries/dry-principle Commented Sep 5, 2013 at 16:40
  • So these are attributes, basically, not statuses. The conceptual difference is important: purely logically, an instance of an item can only have one status, but it can have many attributes. While you have only thought of three now, how likely will it be that you will add many more attributes in the future? Commented Sep 5, 2013 at 17:01

3 Answers 3

Reset to default
4

In my experience, trying to encode multiple data points into a single column always ends up being more trouble than it's worth. Sure, it seems cool and clever to use BITWISE operators, but there are many things that go wrong and it won't always be efficient to test those bits without cumbersome and unintuitive workarounds. It's the same reason we stay away from storing comma-separated lists, JSON strings etc. in a single column - eventually you care about viewing or filtering on those distinct bits which you now have to extract, sometimes expensively.

With the information I have, my vote is for three separate BIT columns. They will still collapse to similar storage patterns as a single column with the three bits on/off, and can be made more efficient individually and across the board in several ways, including:

  • data compression
  • sparse columns
  • filtered indexes (e.g. WHERE allow_returns = 1)

Someone else advocated for three CHAR(1) columns. These do not benefit from storage collapse and also require a check constraint, making them less than ideal in my mind.

Now, my answer might change if you say, "well, what if I might add 15 other attributes in the future?" I certainly don't think it's wise to build the columns this way if they're not relatively static - changing the schema (and therefore all of the code and interfaces to it) for every new or changed attribute is going to be a royal pain. So in that case you might want to consider EAV - where the attributes are not part of the metadata but part of the data. There are a lot of objections to EAV, mostly around performance and the difficulty in enforcing constraints (in this case unlikely to be an issue if all of these attributes are either on or off), but it worked quite well for us at my previous job. You might model it like this:

CREATE TABLE dbo.Attributes ( AttributeID TINYINT PRIMARY KEY, Name VARCHAR(32) NOT NULL UNIQUE ); CREATE TABLE dbo.ItemAttributes ( ItemID INT NOT NULL FOREIGN KEY REFERENCES dbo.Items(ItemID), AttributeID TINYINT NOT NULL FOREIGN KEY REFERENCES dbo.Attributes(AttributeID), Status BIT NOT NULL, PRIMARY KEY(ItemID, AttributeID) );

And again, you can have filtered indexes here to make certain queries much more efficient, such as (imagine the AttributeID for "allow returns" is 10):

CREATE INDEX optAllowReturns ON dbo.ItemAttributes(ItemID) WHERE AttributeID = 10 AND Status = 1;

If you have certain attributes that are not on/off (for example, three states of manufacture or shipping), you can change the Status column to:

Value TINYINT NOT NULL

This can double as an on/off value for attributes that are represented that way, and as tri- or more-state value for attributes that require more than simple on/off. You can also reflect which type is which in the metadata of the dbo.Attributes table.

Improve this answer
8
  • +1 for informative answer. I'd be interested to see though if in real life all this bit collapsing, compression etc. actually have any performance benefits over good ol' char(1)'s. I would imagine that if I were to index any of those 1-bit columns, or even all three of them, the index key will actually have 1 byte for each column anyway. Commented Sep 5, 2013 at 18:59
  • @mustaccio still not clear I understand your insistence on using CHAR(1). As I already pointed out, the OP does not expose these columns to users. And unlike BIT, you have to add check constraints, which are not free. So what does it gain you? Commented Sep 5, 2013 at 19:03
  • May be it's not much but maintainability. E.g. after some time you will want allow_returns to indicate not only "1" or "0", but also "yes, if in the original packaging". After some more time you will want to expose this column to the client, be that a human or an XML-based API call. Also, given the current state of education, it's not guaranteed that a future business analyst will understand "1" and "0" as easily as "Y" and "N" when trying to reverse engineer metadata for the then-legacy system... Commented Sep 5, 2013 at 19:31
  • @mustaccio well I think that's a very subjective thing and very hand-wavy. The constraints alone point me the other way. It is also not crazy to change a BIT to TINYINT and use an enum with a foreign key, if you anticipate those kinds of changes. Not every piece of data in a database needs to be understood by the business analyst, especially when those columns are not exposed to them directly, but rather through checkboxes, drop-down lists, etc. I still don't feel you've made a strong case that CHAR(1) has any significant advantage over BIT in this case. Commented Sep 5, 2013 at 19:36
  • I'm not really trying make a case for CHAR columns; just want to understand advantages of the alternative. And what's wrong with check constraints? Commented Sep 5, 2013 at 19:42
1

The best approach is to model each independent data item as its own predicate (column).

If you are sure that each of your status codes will remain binary, then use a bit column for each. Bit is an efficient data type for storage. Note, however, that you shouldn't try to filter (WHERE) on a bit field if you can help it. Bit is not generally something you can effectively index.

If you choose to have a status column which is a foreign key to a status description lookup table, then have one FK status column for each independent status. Don't comingle selling, ordering and returning.

Improve this answer
3
  • I do including statuses because of filtering, One of my common task is to extract only items with some status for one warehouse Commented Sep 5, 2013 at 11:51
  • 1
    However filtered indexes with where clauses on bit columns can be very effective. Commented Sep 5, 2013 at 16:37
  • @adopilot - Depending on how selective your bit columns are, full table indexes on bit columns can be helpful. In other words, if only a small minority of your rows have a bit flag with a given value (0 or 1) and you are always looking for that more rare value, then a full table index will be helpful. In other conditions, you can use filtered indexes as correctly suggested by Aaron Bertrand. Commented Sep 5, 2013 at 17:56
0

A single 3-bit column, as well as 3 separate 1-bit columns, will occupy 1 byte of storage for each row, which saves you a whopping 7 MB for the entire table, compared to using human-friendly CHAR(1) columns for each flag, such as ALLOW_RETURNS CHAR(1) CONSTRAINT CHECK (ALLOW_RETURNS IN ('Y','N')).

Improve this answer
5
  • You're forgetting that up to 8 BIT columns can collapse into a single byte, so 3 individual BIT columns can also be accomplished without this "whopping" 7 MB of extra space (I don't know that you really need to make the storage a more friendly Y/N - are people fiddling with the bits on disk, or are the options exposed to them by checkboxes etc. at the UI layer?). Also consider sparse columns, compression, etc. Commented Sep 5, 2013 at 16:39
  • I think that is exactly what I said - the 3 bit column will occupy 1 byte, which gives you insignificant space savings. I doubt the performance difference between 3 bit columns and 3 char(1) columns will be noticeable though. On the other hand, in my view character flags are better from the application development and maintenance perspective, because they are self-documenting. Commented Sep 5, 2013 at 16:47
  • Oh, the wording made me think you were advocating a single column that contained a bitwise value. Suggest re-wording from "a 3-bit column" to "3 bit columns". Also 1 (true) and 0 (false) are also IMHO self-documenting (even though the OP said "not shown to users"), and as BIT columns they have the benefit of collapsing storage-wise and also not requiring check constraints. Commented Sep 5, 2013 at 16:48
  • Can you show the table definition that includes a single 3-bit column? Commented Sep 5, 2013 at 19:45
  • What I meant was a tinyint with the bit map for the three indicators; you would only need 3 bits out of 8 but would store 8 anyway. Commented Sep 5, 2013 at 19:53

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.