The Goldie Locks problem - Where's the sweet spot of table size vs number of tables

[Sean jackson]

I'm automating a manual system we have in our little fitness business. (currently in very manual excel)
Basically automating how we program strength sessions.
I'm pretty new to Access, but have a strong excel background, so nothing too strange or new.

My question revolves around where is the sweet spot when breaking down tables.
I understand it's important to keep tables relatively small in Access to avoid lag time...but how small, and when is small too small?
...details follow.

At its most simplest, there are 3 primary information sources
- Our Member
- Each specific Session
- And each individual members Set Data (i.e. the information we need for every single lifting set that member does, weather a warm-up set or a working set)

At one end of the spectrum the session and set data could all go in one table, meaning every set record is just listed in one large table.
This could get as large as 300,000 records produced each year, and 25-30 fields = a possible 9,000,000 cells of data. Ouch.


One way to avoid lag would be to archive any records older than let's say 2 months, but frankly we'd still pulling queries from the old data regularly, when we pull strength progression over time graphs etc. So, I don't imagine this would work.

Another option is we break the tables up into very small tables
i.e. There is a new table for every individual member prescription.
In other words, every time an individual member turns up to a session to lift, they have a new table related to that specific member for that specific session.
Such a table would only house perhaps 10-25 records, and perhaps <20 fields.
These would have Member ID and Session ID's
I'm still trying to wrap my head around query's but one could query specific individual members and specific session IDs to pull a session report showing all sets for all members attending that specific session, and therefore limit the number of records the system is trawling thru.
But there would mean perhaps 50,000 new tables each year.

A middle ground option would be to perhaps have


- Each session have its own table, so it includes every set for every member attending that session (max of 336 records and < 30 fields)
- Or each member has their own table with every set record they ever have sitting in that table (Max of 2,500 records created / year and <25 fields)

This last one makes sense as we tend to be pulling reports from both sessions and individuals, but the session reports are pulled from far less data then the individual reports.

I'm hoping this is all making sense.
Again, my fundamental question is if archiving records is a bad idea, as we will be regularly pulling reports from all records.
Then where does one find the balance between number of tables and number of records / fields per table?

Also, can anyone recommend a database best practice tutorial, I'm finding the access tutorials tend to talk about using the access interface more than best practice itself. i.e. Normalization (1FN, 2FN, 3FN, BCNF), and other DB best practice stuff, hints and tips, pitfalls, etc.

I look forward to your thoughts

Cheers


Sean

[Micron]

Yikes. You really have what I call an Excel mentality when it comes to thinking about databases. I don't mean to be harsh, but you're way off base with some of those suggestions. You would quickly be asking why on earth anyone would create a database when it's so much work. Aside from a grid that looks like rows and columns, there is little in common between Excel and Access when it comes to data storage. You might even want to forget what you know about Excel when embarking on this journey.

