Database Design

[BlackCat68]

Hi this is my first post so please be kind. I am self taught in Access and pretty much a novice/amateur and have good basic knowledge of VBA and building queries, tables and forms etc.

However I usually just go ploughing straight in and build on the fly without too much thought of the overall design or end result. I usually have a basic idea but I want to start to think differently about how I go about planning the structure before I start to build anything.

I have been tasked to build an Order Tracking system in MS Access for one of our customers that the small company I work for has just started to support their IT requirements.

I have not spoken to the customer myself yet, by boss has just passed me a pdf file that the customer users to capture information about an order and asked me if I could build an order tracking system for them.

I have no other information as of yet but I just want to start thinking about how I would go about designing and structuring the tables etc. Obviously I will need to ask them lots of questions and also see there data at some point.

Attached is a screenshot of the pdf file to provide an idea of what they currently use.

Any advice would be greatly appreciated.
Thanks

 

[Minty]

You certainly need to start with the structure and tables, however, when talking to end-users about a business process it's often best to start at the end.

What do they need out of the system: reports, employee task lists, invoices, follow-ups, quotes etc. etc.
Once you know what the required outputs are, you combine that with their current paper process to determine what you actually need to record and the table structure follows.

And allow for them forgetting stuff they do "automatically" without thinking about it.
"Oh yeah, we always attach a picture and spec drawing with each quote"...

 

[BlackCat68]

You certainly need to start with the structure and tables, however, when talking to end-users about a business process it's often best to start at the end.

What do they need out of the system: reports, employee task lists, invoices, follow-ups, quotes etc. etc.
Once you know what the required outputs are, you combine that with their current paper process to determine what you actually need to record and the table structure follows.

And allow for them forgetting stuff they do "automatically" without thinking about it.
"Oh yeah, we always attach a picture and spec drawing with each quote"...

Completely understand your comment about customers. Sometimes its better to watch what they do manually and then automate it. Might be a little difficult given the current lockdown but I will do lots of talking before I go anywhere near a databate this time. Thank you for your advice, much appreciated.

 

[gemma-the-husky]

Is your company an IT company?
This might end up as an enormous undertaking.

 

[The_Doc_Man]

Since you are in early design phase, I have some old aphorisms that work for me to keep me oriented; "eyes on the prize" sort of thing.

1. If you can't do it on paper, you can't do it in Access.

You need to have a paper flow diagram that shows where data comes in, gets processed for each business action, and gets output. You might need to sketch or otherwise lay out the intended output as an example. You need to know within some degree of accuracy what your endgame has to produce. You need to capture details of data flow including exceptions. You must ask questions of the boss to find requirements. (Maybe not YOUR boss if this is work for someone else... but the boss of the process being analyzed.) You have to remember that you are building a map that you hope matches the territory. And you NEED that map. Because if you start on a journey without a map, how will you know that you have or have not reached your destination? From a more psychological point of view, if you don't understand your business process will enough to make a logical flow diagram of it, you don't understand it well enough to be writing code.

2. Access won't tell you anything you didn't tell it first.

Access knows exactly and only one thing... how to generally build tables, queries, forms, reports, macros, modules, and relationships. YOU are the subject-matter expert for your project. In THAT regard, Access is as dumb as a box of rocks. If you have that flow diagram from my step 1, you should now know what will be in your outputs. But often you have to step backwards to identify the source of a particular datum. If you need X output, you need to assure that somewhere in that pile of application, you HAVE a source of X. And if you need XYZ, you either need XYZ as input... or X, Y, and Z as inputs and you need a formula to combine them to XYZ. This DOES sometimes mean that you have to work backwards through your design to verify sources for all possible outputs. Because Access won't look it up for you from the web.

3. Never let the tail wag the dog.

Remember that you are building Access apps to help an existing work procedure/work flow. All too often, folks make the database in such a way that it forces certain actions onto the business process. IF that forced action IS an improvement (like improving accountability documentation), maybe that is good. But remember that you are building a data flow map based on the business process. You are not building a business process from a data flow map. If there is a disagreement between the data outputs and the real-world process, the real-world process is right. ALWAYS.

 

[BlackCat68]

Is your company an IT company?
This might end up as an enormous undertaking.

I work for a small IT company supporting IT systems for small and large manufacturing businesses. I have built a few systems in Access for other customers but not this one. I am going to ask for a work flow and hopefully spec out the system with them before I start the build.

 

[BlackCat68]

Since you are in early design phase, I have some old aphorisms that work for me to keep me oriented; "eyes on the prize" sort of thing.

1. If you can't do it on paper, you can't do it in Access.

You need to have a paper flow diagram that shows where data comes in, gets processed for each business action, and gets output. You might need to sketch or otherwise lay out the intended output as an example. You need to know within some degree of accuracy what your endgame has to produce. You need to capture details of data flow including exceptions. You must ask questions of the boss to find requirements. (Maybe not YOUR boss if this is work for someone else... but the boss of the process being analyzed.) You have to remember that you are building a map that you hope matches the territory. And you NEED that map. Because if you start on a journey without a map, how will you know that you have or have not reached your destination? From a more psychological point of view, if you don't understand your business process will enough to make a logical flow diagram of it, you don't understand it well enough to be writing code.

2. Access won't tell you anything you didn't tell it first.

Access knows exactly and only one thing... how to generally build tables, queries, forms, reports, macros, modules, and relationships. YOU are the subject-matter expert for your project. In THAT regard, Access is as dumb as a box of rocks. If you have that flow diagram from my step 1, you should now know what will be in your outputs. But often you have to step backwards to identify the source of a particular datum. If you need X output, you need to assure that somewhere in that pile of application, you HAVE a source of X. And if you need XYZ, you either need XYZ as input... or X, Y, and Z as inputs and you need a formula to combine them to XYZ. This DOES sometimes mean that you have to work backwards through your design to verify sources for all possible outputs. Because Access won't look it up for you from the web.

3. Never let the tail wag the dog.

Remember that you are building Access apps to help an existing work procedure/work flow. All too often, folks make the database in such a way that it forces certain actions onto the business process. IF that forced action IS an improvement (like improving accountability documentation), maybe that is good. But remember that you are building a data flow map based on the business process. You are not building a business process from a data flow map. If there is a disagreement between the data outputs and the real-world process, the real-world process is right. ALWAYS.

Thanks for this, it is extremely helpful. I am going to take your advice and try and do a paper flow of what they need before I go anywhere near Access this time.