Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Table Design / Normalised Data Structure

    Hi All

    Working on a big Freight Rate program that has spiraled in scope since I started.

    Basically I have implemented a bandaid solution for the short term but now I need to start on a complete redesign to make it work better / performance over the web using Azure SQL.



    So starting from scratch would appreciate advise / input on table design and normalised data structure.

    Below is a simple excel file I did up to show how we recieve data and hopefully explain the connections between fields.
    Table Design.zip

    The main purpose of the database is to speed up the data entry so uploading formatted spreadsheets is a must.

    The data in the tables will then be combined together in a form for users to look up rates, the layout like this:

    Click image for larger version. 

Name:	Menu Layout.PNG 
Views:	71 
Size:	57.8 KB 
ID:	44867

    Hopefully this gives you guys an idea on what I'm trying to achieve, what would be the best table design / normalisation of the data in Access/Azure SQL to get the best performance / functionality?

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    you should have sent us the table design too.

  3. #3
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    I want to start from scratch so there are no tables as yet.

    I have the old one but I think its so bad it's probably better starting over.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would start with: Looking at the orange fill range, "Additional Surcharges + Validity Dates (to be added to Freight Base Costs on form)", it would be converted like this:


    FRT_Additionals_Table
    Click image for larger version. 

Name:	Excel2Access.png 
Views:	59 
Size:	225.5 KB 
ID:	44871
    Looking at the data/Validity Dates, the data seems to be duplicated??





    A Better view of field names
    Click image for larger version. 

Name:	FieldNames.png 
Views:	59 
Size:	17.7 KB 
ID:	44872



    Not sure what your abbreviations mean:
    POL => Port of Lading

    POD =>
    Port of Discharge
    Port of Debarkation
    Port Of Destination

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Are you dealing with only shipping containers or shipping vessels and shipping containers?

    Would you explain the process(s) you go through? Like "A day in the life of a shipping container"?

  6. #6
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by ssanfu View Post
    I would start with: Looking at the orange fill range, "Additional Surcharges + Validity Dates (to be added to Freight Base Costs on form)", it would be converted like this:


    FRT_Additionals_Table
    Click image for larger version. 

Name:	Excel2Access.png 
Views:	59 
Size:	225.5 KB 
ID:	44871
    Looking at the data/Validity Dates, the data seems to be duplicated??





    A Better view of field names
    Click image for larger version. 

Name:	FieldNames.png 
Views:	59 
Size:	17.7 KB 
ID:	44872



    Not sure what your abbreviations mean:
    POL => Port of Lading

    POD =>
    Port of Discharge
    Port of Debarkation
    Port Of Destination
    Quote Originally Posted by ssanfu View Post
    Are you dealing with only shipping containers or shipping vessels and shipping containers?

    Would you explain the process(s) you go through? Like "A day in the life of a shipping container"?
    Thanks,

    Starting with abbreviations:

    POL = Port of Loading (where the container is originally loaded on to a ship), could also be called Port of Origin
    POD = Port of Discharge (where the container comes off the ship in Australia)
    20GP / 40GP / 40HC = Are just types of containers (20 foot general, 40 foot general, 40 foot high cube), there are many more types but these are the ones we want to cover, but if there is an easy way to design it so it can be expanded that would be good.
    Carrier = Name of shipping line company, for example the one that got stuck in the Suez Canal recently was Ever Green, Maersk is another well known one
    BAF / GRI / PSS / MISC = Are just different surcharges that can be applied (BAF = Bunker Adjustment Factor (aka FAF or Fuel), GRI = General Rate Increase, PSS = Peak Season Surcharge, MISC is just a miscellaneous field for any other one not already accounted for)
    THC / BL / SEC = Are just different charges incurred at POD (THC = Terminal Handling, BL = Bill of Lading, SEC = Security)
    Transit = Is the time in days a vessel takes between the POL and POD, this is not always the same as different carriers offer different services that may be faster / slower.
    Direct = If the container changes to another vessel at a port somewhere within the POL and POD, just good info to know.



    The database is just dealing with shipping containers and their costs + transits, we dont need to include any vessel names or anything like that, its just the costs + transit times for each carrier and each pair of POL and POD.

    So for example, a user in accounting is checking if carrier ANL invoice to us is correct, so they open the database tool (the form I attached), and put in the POL and POD in the comboboxes + ANL in carrier + a shipment date in "Select Date" text box and click "Search Date". This will then show them the costs in the listbox that are relevant to what they searched for and they can use this to check in the ANL invoice is correct.

    The other main example is a sales rep is quoting a client, so they open the database tool, put in the POL and POD but leave the rest blank, this will then show all costs from all carriers in the listbox for that POL / POD, then they can pick the best one by price or transit etc to use for their quote.

    Thats the main two uses of the database.



    In regards to your FRT_Additionals_Table design, I think there is a miss-understanding, there would be data for each of the SurchargeCode's for every POL / POD pair for every Contract for every Carrier if that makes sense.

    So if I put it as a hierarchy/tree it would go something like this:

    Click image for larger version. 

