Access database storage tables design logic

Hi all,

I'm currently working on developing a forms based Access 2013 database that collects and calculates costs for transport routes from one or more origins to multiple destinations in multiple countries. The objective result of this exercise is to determine the total cost for each and every end-to-end route combination at the lowest level of granularity.

In doing so, I have started to create a form that will allow me to add the origin and destination locations one after the other but I have come to a point where I am really not confident enough to approach the storage side of my selections in the most efficient manner keeping in mind that I do need to store all the data on a permanent basis for presentation and future comparative costing exercises.

I think its also worth noting at this point that my application has not been through any formal specification process. Rather, it is very much an open book project of my own that will help me in my own work without having to resort to using, saving, and maintaining hundreds of different spreadsheets.

That said, I have spent a great deal of time this week trying to determine a viable option of approach and if it were not for the dynamic nature of what I am doing, I would have been fine creating static tables to allow for the same expected data each and every time. Unfortunately, this is not the case and by dynamic, I mean that I could have a scenario that involves the following combination of locations:

At a Country level:

Select Thailand as the initial origin country ->
Select Malaysia as the first destination country ->
Select the US as the second and final destination country ->

Then at Domestic level, I could do the following:

Select Oregon as the destination (after arriving from Malaysia) ->
Select a warehouse for the product to be unloaded at in Oregon ->
Select multiple warehouses across the country for the product to be sent to from Oregon ->
Select multiple retail stores across the country for the product to be sold from.

Or, I could have something as simple as:

At a Country level:

Select Thailand as the origin country ->
Select the US as the destination country ->

Then at Domestic level, I could create the following:

Select one warehouse in Oregon to receive the product ->
Select multiple retail stores across the country for the product to be sold from.

Given the above two examples, how does one go about catering for this type of environment? Does everything get stored in temp variables, a class collection, or in temporary tables built on the fly? I really dont know what to do here and sure hope to get some better direction with this so that I can achieve what I have set out to do.

To further assist with answering this question, I have attached some samples that will hopefully better illustrate my problem. Please excuse me I am not the best at drawing!

File 1 - Flow diagram showing a potential scenario that involves multiple countries and end destinations.
File 2 - Case study file containing the highlighted data captured in the above example.
File 3 - Screen-shot of a dummy table that I created to highlight what the data would look like after adding various routing options.

If anything I have done is unclear, please let me know.

Many thanks,
Laila JacksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Laila JacksonAuthor Commented:
I have uploaded another data sample which is a simplified scenario.
I think you would be better served by a graph database, such as Neo4j, than a relational database like Access.
Dale FyeOwner, Developing Solutions LLCCommented:
I'm not familiar with "graph databases" as described by aikimark, that is the first place I would start, as it sounds like they are probably more suited to the type of analysis you are looking to do.

But you could put this into a relational database.  The first step would be to stop referring to origins and destinations by country and state;  you need a specific locations, so I would start with a locations table, with Location_ID, Country, City, Location_Name, Address1, Address2, ...  The location might be an Airport, SeaPort, or the specific address of a warehouse or store.

Then, to determine your costs, you would need a rates table(tbl_Segment_Rates), which would describe each segment of the route (StartPoint, EndPoint), the method of transport (air, sea, rail, truck, ...), Container Type (refrigerated, ...),  the transporter, the quoted price, and rate effective dates (Eff_Date, Thru_Date) for those prices. This table would contain fields SegmentID, StartPoint_ID, EndPoint_ID, Method_ID, Transporter_ID, Rate, Rate_Eff_Date, Rate_Thru_Date, Est_Shipping_Time.  Depending on the type of transport and the cargo, there are other considerations as well (spoilage being the primary one that I can think of).  You would need tbl_Transporters and  tbl_ShippingMethods as lookup tables for the Transporter_ID and Method_ID tables).

You might need a routes table where you would store the preferred routing from a point of origin to a destination.

Then, you would need a Shipments table (tbl_Shipments), which would describe the individual shipment; Shipper_ID, start point, Start Date, End Point (destination of the entire shipment, before sub-dividing), Recipient_ID, Delivery Date, Container_Type.  Personally, I would describe individual shipments as routes where an entire "package" goes from point A to Point Z.  If a shipment is broken up enroute.  That would essentially begin multiple new shipments, and those would all be described separately in this table.

And a Shipment details table which would contain the Shipment_ID, and the individual route segments.  This table would also have to account for delays between arrival at one intermediate point and departure from that point, which might include additional storage or trans-shipment fees.

Defining all of these will be a nightmare, but the real headache will be computing the "best route".  There are transportation algorithms for this type of Operations Research problem, but they can be complicated.

I'm sure there are other experts here who have worked on transportation problems like this, so lets see what some of them have to say.  Keep in mind, it is the weekend, so you might not get many additional responses until early next week.

Good luck with your project.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Laila JacksonAuthor Commented:
Thanks guys for the responses. I appreciate that not all of us are glued to their keyboards on a weekend so thanks for taking the time to respond.

@ aikimark - I took a really good look at neoj4 last before retiring last evening and database wise it would certainly seem more suited to my project. However, I am very nervous about the development in that after a few years of working with VBA and SQL in Access and Excel, I have just spent the last four months progressing my skills into learning vb.NET and T-SQL. It would seem delving into neoj4 and it's cipher would be like learning a whole new language and I am not sure if this is something I want to pursue personally. In saying that, I did notice that there is an extension that allows .NET development using neoj4 as a back-end.

 @ Dale - I confirm that I already have an extensive set of data tables that cover all the areas you discussed in your response. I should have been more thorough in describing my current resources so please accept my humble apologies for this oversight.

Which leads to your point about doing away with the region/state code in my database. Unfortunately, I want to avoid this at all costs as I have planned to further develop my project into demand and inventory planning as well which requires these relationships.

I really want to make this work and hope to get some more fresh ideas from the community.

Thanks again,
You are thinking about the routing process as being contiguous and having to be described in a single record.  But, in fact, you should be thinking about it as segments.  segment 1 goes from A to B.  segment2 goes from B to C, etc.  This will allow you to simplify the data entry.  I can't tell if you are trying to calculate the best route from A to C or if someone/somesoftware is calculating it and you are then costing it.  I couldn't even begin to come up with a solution for calculating the best route but as Dale said, there is existing software and this is a case where I would strongly suggest buy rather than build.  This assumes that these routing modules can be manipulated programmatically so that you give it a starting and endng point and it returns the route in segments.
Laila JacksonAuthor Commented:
@ Pat - Thanks for your input. After reviewing your post and the use case I posted, if I am following you correctly, you are suggesting that I capture and store each of the point a to point b route combinations as one record (or segment) right?

So applying this approach to the use case I posted, Thailand to Malaysia would be stored as one record for example THAMAL (using the first three letters of each country) to create a segment route code.

Am I following you correctly or have I completely misinterpreted you?
Yes.  Thailand to Malaysia is segment 1
Malaysia to USA is segment 2

If there were two waypoints, there would be three segments.

I know it seems attractive to use country abbreviations to identify legs of a journey but is that the level at which you cost the segment?  If it is more accurate to cost port to port, then that is how you should do it.  Coming from Malaysia, to the US could be a significantly different cost if you were going to Long Beach or New Jersey.  In the first case you would head East around the earth and in the second, you would head west unless you wanted to go via the Panama Canal.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:

If you are worried about the size limits of an Access database (2 gig)  then I would start out using SQL Server Express (10 gig)  with T-SQL.

TIP:  There is no reason you can't use multiple back ends and they do not have to all be the same type. You can even create .NET add-ins that work inside Access.

If this were my project I would use SQL Server and T-SQL along with neoj4 for the data layer. I would make the presentation layer in what works best. That could be Access and/or .NET (VB and/or ASP)
Laila JacksonAuthor Commented:
@ Pat - Great. At least we are on the same page.

For routing codes, I am using a mixture of UN and ISO location standards which I have direct access to. For example, port codes are three letters, country codes are two letters. My database contains tables with roughly 50k port codes and 250 countries all using these standard naming conventions as identifiers. That said, each of the above has a unique ID (auto number) as a primary key in each table.

In so far as costings go, as I said earlier, I am costing down to the lowest level of granularity.  That is, in the case of the US bound product of the use case I posted, I am actually calculating the cost all the way down to the final retail customer outlet (i.e. store 1, store 2, store 3 and so on).

So as you say, I know the cost from Malaysia to US at port level, but then there are domestic costs for getting the product to each retail customer which I am tracking and calculating as well. For example, in the case of Thailand to Store 1 in Los Angeles, it costs $x to get my product to the port of Los Angeles via Malaysia, then it costs $x to get it moved to Torrance, Riverside and Los Vegas and finally $x to get it delivered into each retail store in both LA and Vegas.

This is why I need to track each route end-to-end so that I can actually calculate the total cost.  At any case, I want to provide Store 1 with a Final Cost of getting products from Thailand to their retail outlet.

I will post an actual sample of the costing shortly.

It sounds like you want to cost each segment separately.  I would make the costs additive.
Thailand - Malaysia = $10
Malaysia - Long Beach = $23
Total = $33
Laila JacksonAuthor Commented:
@ Pat - Please excuse me I am not sure what you mean.  Are you talking about running a cumulative cost model?

I mentioned an example which I have enclosed with this post.  Its purely fictitious and does not compute but it should give an idea on what my end result should look like.
Laila JacksonAuthor Commented:
@ Boyd - Hey thanks for your input.

From what you have suggested, I am already considering everything except the neo4j part because of the learning I would need to undertake to get my desktop application, SQL server and then neo4j to communicate at read/write levels.  I think I mentioned that I have only just spent the last four months learning and tinkering with vb.NET after working with VBA for 3 years.  Maybe its best I just start with getting a baseline version working with SQL and .NET to start with and then extend it to neo4j in future?

On that note, I am really wanting to move away from using Access as a front-end altogether simply because it is so limiting in what developers can do with such demanding projects.  I guess one good thing is that I have already written MOST of the business logic for this project in VBA so it will be easier for me to convert all my code over to .NET over time do you not agree?

I think your limitation is with schema design rather than Access.  Moving to .Net isn't going to help at all with your question.  Once you settle on a properly normalized schema, things will fall into place.  The table on the right of your picture is sort of what I had in mind except that in your example, you have 5 trips merged.  That is a reporting issue rather than a storage issue.  Store the data correctly and you can report on it in numerous ways.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Lai ,

I agree with PatHartman's thinking that "your limitation is with schema design rather than Access.".  Access is more of a presentation layer.  

Getting the schema design properly normalized will be key. That is independent of the development platform.

That said, I have spent a great deal of time this week trying to determine a viable option of approach and if it were not for the dynamic nature of what I am doing, I would have been fine creating static tables to allow for the same expected data each and every time. Unfortunately, this is not the case and by dynamic, I....

A properly normalized database will easy handle the "dynamic" nature. In my opinion, that is why you spend all the time and effort on designing a good schema. Using the rules of data normalization as a guide.

This is very similar to a invoice that can have multiple line items.  Each item with a different quantity and price.

PatHartman said:
Store the data correctly and you can report on it in numerous ways.
Exactly. I agree 100%. That is why a properly normalized database design  is so important.

If this were my project I would get the database design figured out. I would do some modeling and design testing (usually on paper) to see if the design is on track. Only after this is done would I even start considering what development platform to use.
Laila JacksonAuthor Commented:
Hey thanks for the constructive input guys. I notice our timezones are rather different hence such a long delay in responses.

I take on board everything you have both said and fully appreciate that this question is not the typical problem posted on this site. By that I mean I am not asking for help to resolve a data conversion issue in vb or an issue with a SQL parameter query attached to a combo box on a form.

That said, the initial objective of my post "Access database storage tables design logic" was to determine a suitable approach to the design schema of my tables based on the information I provided and based on your feedback so far, it would seem that the information I have supplied is somehow inadequate.

If this is the case, please tell me what more information you would need to help in answering my question?
Dale FyeOwner, Developing Solutions LLCCommented:

I'm not a logistics expert, but for tracking and costing purposes I would go with a structure similar to what I described above.  The example you provided is very "spreadsheetish", one row for each overall shipment.  But this means you need an unknown number of columns for successive points in your route.  This might work in a spreadsheet but is very difficult to construct in a database.

You need a shipment table which describes the overall shipment, and a shipment details table, which has one row to represent the start point, end point, and other pertinent data for each leg (segment) of the shipment.

Personally, I believe that a "shipment" should only be considered a "shipment" until it reaches a location where it is broken up into smaller shipments.  This is because the cost of splitting a shipment would be related to the % of the shipment that moves forward to the next stage of the trip.  I would call this a loading or handling fee, and that would be attributed to the point of departure.

So, in the diagram from your first post:

Shipment #1: Thailand => Malaysia => Los Angeles => Torrence
Shipment #2: Torrence => Riverside
Shipment #3: Riverside => Store #1
Shipment #7: Torrence => Las Vegas => Store #103

In your table, this might look like:  I've included [Cube] and [Weight] here, but they really belong in tbl_Shipment
Shipment #  Segment  StartPoint_ID   EndPoint_ID  [Handling fee]  [Cube]  [Weight]  [Cost]  [Dept Date]  [Est Arrival]
1            1       Thailand        Malaysia          2000          X        Y        Z     Aug 10, '15   Aug 11, '15
1            2       Malaysia        Los Angeles                     X        Y        Z     Aug 13, '15   Aug 22, '15
1            3       Los Angeles     Torrence          1000          X        Y        Z     Aug 23, '15   Aug 23, '15
2            1       Torrence        Riverside          500          A        B        C     Aug 24, '15   Aug 24, '15
3            1       Riverside       Store #1           200          E        F        G     Aug 25, '15   Aug 25, '15
7            1       Torrence        Las Vegas          500          H        I        J     Aug 24, '15   Aug 25, '15
7            2       Las Vegas       Store #103                      H        I        J     Aug 25, '15   Aug 25, '15

Open in new window

In reality, you really only need to define a shipping segment if the carrier changes, the mode of conveyance changes, or the shipment is split.  So, if you load your sea-land van in Thailand onto a ship and it goes to Los Angeles on the same ship, and is never removed from the original carrier, you could combine shipment #1, segment #1 and #2 into a single route segment.  But if the sea-land van is transferred from one ship to another in Malaysia, then it would probably have a handling fee associated with it and would therefore need a separate route segment.  

And if the the same carrier transported the sea-land van from Torrence to Store #103 in Las Vegas, you would only need one route segment for that as well.  But if it went from Torrence to Vegas via train and then was put on a truck for transport to Store #103, you would need two segments for that "shipment".

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Laila JacksonAuthor Commented:
Hello Dale,

Thanks for the excellent response to my question.  I understand your notation about not being a logistics specialist and I fully respect that.

If I am interpreting your instructions correctly, then essentially what you are saying is that I should take a multiple table approach to this problem rather than try and cram everything into one "spreadsheetish" tabIe as you described which is clearly not the best approach to follow which was already touched on by both Pat and Boyd earlier.

As I communicated earlier, I already have dozens of tables with all the information needed to run this application, I am merely wanting to ensure that the business logic and flow of it all is sound enough to cater for all the different scenarios I am planning for in my development.

I am going to spend a bit of time rustling the approach you have described to see if I can get it to to work for me and will revert back later on.

In the meantime, thanks again for your input.

Laila JacksonAuthor Commented:
Thanks to all for the input, I have achieved what I wanted for this exercise.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.