Relational table structure

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tetelestai
    New Member
    • Nov 2006
    • 34
    #1

    Relational table structure

    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
    Tags: None
    • MMcCarthy
      Recognized Expert MVP
      • Aug 2006
      • 14387
      #2
      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[/]
      Code:
      Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

        Comment

        • Tetelestai
          New Member
          • Nov 2006
          • 34
          #3
          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
          Code:
          JobOrderID, Auto#, PK
Model, Number, FK
Lot, Text,
Subdivision, Number, FK
RecievedDate, Date
CustomerID,Number, FK
          Table: SubOrders
          Code:
          SubOrderID, Auto#, PK
JobOrderID, Number, FK
SubOrderType, Number, FK
          Table: Floors (Wall Panel)
          Code:
          FloorID, Auto#, PK
SubOrderID, Number, FK
Floor, Text,
          Table: Panels
          Code:
          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
          Code:
          RoofID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text
          Table: RoofTrusses
          Code:
          RoofTrussID, Auto#, PK
RoofID, Number, FK
RTrussName, Text
RTrussType, Text
RTrussQty, Number
RTrussSpan, Number
RTrussPrice, Currency
          Table: TrussFloor
          Code:
          TrussFloorID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text
          Table: FloorTrusses
          Code:
          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
          Code:
          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
          Code:
          RunID, Auto#, PK
RunDate, DateTime
RunDriverID, Number, FK
          Table: RunDetail
          Code:
          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

            Comment

            • MMcCarthy
              Recognized Expert MVP
              • Aug 2006
              • 14387
              #4
              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.

                Comment

                • Tetelestai
                  New Member
                  • Nov 2006
                  • 34
                  #5
                  Originally posted by msquared
                  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

                    Comment

                    • Tetelestai
                      New Member
                      • Nov 2006
                      • 34
                      #6
                      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.



                      Originally posted by Tetelestai

                      Here is my table structure:

                      Table: Orders
                      Code:
                      JobOrderID, Auto#, PK
Model, Number, FK
Lot, Text,
Subdivision, Number, FK
RecievedDate, Date
CustomerID,Number, FK
                      Table: SubOrders
                      Code:
                      SubOrderID, Auto#, PK
JobOrderID, Number, FK
SubOrderType, Number, FK
                      Table: Floors (Wall Panel)
                      Code:
                      FloorID, Auto#, PK
SubOrderID, Number, FK
Floor, Text,
                      Table: Panels
                      Code:
                      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
                      Code:
                      RoofID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text
                      Table: RoofTrusses
                      Code:
                      RoofTrussID, Auto#, PK
RoofID, Number, FK
RTrussName, Text
RTrussType, Text
RTrussQty, Number
RTrussSpan, Number
RTrussPrice, Currency
                      Table: TrussFloor
                      Code:
                      TrussFloorID, Auto#, PK
SubOrderID, Number, FK
FloorType, Number, FK
MBARefNum, Number, FK to External Database
MBAJobName, Text
                      Table: FloorTrusses
                      Code:
                      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
                      Code:
                      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
                      Code:
                      RunID, Auto#, PK
RunDate, DateTime
RunDriverID, Number, FK
                      Table: RunDetail
                      Code:
                      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

                        Comment

                        Previous template Next
                        Working...