Maybe we should start with a few links I stole from Orange here (between *'s), and get you to review fundamentals of normalization, since you asked about that anyway. Don't worry about lag. It won't be a problem unless you disregard all the tips you might get (not just here, but in the links anyone provides).
****
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html
****
In addition, don't make the common mistakes when it comes to naming, http://www.access-programmers.co.uk/...d.php?t=225837
reserved words, http://allenbrowne.com/AppIssueBadWord.html
lookup fields (don't),
calculated table fields (don't) http://allenbrowne.com/casu-14.html
and multi value fields (definitely don't) see "What's Mixed" here http://allenbrowne.com/Access2007.html
and definitely understand what autonumbers are for http://www.utteraccess.com/wiki/index.php/Autonumbers

Based on your post, you'll need a table for members and one for sessions at a minimum, but there's probably more. You should be able to reflect better on that after you've read up on normalization. The way to keep the speed acceptable will be based on your normalization approach, the proper use of indexes, and efficient queries. The table size limit is (I believe) 2 GB including overhead, which is an enormous amount of text. Now there's a clue - don't store pictures or attachments in your db.

[Sean jackson]

Thanks for the reply Micron, I appreciate your time. Great links as well, cheers!

Totally agree that there is a huge difference between Excel and Access, however, I'm finding the logic Excel has trained in me, is making my launch into Access a hell of a lot easier.

I'm definately going to take a look at the links, but I'd like to address one of your comments, in regards to other stuff I've read.

Don't worry about lag. It won't be a problem unless you disregard all the tips you might get (not just here, but in the links anyone provides).

Based on your post, you'll need a table for members and one for sessions at a minimum, but there's probably more. You should be able to reflect better on that after you've read up on normalization. The way to keep the speed acceptable will be based on your normalization approach, the proper use of indexes, and efficient queries. The table size limit is (I believe) 2 GB including overhead, which is an enormous amount of text. Now there's a clue - don't store pictures or attachments in your db.

Firstly, I have aggresively normalized my table diagram, meaning a whole lot of tables.
But I am reading that one can "over table" and I'm also reading that Access can lag seriously well before the 2GB limit
So there's a middle ground here somewhere.

Producing 9,000,000 fields / annually into a single table is supposed to cause all kinds of issues.
So there is apparently a middle ground here, even if normalization worked for all sets in one table, that table might be too large.
Where as breaking everything into seperate very small individual prescription tables (each members workout for each session) would be over kill the other way, to many small tables.

I write this as I just want to make sure, I've made myself clear. If your advice remains the same, I'll drive on and do my homework.

Following is a rewrite of my question.

To bring me back to the core question
In regards to normalization clearly, there needs to be a seperate member and session table.
I think a better way to voice my question is when it comes to the "Set Table"

A member might turn up for a GROUP session, and we might plan for him or her to execute, 2 movements, of 5 warm-up sets, and 3 working sets. THere might be a maximum of 24 members in that sesssion.

So for that session alone there is a maximum of 384 sets (records.)
This Set table is going to get pretty big pretty quickly.
And my understanding is this can quickly become an issue in Access.

Ways of breaking this table up
- Archieve the older records, apparently not a good option, die to our need to continully be dipping into old records when pulling strength progress erports
- So we are left with the choice of either breaking tables up by session or member
- A "session sets table" for each session would have a maximum of 384 records
- A "member sets table", a record of every set a specific member has ever done, could produce upwards of 10,000 records per year.

Assuming I am correct in that there is a partical limit to table sizes befre it must be address by either an extra level of tables or be archiving data, where is this limit generally, and is this extra level of tables a good idea?

Cheers

Sean

[Sean jackson]

Also, just a super rough table diagram.
Still a work in progress, but it might give you a bit of an idea of whats going on
HTH

Sean

[orange]

As Micron said Access and Excel (database and spreadsheet) are very different animals. Be cautious with how well your Excel experience "helps" you with Access. Often people have to unlearn spreadsheet to learn/experience database.

You have made an interesting diagram and it may be what you need. I recommend you start with a 5-6 line description -in plain English, no jargon -of your intended business. Then work through 1 or 2 of these tutorials.

-Class Information System

-Catering Business
-Entity Relationship Diagramming

You will learn a procedure to go from a business description to a database design that matches your requirements. And the procedure can be used with any database.
It's also a good practice to get some test data and "play scenarios" against your evolving database design. If you can't answer a question, or can get a result based on your test, then reconcile/adjust the model or the data or the scenario. Make it work on paper before spending too much time on Access. Your evolving data model and refinements via testing will get you a blueprint for your database.

[Micron]

Agree with Orange. You have put a lot of thought into this so far, and I also suggest you write a short narrative (take a few more lines if you need them!) to help us understand the nature of the business if you need further design help. Not being savvy about the gym business, I can't relate to some of what you have, thus don't have much in the way of advice on your schema, but it looks like you understand normalization. I cannot, for example, arrive at 384 records with the numbers in your example. I can appreciate the concerns you've reiterated, but my opinion is that in a well designed db it is relatively easy to overcome those concerns, even if it means creating multiple back ends to house records by year or by some other aspect. So in writing this, I'll add a couple of more suggestions in support of that concern. Definitely split your db into back end (tables) and Front End (queries, modules, forms, reports) and turn off Name Autocorrect in your FE. When you think you are ready to verify your paper testing and have sample data, you create queries first, not forms. If you cannot get what you want from, or add/edit data using your query, there is something wrong (notwithstanding the inability of some query types to add/edit data, such as Totals queries).

Another thing you might want to start out with, given your concerns about space and performance. It looks like you have 8 values for muscle group (you have muscel?) but how are they input? It should not be manually. Asking for a report that includes muscle as part of the record will ignore all records where the value is muscel, and there can be a whole host of such input spelling errors. Put the values in tblMuscle and store the id in tblMovement. It will take less space to repeat 7 200,000 times than it will to store "Body Composition".

[aytee111]

Really good posts here, follow it and you can't go wrong. My 2c worth is to first get the design of the tables correct without worrying about number of records and lag. As Micron said, you can always create separate back-ends by year. But if you separate tables by number of records it will become a nightmare.

[orange]

Cross posted
www.access-programmers.co.uk/forums/showthread.php?t=290953

https://answers.microsoft.com/en-us/...a-e5228c56b305

Concern:

A message to forum cross posters

[Micron]

Wow. Shotgun approach - if the time stamps are correct, an answer and two follow up posts before the cross posting. Some of those answers were virtually essays.
What's odd is how some people b&$ch when at work, they find out they and someone else are working on the same thing even though they got paid for it. Imagine how it feels to work on the same thing as several others - for free.

[NTC]

1. Don't repeat data
i.e. you have a company's complete address; you don't want to re-enter that everywhere it appears in the application - - you just use its unique key value - and then link back to its single record in its single table location.

2. Don't lose data
i.e. sales tax today is 7% - you don't want to use a link because if tomorrow it is changing to 8% all of your past records would change - instead you 'write' that value into its record/table and it will repeat (as it should)

Those are the 2 principles that must be adhered to in defining "normalized" relational tables - beyond that what is a correct design is entirely dependent on the objectives of the application.