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 ?