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 5 (of 5 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy