Relational table structure

[Tetelestai]

I'm creating a database for housing component manufacturing plant. I have relations of Orders can have one to three Sub-Orders which can have one or many Levels(floors) which can have one or many Parts(trusses or panels) each Part belongs to one Shipment(has one or many Parts) many shipments make up a delivery Run.

I have read http://www.thescripts.com/forum/thre...elational.html and do have a similar situation but was looking for advice for this situation. Also just voicing my problem may help myself.

What I have done is after the Sub-Order table and depending on the Sub Order (Roof Truss or Wall Panel, Floor Truss) I have a seperate table for Roof Levels, Wall Panel Levels and Floor Truss Levels with each having seperate Parts table. The reason for this is the information that they contain is different between the parts. Also, I have to track more production information with panels then I have to with trusses as we have a seperate program for trusses. I have not created the shipment tables as this is where the difference between the sub orders end. A shipment may contain panels or trusses but not both. A Run may contain a shipment of panels and a shipment of trusses. Also another consideration is that a shipment may contain loose lumber which is not related to any panel or truss but related to the level they belong too.

Have I gone too deep with my model? Should I combine the Levels tables and Parts Tables? If I keep this structure how do I create the shipment table to reflect what they contain from different tables?

Thanks for the help

[MMcCarthy]

Firstly, its not clear whether you read the article referred to in the other thread Normalization and Table Structures. I would recommend you do that first.

Secondly if you need us to look at the structure of your database you have to provide that structure in the form of metadata.

Here is an example of how to post table MetaData :
[b]Table Name=tblStudent[/]

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[Tetelestai]

Thanks for the reply Mary,

I have read the normalization article. (read it again too). Can you take this statement too far?

Create a separate table for each set of related data and Identify each set of related data with a primary key

I've created a table for each SubOrder type and each of those tables have a suborder parts table (Panels,Trusses). See Below

Here is my table structure:

Table: Orders

JobOrderID, Auto#, PK
Model, Number, FK
Lot, Text,
Subdivision, Number, FK
RecievedDate, Date
CustomerID,Number, FK

Table: SubOrders

SubOrderID, Auto#, PK
JobOrderID, Number, FK
SubOrderType, Number, FK

Table: Floors (Wall Panel)

FloorID, Auto#, PK
SubOrderID, Number, FK
Floor, Text,

Table: Panels

PanelID, Auto#, PK
FloorID, Number, FK
PNLLabel, Text,
PNLNum, Number
Type, Text
Class, Text
Size, Number
Length, Number
Height, Number
Complete, Yes/No
CompleteDate, Date/Time
Bunk, Text

Table: Roofs

RoofID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text

Table: RoofTrusses

RoofTrussID, Auto#, PK
RoofID, Number, FK
RTrussName, Text
RTrussType, Text
RTrussQty, Number
RTrussSpan, Number
RTrussPrice, Currency

Table: TrussFloor

TrussFloorID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text

Table: FloorTrusses

FloorTrussID, Auto#, PK
FloorTrussID, Number, FK
FTrussName, Text
FTrussType, Text
FTrussQty, Number
FTrussSpan, Number
FTrussPrice, Currency

There are many similarites between Roof & Floor Trusses should these tables be combined? Is spliting them up making the tables too specific?

Handling Shipments: A shipment has one or many Panels/RTrusses/FTrusses. Should I have a table for each shipment type:

(ie Table: WPanelShipments

WPanelShipID, Auto#, PK
WPShipDueDate, Date/Time

) Panels table would have a WPanelShipID FK

This would be also true for the rooftrusses and floortrusses tables

Runs: A Run has one or many Shipments of any of the types. My inital thought here is to have a Run table and a RunDetails table with this stucture...

Table: Run

RunID, Auto#, PK
RunDate, DateTime
RunDriverID, Number, FK

Table: RunDetail

RunID, Number, PK
WPanelShipID, Number, PK
RTrussShipID, Number, PK
FTrussShipID, Number, PK

The RunDetail table correct? This would be my biggest mental hangup.


Thanks for your time

[MMcCarthy]

I understand why you have broken them out but I'll need some time to look at the structure of this. Leave it with me and I'll get back to you as soon as I can.

[Tetelestai]

I understand why you have broken them out but I'll need some time to look at the structure of this. Leave it with me and I'll get back to you as soon as I can.

Any Luck or Applied Skill?


Thanks

[Tetelestai]

For posterity

I am still unable to solve the relations above, so I decided to change the structure as follows:

Orders - remains the same
SubOrders - remains the same
Floors, Roofs, TrussFloors combined into 'Assemblies'
Panels, RoofTrusses, FloorTrusses combined into 'Items'

Relations:

Orders have 1:M SubOrders which have 1:M Assemblies which have 1:M Items

Runs have 1:M Shipments which have 1:M Items

I am planing to break out the item descriptions into seperate tables (Panels, Trusses etc), have not gotten that far yet.

Here is my table structure:

Table: Orders

JobOrderID, Auto#, PK
Model, Number, FK
Lot, Text,
Subdivision, Number, FK
RecievedDate, Date
CustomerID,Number, FK

Table: SubOrders

SubOrderID, Auto#, PK
JobOrderID, Number, FK
SubOrderType, Number, FK

Table: Floors (Wall Panel)

FloorID, Auto#, PK
SubOrderID, Number, FK
Floor, Text,

Table: Panels

PanelID, Auto#, PK
FloorID, Number, FK
PNLLabel, Text,
PNLNum, Number
Type, Text
Class, Text
Size, Number
Length, Number
Height, Number
Complete, Yes/No
CompleteDate, Date/Time
Bunk, Text

Table: Roofs

RoofID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text

Table: RoofTrusses

RoofTrussID, Auto#, PK
RoofID, Number, FK
RTrussName, Text
RTrussType, Text
RTrussQty, Number
RTrussSpan, Number
RTrussPrice, Currency

Table: TrussFloor

TrussFloorID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text

Table: FloorTrusses

FloorTrussID, Auto#, PK
FloorTrussID, Number, FK
FTrussName, Text
FTrussType, Text
FTrussQty, Number
FTrussSpan, Number
FTrussPrice, Currency

There are many similarites between Roof & Floor Trusses should these tables be combined? Is spliting them up making the tables too specific?

Handling Shipments: A shipment has one or many Panels/RTrusses/FTrusses. Should I have a table for each shipment type:

(ie Table: WPanelShipments

WPanelShipID, Auto#, PK
WPShipDueDate, Date/Time

) Panels table would have a WPanelShipID FK

This would be also true for the rooftrusses and floortrusses tables

Runs: A Run has one or many Shipments of any of the types. My inital thought here is to have a Run table and a RunDetails table with this stucture...

Table: Run

RunID, Auto#, PK
RunDate, DateTime
RunDriverID, Number, FK

Table: RunDetail

RunID, Number, PK
WPanelShipID, Number, PK
RTrussShipID, Number, PK
FTrussShipID, Number, PK

The RunDetail table correct? This would be my biggest mental hangup.


Thanks for your time