Basic Bill of Materials schema

Question

I've been working on Bill of Materials mini schema for a while. At first I had single Part table where I've referenced itself. I was told it would be better to have separate table because we'd need to have info like quantity and that quantity is not part of Part itself. Here's the script...

CREATE TABLE Part
(
    ID INT NOT NULL PRIMARY KEY IDENTITY,
    PartNumber NVARCHAR(50) NULL,
    [Description] NVARCHAR(MAX) NULL,
    ListPrice DECIMAL(12,2) NULL
)

CREATE TABLE BOM
(
    ID INT NOT NULL PRIMARY KEY IDENTITY,
    PartId INT NOT NULL,
    ParentId INT NULL,
    Quantity INT NULL
)

ALTER TABLE BOM ADD CONSTRAINT BOM_PartId_FK
FOREIGN KEY (PartId) REFERENCES Part(ID)

ALTER TABLE BOM ADD CONSTRAINT BOM_ParentId_FK
FOREIGN KEY (ParentId) REFERENCES Part(ID)

insert into Part (PartNumber, Description, ListPrice) values ('AAA', 'A', 250.00)
insert into Part (PartNumber, Description, ListPrice) values ('AA', 'A', 100.00)
insert into Part (PartNumber, Description, ListPrice) values ('BBB', 'B', 250.00)
insert into Part (PartNumber, Description, ListPrice) values ('BB', 'B', 90.00)
insert into Part (PartNumber, Description, ListPrice) values ('B', 'B', 40.00)

insert into BOM (PartId) values (1)
insert into BOM (PartId, ParentId, Quantity) values (2, 1, 5)
insert into BOM (PartId, ParentId, Quantity) values (4, 3, 10)
insert into BOM (PartId, ParentId, Quantity) values (5, 4, 50)
insert into BOM (PartId, ParentId, Quantity) values (4, 1, 50)

Would this be ok as beginner BOM schema?

I've tested with the below query, this gets immediate children of BOM with ID of 1.

select e.*
from BOM b
join BOM e on b.PartId = e.ParentId
where b.ID = 1

I will recursively call this from c# to populate children from any level in the BOM, but usually top most level.

Answer 1

You're mixing up casing styles for T-SQL keywords - pick one: ANNOYINGCASE or readablecase, but don't use both in the same script. I have no bias or preference whatsoever for either*, all that matters is consistency.

Semicolons are not required to indicate the end of a statement, but they are nonetheless a good habit to have.

You inline primary keys and let the server name them. I might have a fetish for naming things, but I like my PK's named PK_TableName.

Speaking of naming, the Part table should be named Parts, and should probably have a natural key in the form of a unique constraint on the PartNumber column... which I find is a redundant name - Number would be better.

Foreign keys have potentially ambiguous names, or will be when your schema grows. I like naming my FK's FK_ReferencedTable_AlteredTable[_ColumnName], where the [_ColumnName] part is only needed for when there are multiple FK's on the same table referencing the same table, like when an OrderHeaders table references a FiscalCalendars table with its OrderDateCalendarId, ShipDateCalendarId and CancelDateCalendarId, which would respectively be FK_FiscalCalendars_OrderHeaders_OrderDate, FK_FiscalCalendars_OrderHeaders_ShipDate and FK_FiscalCalendars_OrderHeaders_CancelDate. So in your case that would be FK_Parts_BillOfMaterials and FK_BillOfMaterials_BillOfMaterials. Being rigorously consistent about naming foreign keys makes it easy to instantly know what tables are involved and in which direction, just by looking at the FK's name. Oh, and I agree with @RubberDuck - use BillOfMaterials, and keep bom for table aliases when querying the schema.

You're not specifying the schema you're creating the tables in. Better be explicit; you're not specifying the seed for your IDENTITY columns - again, better be explicit.

I hate to see Description be syntax-highlighted in SSMS - I like that you're avoiding that by enclosing the name in square brackets. But then, the issue could be avoided altogether by calling the column Name instead - that way you don't have to use square brackets everywhere, and Description doesn't stick out like a sore thumb by being the only name using square brackets.

That said, NVARCHAR(MAX) has implications that make it an annoying field, especially if the longest description/name is going to be 200, or even 1024 characters. Don't use MAX for anything below 4000 characters.

Also, I think you're abusing NULL columns. Does a part that has no number, no description and no price really make sense? Wouldn't that rather be an empty number, an empty description and a 0 price?

Comma-first style makes it easier to change the order of the columns if you ever want to do that.

Lastly, and again this might be just me, but I like when I can run a T-SQL script twice in a row without it blowing up or doing weird things.

Putting it all together (making a few assumptions):

use [database_name]; -- trust me, this one is a life saver
go;

/* drop FK constraints */

if exists (select * from sys.foreign_keys where name = 'FK_BillOfMaterials_BillOfMaterials')
    alter table BillOfMaterials drop constraint FK_BillOfMaterials_BillOfMaterials;

if exists (select * from sys.foreign_keys where name = 'FK_Parts_BillOfMaterials')
    alter table BillOfMaterials drop constraint FK_Parts_BillOfMaterials;


/* drop tables */

if exists (select * from sys.tables where name = 'Parts')
    drop table Parts;

if exists (select * from sys.tables where name = 'BillOfMaterials')
    drop table BillOfMaterials;


/* create tables */

create table dbo.Parts (
     Id int identity(1,1) not null
    ,Number nvarchar(50) not null
    ,Name nvarchar(250) not null
    ,ListPrice decimal(12,2) not null -- 2 decimals might be a little short in some cases
    ,constraint PK_Parts primary key clustered (Id)
    ,constraint NK_Parts unique (Number) -- "NK" == "Natural Key" (assumption here)
);

create table dbo.BillOfMaterials (
     Id int identity(1,1) not null
    ,PartId int not null
    ,ParentId int null
    ,Quantity int not null
    ,constraint PK_BillOfMaterials primary key clustered (Id)
);


/* add FK constraints */

alter table dbo.BillOfMaterials add constraint FK_BillOfMaterials_BillOfMaterials 
    foreign key (ParentId) references dbo.BillOfMaterials (Id);

alter table dbo.BillOfMaterials add constraint FK_Parts_BillOfMaterials 
    foreign key (PartId) references dbo.Parts (Id);

---

_*cough

Answer 2

It's ok, but there's room for improvement.

1. Don't use BOM. Use BillOfMaterials. It won't matter to you, but the next guy will thank me.
2. You're missing indexes on the items you defined foreign keys for. They're not free, you have to create them yourself.
3. I find it really odd that the BOM.Quantity can be null. If a part is on the BoM, shouldn't there be at least one of them required as part of the "kit"? I may even consider using a default value of one.