Database design question

[zarkon4]

I have a question concerning the design of a database.
I am trying to figure out a design for a multiple level Bill of Material design in SQL Server.
Anyone ever set up a database for this, if so how was it designed?
I'm not wanting to copy anyone's design, just trying to get an idea on how to define the tables for it.

 

[MikeBronner]

Not sure exactly what you mean with 'multilevel'. However, this might work for what you need:

1) Create the following tables: bill, bill_items
2) Add all the normal fields on the Bill of Materials that aren't line item fields to the bill table.
3) Add all line item fields to the bill_items table.
4) Create a primary key on each table.
5) Add a foreign key to the bill_items table that 'links' back to the bill table. This will create a one-to-many relationship between the bill and bill_items table.
6) Add a parent_id field to the bill table. This will allow you to create a bill 'hierarchy', if you will, allowing you to create sub-bills.

Not sure if this is the exact answer you are looking for, hope it helps a bit, though.

Take Care,
Mike

 

[zarkon4]

I probably used the wrong term when I said "multilevel".
I meant to say Indented as follows:

A
--1
---2
--1
---2
----3
B
--1
---2
----3
---2
----3
--1
---2
----3
-----4
------5
----3
-----4
--1

 

[MikeBronner]

What is the significance of the indenting? I'm assuming in your above example that A and B are two different bills, while the numbers are line items on each respective bill. Is that correct?

If so, then all you need to do is add the parent_id field to the bill_items table instead of the bill_table. That way you establish a 'hierarchy' within the line items.

As you are looping through the line items and displaying them, keep track of how far you are indenting, and if one line item is the 'child' of another, then indent it more.

Take Care,
Mike