Name:	Tree.png 
Views:	54 
Size:	9.4 KB 
ID:	44874

    Does that make sense? Like each Carrier can have multiple contracts (but often only 1 or 2 in reality), each contract has 50+ POL paired with 5+ POD, each POL / POD pair has FRT Base Costs + FRT Additionals + Local Charges + Transit data

    Hope that helps.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,668
    A possible structure.

    tblPorts: PortID, PortName, ...;
    tblContainers: ContainerID, ContainerDescription, ... (a list of different container types with parameters needed for any calculations);
    tblClients: ClientID, ClientName, ...;
    tblCarriers: CarrierID, CarrierName, ... (I assumed a carrier is a company?);
    [tblShips: ShipID, ShipName, CarrierID, ...] (an optional table for case you want to register ships too, not only carriers);
    tblTransports: TransportID, TransportName, CarrierID/ShipID, StartDate, ... (a table to register a specific delivery trip of deliveries between any ports in given transport route);
    tblTransportPorts: TransportPortID,TransportID, [StopNo], [ArrivalDatetime], [StopTime], [LeaveDatetime], [HoursToNextPort]... (all port stops of delivery trip - an entry for every separate port - with all info you need to get the timeline until arrival to next port - you have to decide, what kind if info you need to enter, and what is calculated);
    tblDeliveries: DeliveryID, ClientID, TransportID, ContainerID, Quantity, POL, POD, ... (POL and POD are TransportPortID from tblTransportPorts);
    ...

    I'm sure I have missed something, but this will give a direction.

  8. #8
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    There is a bunch in there that isnt required.

    I was thinking something like:

    tblCarriers: CarrierID, CarrierName

    tblContracts: ContractID, ContractType, Contract (linked to tblCarriers, so that each carrier has their own tblContracts)

    tblPorts: PortPairID, POLName, PODName (linked to tblContracts, so that each contract in each carrier has their own tblPorts)

    tblCosts: PortPairID, FRTCostCode, FRTCostValue, FRTNotes, FRTValidFrom, FRTValidTo, SurchargeCode, SurchargeValue, SurchargeNotes, SurchargeValidFrom, SurchargeValidTo, LocalCode, LocalValue, LocalCurrency, LocalNotes, LocalValidFrom, LocalValidTo (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)

    tblTransits: PortPairID, Transit, Direct (linked to tblPorts, so that each portpair under each contract under each carrier has their own tblTransits)


    And some reference tables as below:

    tblFRTCostCode: FRTCostCode (simply a list of all different FRT cost codes, these would be the 20GP, 40GP, 40HC for starters, but leaves it open to expand by adding more like 20HC, 20RE, 20OT for example)
    tblSurchargeCode: SurchcargeCode (simply a list of all different surcharge cost codes, these would be the 20GP BAF, 40GP BAF, 40HC BAF etc for starters, but leaves it open to expand by adding more)
    tblLocalCode: LocalCode (simply a list of all different local codes, these would be 20GP THC, 40GP THC, 40HC THC etc for starters but leaves it open to expand by adding more)

    Is the above possible? Making a tbl underneath values in a higher up table (like tblCarriers) auto creating there own tables as required?

  9. #9
    Join Date
    Apr 2017
    Posts
    1,668
    Quote Originally Posted by stildawn View Post
    tblContracts: ContractID, ContractType, Contract (linked to tblCarriers, so that each carrier has their own tblContracts)

    tblPorts: PortPairID, POLName, PODName (linked to tblContracts, so that each contract in each carrier has their own tblPorts)
    ...
    I.e. a lot of tables of ports, where port names are entered manually in every entry - what also means there may be a lot of same ports in different tables/entries spelled differently and handled as different ports as result! And having practically identical info in several tables is heavily not-normalized!

    Another thing to consider: Let's assume you have a new port in some route one day. You create a new table for this port, but none of your current reports or forms don't have any clue about this. You have to redesign your reports and forms every time you add a new port! The same applies whenever you have to drop a port from some route. And what about older data - how will your program differ between different route designs over time?

    And what when you need to create some report e.g. for specific port, or for specific container type over all routes. This task will be a real horror for you!

  10. #10
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    What would be the most normalised way of handling this hierarchy/tree then:

    Click image for larger version. 

