MSDN Magazine - September 2008 - (Page 60)

hold a reference to a bill and a reference to a parts list. Figure 3 shows an entity relationship diagram including this extra table. A BOM System in SQL Server 2005 Now that you’ve seen the theory underpinning a BOM system, it’s time to implement one. The procedure is straightforward. 1. Create the database 2. Create the tables shown in the entity relationship diagram 3. Add the constraints required on the appropriate tables 4. Configure access permissions on the tables as needed 5. Populate the tables with test data 6. Write and execute queries against the tables in order to validate the design Here is the TSQL code for creating a sample database. You will likely need to change the filename paths for use on your system: create name size log on name size database [dbBom] on primary ( = n'dbBom', filename = n'c:\msdnmag\dbBom.mdf', = 50mb , maxsize = unlimited, filegrowth = 2mb ) ( = n'dbBom_log', filename = n'c:\msdnmag\dbBom_log.ldf', = 10mb , maxsize = 2048gb , filegrowth = 10%); alter table dbo.part add constraint pkPart primary key clustered(partID); alter table dbo.bill add constraint pkBill primary key clustered(billID); alter table dbo.partList add constraint pkPartList primary key clustered(partListID); alter table dbo.billPartList add constraint pkBillPartList primary key clustered(billID,partListID); Some of these columns should always have a unique value, like the description (descr) of a part or bill. You can also prevent us ers from creating what amounts to a duplicate part list entry by requiring the combination of a partID and the quantity of that part required: alter table dbo.part add constraint uqDescr unique(descr); alter table dbo.bill add constraint uqBill unique(descr); alter table dbo.partList add constraint uqPartList unique(partID,quantity); After you have successfully executed this statement, make sure to change your database scope to dbBOM, such as with a USE db BOM statement. Create the Tables Following is the executable TSQL code for creating the four required tables: create table dbo.part( partID smallint not null, descr varchar(50) not null, cost money not null); create table dbo.partList( partListID int not null, partID smallint not null, quantity smallint not null); create table dbo.billPartList( billID int not null, partListID int not null); create table dbo.bill( billID int not null, parentBillID int null, descr varchar(50) not null); Finally, add the foreign key constraints between the tables. The first constraint is between dbo.part and dbo.partList. This will re quire that the part being added to the part list first be defined in the dbo.part table. If a part is deleted from dbo.part, you want that change to be reflected in the part list. That is easily done by adding the “on delete cascade” clause to the constraint. Since the partID is marked as an identifier, it cannot be changed and thus no cascad ing action should be taken: alter table dbo.partList add constraint fkPartList_Part foreign key(partID) references dbo.part(partID) on delete cascade on update no action; Note that the only column in these tables allowed to have a null value is the parentBillID in dbo.bill. This null value allows us to dis tinguish a bill that represents a complete system from other bills. A key to making this implementation referentially valid is get ting the constraints on the tables correct. The tables have different functions in the solution, so they will have different constraints. However, every table in SQL Server should have a primary key as sociated with it, like so: The next constraint requires that if a bill is assigned a parentBill ID, the bill referenced by billID must already exist in the dbo.bill table. The cascading constraints are different. You are prohibited from having a cascading constraint here because SQL Server can not guarantee that an infinite recursion of actions might take place when you delete or update a parent bill. Since no defaults have been defined, “set default” is not an option either. alter table dbo.bill add constraint fkBill_Bill foreign key(parentBillID) references dbo.bill(BillID) on delete no action on update no action; PK,I1 I2 billID descr PK,FK2 PK, FK1 partListID billID PK,I1 FK1,I2 I2 partListID partID quantity The billPartList table requires a couple of foreign key constraints. First, you need to require that the part list being associated with a bill exists in the PartList table. Also, the bill being refer enced must be within the dbo.bill table. Since neither the billID from dbo.bill nor the partListID from dbo.partList PK,I1 partID can be updated, there is no cascade action possible for the constraints. However, if a part or bill is deleted, those descr I2 cost changes may affect this table: alter table dbo.billPartList add constraint fkBillPartList_PartList foreign key(partListID) references dbo.partList(partListID) Figure 3 Entity Relationship Diagram for a Simplified Bill of Materials System 60 msdn magazine SQL Server 2008

Table of Contents Feed for the Digital Edition of MSDN Magazine - September 2008

MSDN Magazine - September 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Cutting Edge Hierarchy ID New Features for Microsoft SQL Server 2008 Prism Data Services Advanced WPF Test Run Security Briefs Foundations { End Bracket }

MSDN Magazine - September 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.