Post reply

Database Design for "order status"

  • March 30, 2007 at 11:21 am

    #177279

    I have a database that I have been working on that is going to used for controlling the status of orders in a warehouse.

    The database uses boolean fields to determine the order's current status.

    There are over 10 different statuses, and an order can also have multiple statuses; i.e. not printed and outstanding.

    Example of Field Name in the order table. I also include the order number and other information in this table.

    Processed 0/1

    Printed 0/1

    Outstanding 0/1

    Closed 0/1

    I am wondering if this is a good design, or if someone can provide me with another design method for this, as in my testing it can take a long to time to "select" closed orders for example.

    Regards

  • March 30, 2007 at 2:56 pm

    #697941

    Since an order can have several status and a status can be in effect for many orders, the relational representation is:

     

    Order table (OrderID PK)

    StatusTable (StatusID PK)

    and a resolution table OrderStatus (OrderID, StatusID PK)

     

    Regards,

    Carlos

  • April 1, 2007 at 5:51 pm

    #698014

    I have a GREAT implementation for you.  Use Binary Encoding.  With this mechanism, a single tiny/small/int field can represent multiple states at once.  Take a smallint.  Limit is +-32765.  Powers of 2 are 2, 4, 8, 16, 32, 64, 128, 256, 512 . . .

    Using bitwise operators, you can set (| or bitwise OR operator), or check (& or bitwise AND operator) any one of those values. 

    Say your starting value is 0, meaning all bits are off.  If you do this:

    SET Status = Status | 6

    you have just turned on the 2 and 4 bits.

    SELECT @bit1 = Status & 2 FROM Orders WHERE id = 123

    will tell you whether or not the 2 status is on or off.

    I have used this in a wide array of situations for various clients.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 1, 2007 at 7:30 pm

    #698021

    I like that !!! Thank you

  • April 2, 2007 at 10:38 am

    #698135

    Before you buy the bit datatype, consider that bit column indexes are usually valueless (because of the low selectivity of the column 1/0)

    In addition, you'll only be able to make sense of what is in the column by applying bitwise operators. 

    Regards,

    Carlos

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply