By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Relational table structure

P: 34
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
Feb 4 '08 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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[/]
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Feb 10 '08 #2

P: 34
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
Expand|Select|Wrap|Line Numbers
  1. JobOrderID, Auto#, PK
  2. Model, Number, FK
  3. Lot, Text,
  4. Subdivision, Number, FK
  5. RecievedDate, Date
  6. CustomerID,Number, FK
Table: SubOrders
Expand|Select|Wrap|Line Numbers
  1. SubOrderID, Auto#, PK
  2. JobOrderID, Number, FK
  3. SubOrderType, Number, FK
Table: Floors (Wall Panel)
Expand|Select|Wrap|Line Numbers
  1. FloorID, Auto#, PK
  2. SubOrderID, Number, FK
  3. Floor, Text,
Table: Panels
Expand|Select|Wrap|Line Numbers
  1. PanelID, Auto#, PK
  2. FloorID, Number, FK
  3. PNLLabel, Text,
  4. PNLNum, Number
  5. Type, Text
  6. Class, Text
  7. Size, Number
  8. Length, Number
  9. Height, Number
  10. Complete, Yes/No
  11. CompleteDate, Date/Time
  12. Bunk, Text
Table: Roofs
Expand|Select|Wrap|Line Numbers
  1. RoofID, Auto#, PK
  2. SubOrderID, Number, FK
  3. FloorType, Number, FK
  4. MBARefNum, Number, FK to External Database
  5. MBAJobName, Text
Table: RoofTrusses
Expand|Select|Wrap|Line Numbers
  1. RoofTrussID, Auto#, PK
  2. RoofID, Number, FK
  3. RTrussName, Text
  4. RTrussType, Text
  5. RTrussQty, Number
  6. RTrussSpan, Number
  7. RTrussPrice, Currency
Table: TrussFloor
Expand|Select|Wrap|Line Numbers
  1. TrussFloorID, Auto#, PK
  2. SubOrderID, Number, FK
  3. FloorType, Number, FK
  4. MBARefNum, Number, FK to External Database
  5. MBAJobName, Text
Table: FloorTrusses
Expand|Select|Wrap|Line Numbers
  1. FloorTrussID, Auto#, PK
  2. FloorTrussID, Number, FK
  3. FTrussName, Text
  4. FTrussType, Text
  5. FTrussQty, Number
  6. FTrussSpan, Number
  7. 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
Expand|Select|Wrap|Line Numbers
  1. WPanelShipID, Auto#, PK
  2. 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
Expand|Select|Wrap|Line Numbers
  1. RunID, Auto#, PK
  2. RunDate, DateTime
  3. RunDriverID, Number, FK
Table: RunDetail
Expand|Select|Wrap|Line Numbers
  1. RunID, Number, PK
  2. WPanelShipID, Number, PK
  3. RTrussShipID, Number, PK
  4. FTrussShipID, Number, PK
The RunDetail table correct? This would be my biggest mental hangup.


Thanks for your time
Feb 12 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Feb 12 '08 #4

P: 34
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
Feb 19 '08 #5

P: 34
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
Expand|Select|Wrap|Line Numbers
  1. JobOrderID, Auto#, PK
  2. Model, Number, FK
  3. Lot, Text,
  4. Subdivision, Number, FK
  5. RecievedDate, Date
  6. CustomerID,Number, FK
Table: SubOrders
Expand|Select|Wrap|Line Numbers
  1. SubOrderID, Auto#, PK
  2. JobOrderID, Number, FK
  3. SubOrderType, Number, FK
Table: Floors (Wall Panel)
Expand|Select|Wrap|Line Numbers
  1. FloorID, Auto#, PK
  2. SubOrderID, Number, FK
  3. Floor, Text,
Table: Panels
Expand|Select|Wrap|Line Numbers
  1. PanelID, Auto#, PK
  2. FloorID, Number, FK
  3. PNLLabel, Text,
  4. PNLNum, Number
  5. Type, Text
  6. Class, Text
  7. Size, Number
  8. Length, Number
  9. Height, Number
  10. Complete, Yes/No
  11. CompleteDate, Date/Time
  12. Bunk, Text
Table: Roofs
Expand|Select|Wrap|Line Numbers
  1. RoofID, Auto#, PK
  2. SubOrderID, Number, FK
  3. FloorType, Number, FK
  4. MBARefNum, Number, FK to External Database
  5. MBAJobName, Text
Table: RoofTrusses
Expand|Select|Wrap|Line Numbers
  1. RoofTrussID, Auto#, PK
  2. RoofID, Number, FK
  3. RTrussName, Text
  4. RTrussType, Text
  5. RTrussQty, Number
  6. RTrussSpan, Number
  7. RTrussPrice, Currency
Table: TrussFloor
Expand|Select|Wrap|Line Numbers
  1. TrussFloorID, Auto#, PK
  2. SubOrderID, Number, FK
  3. FloorType, Number, FK
  4. MBARefNum, Number, FK to External Database
  5. MBAJobName, Text
Table: FloorTrusses
Expand|Select|Wrap|Line Numbers
  1. FloorTrussID, Auto#, PK
  2. FloorTrussID, Number, FK
  3. FTrussName, Text
  4. FTrussType, Text
  5. FTrussQty, Number
  6. FTrussSpan, Number
  7. 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
Expand|Select|Wrap|Line Numbers
  1. WPanelShipID, Auto#, PK
  2. 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
Expand|Select|Wrap|Line Numbers
  1. RunID, Auto#, PK
  2. RunDate, DateTime
  3. RunDriverID, Number, FK
Table: RunDetail
Expand|Select|Wrap|Line Numbers
  1. RunID, Number, PK
  2. WPanelShipID, Number, PK
  3. RTrussShipID, Number, PK
  4. FTrussShipID, Number, PK
The RunDetail table correct? This would be my biggest mental hangup.


Thanks for your time
Apr 9 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.