Table Structure (1 Viewer)

tmyers

Well-known member
Local time
Today, 06:31
Joined
Sep 8, 2020
Messages
1,090
Per my previous post asking which program to be used, I am going full stop on the Excel sheet and switching to Access.

I am struggling to wrap my head around how to do tables that handle changes for a job. I will try to lay out my thoughts.

So far I have made my main table and a couple supporting tables (see attached). I need to make a table that can hold the "base" info for a project (such as the quote being used to issue PO's for). That seems relatively easy enough, as I can integrate that with my other app.

However this is where I start getting lost. How would my next table(s) look to handle changes? I would need to input quantity changes (that is the main thing), but also keep track of comments, attachments and maybe a few other odds and ends.

So for a given project, I would see the base order for the job then see each change made during the projects life time and the financials associated with each change. So at any given time we could look at essentially a snapshot of the financials of a job for each change made, both overall and each individual item.

I think this would be maybe 2 tables? One to hold the base data and the other to hold the changes and tie it back to the overall projectid?
 

Attachments

  • Capture.PNG
    Capture.PNG
    18.5 KB · Views: 138

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Sep 12, 2006
Messages
15,614
Seriously, I would do this by not trying to track change in the first instance. I would imagine it's going to be a difficult enough project to complete.
I would try to get it working by just managing the live data. That will be hard enough. Maybe you don't need to track change at all within the database. Perhaps just a monthly progress report with all the latest data would be sufficient.

So if the start date changes, or prices change, or quantities change, or the project manager changes, just change the live detail. Once you get it working, you can then consider which changes you need to track, and which you don't, and add them in later.

The first step should really be to define the data you want to manage, and them split this into a normalised table structure, so you aren't duplicating the same data in different tables.
 

tmyers

Well-known member
Local time
Today, 06:31
Joined
Sep 8, 2020
Messages
1,090
Seriously, I would do this by not trying to track change in the first instance. I would imagine it's going to be a difficult enough project to complete.
I would try to get it working by just managing the live data. That will be hard enough. Maybe you don't need to track change at all within the database. Perhaps just a monthly progress report with all the latest data would be sufficient.

So if the start date changes, or prices change, or quantities change, or the project manager changes, just change the live detail. Once you get it working, you can then consider which changes you need to track, and which you don't, and add them in later.

The first step should really be to define the data you want to manage, and them split this into a normalised table structure, so you aren't duplicating the same data in different tables.
Is building a system for this kind of thing really that complex? :oops:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:31
Joined
Feb 28, 2001
Messages
27,001
Building any system to match a real-world process that takes more than a small handful of people or more than a couple of hours to complete by hand will always be complex. Anything that involves lots of parts - and particularly, lots of moving parts - will require lots of planning to understand the data flow. Here is some advice I gave someone else a while back when they were entering into a new project.

 

tmyers

Well-known member
Local time
Today, 06:31
Joined
Sep 8, 2020
Messages
1,090
Hi Can you upload a zipped copy of your database?
Currently I can not. After various comments in my other post and what not, I have rolled the idea in my existing DB and I believe I have it handled now lol.
 

Minty

AWF VIP
Local time
Today, 10:31
Joined
Jul 26, 2013
Messages
10,355
Is building a system for this kind of thing really that complex? :oops:
Any system can be as complex as you let it be 😁

For instance, you are setting up a system for hiring widgets to clients.
You initially set up a quote. It then transpires that a client might want 3 widgets on one site and 2 on another site.

Do you let the users have multiple sites referenced on one quote(how they have historically produced the quote)? or force them to issue 2 quotes?
Trust me one is much less hassle than the other, but the complicated method gives them an easier life.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 19, 2002
Messages
42,981
A relational database is not like a spreadsheet where you can save a copy of everything at a point in time. The schema is probably normalized and that means that there are numerous tables involved and only one field on one row of one table might need to change. Are you going to save all rows of all tables with the same versionID to pull everything together? That means you might end up with multiple "versions" for each change. So you change three fields on one form and when you save it, you run your version code that copies everything and saves it. Then you change two fields on a different form and when you save it, your version code copies all the rows again. If you don't do it that way, you will have a monster of a job getting the relevant rows to come back together again so you can show what happened on Tuesday. Doing it with timestamps won't help because each record changed will have a different timestamp. That's fine if you only make one change on any given day but what if you make two changes? How will you pull everything together. It's not like it can't be done, SQL Server does something like that with it's forward recovery process. But it certainly isn't trivial.

A different option which is simpler is to use a PDF to save a "report" of whatever you changed for this version. The complication with this ensuring that the "report" gets generated at the proper time and the user doesn't forget.

So, talk this through with the users and ask them what they would use this feature for. It is very complicated once you think about it. Even for apps where i keep history, I don't ever try to put everything back together at a point in time. The history is basically to keep track of who made changes and when.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:31
Joined
Sep 12, 2006
Messages
15,614
I mentioned this before. Date stamping everything is over the top, and really doesn't help reconstruct a snapshot of past data. In general terms, how often would you ever want to see a historical position. You are giving yourself an enormous headache for something that's hardly ever going.to be used. Just take a snapshot at the end of every month.

It's like accounts. You arrange to prepare accounts to suit your cycle, be that monthly, quarterly, 4-weekly. Having done so, you don't suddenly decide to reconstruct the position as of 12th January. You might be able to, but it's an enormous task,
 

Users who are viewing this thread

Top Bottom