Re: Determining if a bit is set

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dan Holmes (danholmes_at_bigfoot.com)
Date: 07/21/04 

Date: Wed, 21 Jul 2004 08:20:14 -0400

>
> Joe Celko wrote:
>
>> I now have an article on this topic posted.
>> http://www.dbazine.com/celko23.shtml
>>
I am afraid i still don't get it. Like my post in "Bit Math..." I have
an order that needs to be picked. I have four methods of picking none
of which are mutually exclusive. I could pick by method one, two, three
or four. I could even start picking on method one and stop then start
using method three. Is the relational way to have a table with
pickmethods with DRI to a table like the following.

I didn't add DRI from orderrelease to orderreleasepickmethods but in
this model it should be there.

Is this the relational solution? It seems harder.

CREATE TABLE OrderHeader (
        OrderID char (25) NOT NULL ,
        Stage char (1) NOT NULL ,
        Status char (1) NOT NULL ,
        CustomerID char (25) NOT NULL ,
        OrderDate datetime NOT NULL
) ON PRIMARY
GO

CREATE TABLE OrderRelease (
        OrderID char (25) NOT NULL ,
        ReleaseID char (25) NOT NULL ,
        Stage char (1) NOT NULL ,
        Status char (1) NOT NULL ,
        ReleaseDate datetime NOT NULL ,
        ToShipDate datetime NOT NULL ,
        Comment varchar (255) NOT NULL ,
        DefaultStageLoc char (25) NOT NULL ,
        CarrierID char (25) NOT NULL ,
        AddressID char (25) NOT NULL ,
        RouteCode char (25) NOT NULL ,
        PickMethod int NOT NULL
) ON PRIMARY
GO

ALTER TABLE OrderHeader ADD
        CONSTRAINT PK_OrderHeader PRIMARY KEY NONCLUSTERED
        (
                OrderID
        ) ON PRIMARY
GO

ALTER TABLE OrderRelease ADD
        CONSTRAINT PK_OrderRelease PRIMARY KEY NONCLUSTERED
        (
                OrderID,
                ReleaseID
        ) ON PRIMARY
GO

ALTER TABLE OrderRelease ADD
        CONSTRAINT FK_OrderRelease_OrderHeader FOREIGN KEY
        (
                OrderID
        ) REFERENCES OrderHeader (
                OrderID
        ) ON DELETE CASCADE
GO

CREATE TABLE OrderReleasePickMethods (
        OrderID char (25) NOT NULL ,
        ReleaseID char (25) NOT NULL ,
        methodID int NOT NULL
) ON PRIMARY
GO

CREATE TABLE PickMethods (
        methodID int NOT NULL ,
        description varchar (50) NULL
) ON PRIMARY
GO

ALTER TABLE OrderReleasePickMethods ADD
        CONSTRAINT PK_OrderReleasePickMethods PRIMARY KEY CLUSTERED
        (
                OrderID,
                ReleaseID,
                methodID
        ) ON PRIMARY
GO

ALTER TABLE PickMethods ADD
        CONSTRAINT PK_PickMethods PRIMARY KEY CLUSTERED
        (
                methodID
        ) ON PRIMARY
GO

ALTER TABLE OrderReleasePickMethods ADD
        CONSTRAINT FK_OrderReleasePickMethods_PickMethods FOREIGN KEY
        (
                methodID
        ) REFERENCES PickMethods (
                methodID
        )
GO