Name:	Tree.png 
Views:	47 
Size:	9.4 KB 
ID:	44909


    In regards to the ports, its not a "route" there will only ever be a start point (POL) and end point (POD), yes there may be new ones added, but they don't affect anything else, they are standalone (well they get listed on the form, see below for current form):

    Click image for larger version. 

Name:	Menu Layout.PNG 
Views:	46 
Size:	57.8 KB 
ID:	44910

    Perhaps its just one master tblPorts table, and then one tblCosts as below:

    tblCosts: CarrierID, ContractID, PortPairID, FRTCostCode, FRTCostValue, FRTNotes, FRTValidFrom, FRTValidTo, SurchargeCode, SurchargeValue, SurchargeNotes, SurchargeValidFrom, SurchargeValidTo, LocalCode, LocalValue, LocalCurrency, LocalNotes, LocalValidFrom, LocalValidTo

    But then that is getting back to the few massive tables that I currently have which are not normalised??

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It seems you still have your Excel hat on.
    Click image for larger version. 

Name:	Design1.png 
Views:	42 
Size:	45.2 KB 
ID:	44918
    The above is Excel Design.
    In an Access table, you wouldn't have field names of "ANL", "PIL", Maersk" would you? You would have a field name "Carriers" and the DATA would be "ANL", "PIL", Maersk".
    Same with Contracts. Would you have field names like "12345" or "abcde"? The field name might be "ContractName", with DATA like "12345" or "abcde".
    tblPortsWould not have field names like "Hong Kong", "Sydney", "Singapoer", etc. The field name might be "PortName", with DATAlike "Hong Kong", "Sydney", "Singapoer".

    So now the "FRT_Additionals_Table" table. If the cost codes are "20GP BAF", "40GP BAF", "40HC BAF", "20GP GRI", etc, do you think they should be field names? Or shiykd the field name be something like "CostCode"?
    Arvil brought this up in his previous post as to why DATA should not be field names......

    Using your Post #8, these are some tentative table designs.
    Code:
    tblCarriers
    ------------
    CarrierID_PK  (Autonumber)
    CarrierName   (Text)
    
    tblContracts
    -------------
    ContractID_PK       (Autonumber)
    CarrierID_FK        (Number - Long Int)  (linked to tblCarriers, so that each carrier has their own tblContracts)
    ContractTypeID_FK   (Number - Long Int)
    ContractName        (Text)
    
    tblContractTypes
    ----------------
    ContractTypeID_PK   (Autonumber)
    ContractType        (Text)
    
    
    tblPorts
    ---------
    PortID_PK   (Autonumber)
    ContractID_FK   (Number - Long Int) (linked to tblContracts, so that each contract in each carrier has their own tblPorts)
    PortName        (Text)
    PortType        (Text)  (Values = POLName or PODName )
    
    tblCosts
    ----------
    CostsID_PK      (Autonumber)
    POD_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    POL_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    CostCodeID_FK   (Number - Long Int) (linked to tblCostCodes)
    CostType        (Text)  (Values => FRT, Surcharge or Local )
    CostValue       (Number - Double)
    ValidFrom       (Date/Time)
    ValidTo         (Date/Time)
    LocalCurrency   (Text)  (AUD, MYR, etc)
    Notes           (Text) 
    
    tblCostCodes
    ------------
    CostCodeID_PK   (Autonumber)
    CostCode        (Text)  (Values => 20GP BAF, 40P BAF, 40HC BAF, 20GP GRI)
    
    tblTransits
    ------------
    PortPairID_PK   (Autonumber)
    TransitDays    (Number - Integer)
    Direct         (????)
    
    (linked to tblPorts, so that each portpair under each contract under each carrier has their own tblTransits)   <<== I'mnot sure about this yet
    Still need another table to bring together (collect) all of the Look Up TABLE selections, maybe

    tblShipping
    ===========
    ShippingID_PK (Autonumber)
    CarrierID_FK (Number - Long)
    ContractID_FK (Number - Long)
    ContractTypeID_FK (Number - Long)
    POL_PortID_FK (Number - Long)
    POD_PortID_FK (Number - Long)
    CostsID_FK (Number - Long)
    TransitID_FK (Number - Long)





    OK, have to go put the thinking hat back on. I think it might needto go to the shop to be fixed.......

  12. #12
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by ssanfu View Post
    It seems you still have your Excel hat on.
    Click image for larger version. 

