List of last used tables

[isladogs]

I'm intrigued by this and find it hard to countenance the design of such a system. The biggest db I look after only has about 30 tables of which about 10 are fairly static lookup tables - but the relationships diagram is quite complex enough, and it is properly normalised.

Is a properly normalised Access database with hundreds of tables a frequent occurrence ?

It's certainly not uncommon

My largest database has around 330 tables mostly in a SQL BE.
See screenshot in this post https://www.access-programmers.co.uk/forums/showpost.php?p=1553897&postcount=2

It is indeed fully normalised though I'm not sure why you think that's relevant.
In fact, fully normalising is likely to reduce the number of fields per table but increase the number of tables

 

[BeeJayEff]

It's certainly not uncommon

My largest database has around 330 tables mostly in a SQL BE.
See screenshot in this post https://www.access-programmers.co.uk/forums/showpost.php?p=1553897&postcount=2

It is indeed fully normalised though I'm not sure why you think that's relevant.
In fact, fully normalising is likely to reduce the number of fields per table but increase the number of tables

Fair enough - the only time I've seen a database with such a huge number of tables was because it was far from normalised (e.g. one table per month for several years).

 

[isladogs]

Ah I see what you were driving at now....

The database I quoted has been in continual development for over 15 years, is used in a number of schools and does cover a huge amount of staff, student and whole school data.

At one time it contained even more tables until I removed some old features that we're no longer in use!

 

[The_Doc_Man]

The database that was used to make personnel management decisions for the U.S. Navy Reserve had only about 240 tables - which sounds like a lot - but fully HALF of those were either "translation" tables that converted codes to a longer name or constant-lookup tables to supply numeric rates based on member attributes. Translations like the rank-rate table that converted E5 to Petty Officer First Class or O4 to Lieutenant Commander. Constants like the yearly uniform allowance based on the reservist's location and rating.

Another third of the tables (about 80 or so) related to external interfaces with about 20 other Navy organizations. They weren't needed for the internal processing but we needed places to hold things when we were preparing files for electronic transmission to an agency that used different codes than we did.

So if you do the math, about 20 tables were the active parts - with three being primary: Person, Billet, and Unit.

That is the MOST complex database I've ever seen or worked around and decidely YES it was properly normalized. It wasn't Access, though. It was an ORACLE monstrosity.

 

[isladogs]

It is a very wide ranging application. If anyone is interested, a summary of it's main features can be found here: http://www.mendipdatasystems.co.uk/school-data-analyser/4584605482

I mentioned in a recent thread that I'd made a decision with that monster database over 10 years ago that I'd regretted ever since.

At the end of each academic year all attendance, reporting and other grade tables were archived into separate tables. In addition when students left school all their data was separated into leavers tables plus of course leavers archived tables.
This made finding the data much faster but significantly increased the number of tables together with additional queries, forms, reports and all the code that accompanied them.
Furthermore the end of year routine had to be checked each year and modified where field changes had occurred.

All in all many hundreds if not thousands of hours of work have been added by those decisions and without rewriting the entire database app, it's ongoing work. Luckily, development work has significantly slowed down as it now has all the features needed by all my client schools.

If the decision had been to keep all leavers and archived data in the original tables, the total number of tables would drop by 80 to about 250 with similar drops in numbers for all other database objects. The FE (approx. 130MB) would be smaller and probably faster.

So whilst it still performs very well, it is a behemoth that taught me a lesson I won't forget.

 

[Thales750]

I was informed last night, by a very good friend of mine, that I misspoke. So there is a new edit in this post concerning ISO Certification.

This system is mission critical for an entire chemical plant.

It manages: Shipping and Receiving, Plant Operations (this is too complex to even comment on), PHAs, NCR documentation and remedial planning and tracking, Lab results including SQC Data pushed out to Excel, Training, Interfaces with Accounting, etc.

The Operations side creates material balances (inventories) from continuous flow equipment, keeps track of levels in all tanks, Railcars, and Trailers. The second part of material balances is to group the various flows into customer orders. There may be a few of the 318 tables that are unneeded, but none of them are being used as spreadsheets.

But after all that, it sees into the future. It creates just-on-time ordering and maintenance schedules to optimize plant production months in advance. It accomplishes this by creating SOPs from historical data.

There's more, but we have created hundreds of pages of docs, Power Points, and videos, to disseminate all the information.

Three years in the making. This has been an incredible experience.

Some really smart people worked on this project, both at the customer's site and here at Clear Lake Data, including a Microsoft Access MVP; without whom, this would not have been possible.

 

[Thales750]

This is the final Count of the new Merged Database.

Query2Object
Type Count
acForm 341
acMacro 3
acReport 130
acModule 11
acTable 12
acQuery 297
acTable 306 Linked