Basic Bill of Materials schema

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.

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

• Don't use BOM. Use BillOfMaterials.

It won't matter to you, but the next guy will thank me.

• You're missing indexes on the items you defined foreign keys for. They're not free, you have to create them yourself.

• 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.

StackExchange Code Review Q#107422, answer score: 2