Name:	Design1.png 
Views:	42 
Size:	45.2 KB 
ID:	44918
    The above is Excel Design.
    In an Access table, you wouldn't have field names of "ANL", "PIL", Maersk" would you? You would have a field name "Carriers" and the DATA would be "ANL", "PIL", Maersk".
    Same with Contracts. Would you have field names like "12345" or "abcde"? The field name might be "ContractName", with DATA like "12345" or "abcde".
    tblPortsWould not have field names like "Hong Kong", "Sydney", "Singapoer", etc. The field name might be "PortName", with DATAlike "Hong Kong", "Sydney", "Singapoer".

    So now the "FRT_Additionals_Table" table. If the cost codes are "20GP BAF", "40GP BAF", "40HC BAF", "20GP GRI", etc, do you think they should be field names? Or shiykd the field name be something like "CostCode"?
    Arvil brought this up in his previous post as to why DATA should not be field names......

    Using your Post #8, these are some tentative table designs.
    Code:
    tblCarriers
    ------------
    CarrierID_PK  (Autonumber)
    CarrierName   (Text)
    
    tblContracts
    -------------
    ContractID_PK       (Autonumber)
    CarrierID_FK        (Number - Long Int)  (linked to tblCarriers, so that each carrier has their own tblContracts)
    ContractTypeID_FK   (Number - Long Int)
    ContractName        (Text)
    
    tblContractTypes
    ----------------
    ContractTypeID_PK   (Autonumber)
    ContractType        (Text)
    
    
    tblPorts
    ---------
    PortID_PK   (Autonumber)
    ContractID_FK   (Number - Long Int) (linked to tblContracts, so that each contract in each carrier has their own tblPorts)
    PortName        (Text)
    PortType        (Text)  (Values = POL or POD)
    
    tblCosts
    ----------
    CostsID_PK      (Autonumber)
    POL_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    POD_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    CostCodeID_FK   (Number - Long Int) (linked to tblCostCodes)
    CostType        (Text)  (Values => FRT, Surcharge or Local )
    CostValue       (Number - Double)
    ValidFrom       (Date/Time)
    ValidTo         (Date/Time)
    LocalCurrency   (Text)  (AUD, MYR, etc)
    Notes           (Text) 
    
    tblCostCodes
    ------------
    CostCodeID_PK   (Autonumber)
    CostCode        (Text)  (Values => 20GP BAF, 40P BAF, 40HC BAF, 20GP GRI)
    
    tblTransits
    ------------
    PortPairID_PK   (Autonumber)
    TransitDays    (Number - Integer)
    Direct         (????)
    
    (linked to tblPorts, so that each portpair under each contract under each carrier has their own tblTransits)   <<== I'mnot sure about this yet
    Still need another table to bring together (collect) all of the Look Up TABLE selections, maybe

    tblShipping
    ===========
    ShippingID_PK (Autonumber)
    CarrierID_FK (Number - Long)
    ContractID_FK (Number - Long)
    ContractTypeID_FK (Number - Long)
    POL_PortID_FK (Number - Long)
    POD_PortID_FK (Number - Long)
    CostsID_FK (Number - Long)
    TransitID_FK (Number - Long)





    OK, have to go put the thinking hat back on. I think it might needto go to the shop to be fixed.......

    Thanks ssanfu looks mostly good to me, few thoughts below:

    Code:
    tblCosts
    ----------
    CostsID_PK      (Autonumber)
    POD_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    POL_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    CostCodeID_FK   (Number - Long Int) (linked to tblCostCodes)
    CostType       (Text)  (Values => FRT, Surcharge or Local )
    CostValue       (Number - Double)
    ValidFrom       (Date/Time)
    ValidTo         (Date/Time)
    LocalCurrency   (Text)  (AUD, MYR, etc)
    Notes           (Text)
    
    tblCostCodes
    ------------
    CostCodeID_PK   (Autonumber)
    CostCode       (Text)  (Values => 20GP BAF, 40P BAF, 40HC BAF, 20GP GRI)
    I think CostType should be moved into tblCostCodes as that makes more sense to me as a CostCode will only ever be 1 of the 3 CostType options

    Code:
    tblCostCodes
    ------------
    CostCodeID_PK   (Autonumber)
    CostCode       (Text)  (Values => 20GP BAF, 40P BAF, 40HC BAF, 20GP GRI)
    CostType       (Text)  (Values => FRT, Surcharge or Local )



    Transits could be:

    Code:
    tblTransits
    ------------
    PortPairID_PK   (Autonumber)
    POL_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    POD_PortID_FK   (Number - Long Int)  (linked to tblPorts so that each portpair under each contract under each carrier has their own tblCosts)
    TransitDays   (Number - Integer)
    Direct         (Text) (Values = Yes or No)
    Question, how is POL_PortID_FK / POD_PortID_FK generated, as the tblPorts doesnt have a field for POL / POD only for Ports and PortType



    Not overly sure what this part is for:

    tblShipping
    ===========
    ShippingID_PK (Autonumber)
    CarrierID_FK (Number - Long)
    ContractID_FK (Number - Long)
    ContractTypeID_FK (Number - Long)
    POL_PortID_FK (Number - Long)
    POD_PortID_FK (Number - Long)
    CostsID_FK (Number - Long)
    TransitID_FK (Number - Long)
    Cheers

  13. #13
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    So is the above good? Should I set it up?

  14. #14
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Just realised that the above table designs doesnt look to preserve historic data.

    With the end product, a user needs to be able to enter a date and find the valid costs based on that date, the above looks to me to not preserve that as I assume new data would overwrite the old CostsID?

    Note sure but would this work:

    Code:
    tblCarriers
    ------------
    CarrierID_PK  (Autonumber)
    CarrierName   (Text)
    
    
    tblContracts
    -------------
    ContractID_PK       (Autonumber)
    CarrierID_FK        (Number - Long Int)  (linked to tblCarriers, so that each carrier has their own tblContracts)
    ContractTypeID_FK   (Number - Long Int)
    ContractName        (Text)
    
    
    tblContractTypes
    ----------------
    ContractTypeID_PK   (Autonumber)
    ContractType        (Text)
    
    tblValidDates
    --------------
    ValidDatesID (autonumber)
    ContractID_FK  (Number - Long Int) (linked to tblContracts, so that each contract in each carrier has their own tblValidDates)
    ValidFrom (date/time)
    ValidTo (date/time)
    
    
    tblPortPairs
    ---------
    PortPairID_PK   (Autonumber)
    ContractID_FK   (Number - Long Int) (linked to tblContracts, so that each contract in each carrier has their own tblPorts)
    ValidDatesID_FK (Number - Long Int) (linked to tblValidDates, so that each carriers contract validdates range has their own tblPorts)
    POLName (text)
    PODName (text)
    TransitDays   (Number - Integer)
    Direct         (Text) (Values = Yes or No)
    
    
    
    tblCosts
    ----------
    CostsID_PK      (Autonumber)
    ValidDatesID_FK (Number - Long Int) (linked to tblValidDates)
    PortPairID_FK (Number - Long Int) (linked to tblPortPairs)
    CostCodeID_FK   (Number - Long Int) (linked to tblCostCodes)
    CostValue       (Number - Double)
    LocalCurrency   (Text)  (AUD, MYR, etc)
    Notes           (Text)
    
    tblCostCodes
    ------------
    CostCodeID_PK   (Autonumber)
    CostCode       (Text)  (Values => 20GP BAF, 40P BAF, 40HC BAF, 20GP GRI)
    CostType       (Text)  (Values => FRT, Surcharge or Local )
    I have bolded and changed to red my amendments, does this make sense would it work?

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    the above looks to me to not preserve that as I assume new data would overwrite the old CostsID?
    You would not edit records, you'd append them. If you want to segregate current and valid costs from expired costs I imagine a ValidTo date field would suffice.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 232
    Last Post: 04-17-2017, 01:08 PM
  2. Replies: 12
    Last Post: 03-14-2017, 04:43 PM
  3. Table Design - Relationship Structure
    By djspod in forum Database Design
    Replies: 2
    Last Post: 03-09-2017, 10:43 AM
  4. Replies: 3
    Last Post: 07-02-2015, 09:15 AM
  5. Complex Survey: Table Structure Design and Normalization
    By kevin007 in forum Database Design
    Replies: 2
    Last Post: 07-06-2010, 09:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums