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,
Lai
dataflowsample.jpg
case-study.xlsx
table.jpg
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,
Lai
dataflowsample.jpg
case-study.xlsx
table.jpg
I think you would be better served by a graph database, such as Neo4j, than a relational database like Access.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
Lai
@ 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,
Lai
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.
ASKER
@ 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?
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.
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.
Laila,
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)
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)
ASKER
@ 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.
Lai
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.
Lai
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
Thailand - Malaysia = $10
Malaysia - Long Beach = $23
Total = $33
ASKER
@ 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.
sample-costing.jpg
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.
sample-costing.jpg
ASKER
@ 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?
Lai
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?
Lai
Lai,
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.
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.
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.
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:
FWIW:
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.
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.
FWIW:
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Lai
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.
Lai
ASKER
Thanks to all for the input, I have achieved what I wanted for this exercise.
ASKER
problem-sample-2.xlsx