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

[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

 

[The_Doc_Man]

Question as asked:

Where's the sweet spot of table size vs number of tables

Answer: This is the wrong question.

You have as many tables as it takes to correctly reflect your business model. This is a mandatory exercise in setting up any database. You have to see what you do on paper first. There is an "Old Programmer's Rule" that I often quote: If you can't do it on paper, you can't do it in Access. Meaning that if you can't draw up a diagram of your business data flow, you are not yet ready to implement anything.

You mentioned members, sessions, and exercise-set data. That's probably three tables right here. But then you started talking about each session having its own table. And that is structurally a definitive no-no.

If you are looking for database "best practices" then the FIRST THING you should study is "database normalization." (If you only looked for "normalization" without including "database" in your search, you would also get political, diplomatic, and mathematical normalization.)

You would have ONE table for members. One of the fields of that table would be the member's ID number, which you could assign yourself or you could have Access assign it for you (look up "Autonumber" in this forum, it has a GREAT search function.)

Your session table would have a session ID but since members can attend/participate in multiple sessions, there is a question to be answered regarding whether sessions are group or single in nature. If single, then each session record has the member ID in it for sessions that the member attended. If group, then you have something we call a "junction table" (again, use this forum's search function) for an intermediate table that lists a member and session ID in a single record.

As to the member's exercise plan, that would be a separate table of many records with an order-of-execution number and the member's ID number as records in a single table.

Using queries lets you tie all of these together. One query based on a given member ID can show you that member's session history. Another query on member ID would allow you to list that member's exercise plan information. Another query on (group) session ID would show you who attended that group.

Here's the other "Old Programmer's Rule" that applies because of your comments about reports: "Access won't tell you anything you didn't tell it first." (Or at lest, told it how to find/compute it.) Meaning, while still in the design phases, decide as much as possible what questions you want to ask of your database. Realize that it can't answer a question about squat thrusts if you don't have data on squat thrusts. (I'm generalizing a bit here, but I hope you get my direction.) So if you think there is a question you will want to ask, be absolutely sure that you have arranged for that kind of data to be captured/fed for your database.

As to best practices in general? In a sense, the best practices are the ones that work for you, but that stay as close as possible to good design processes and good implementation processes. Access theoretically can take you a pretty far way on the path to normalization, but most of the time you are OK if you can get AT LEAST to 3NF.

A far more important concept is to never make your users worry about the "innards" of your system. Make a switchboard or dispatcher form that is the only thing users see. They then have to click a button to get a form for a specific action like adding a member or changing that member's status. And the form should "know and use" the table structure for members. Don't make the user have to know the details. The form can translate for you (or at least Access can), so don't show a numeric code when a translated code is possible. Stated another way, the tables really, REALLY, REALLY should be normalized - but the forms don't have to reveal that fact.

Avoid the temptation to make one form do too many things. You're just asking for confusion. So the next question is, "Doesn't that mean a lot of work to make a lot of forms?" Yes, but... here's my suggestion: Before you make the first form, make a semi-blank form template that has your background and color scheme already laid out. Have a few function buttons like Save and Undo and Create and Delete. (The Access control wizard can help you build them.) Then when you are ready to define individual forms, copy the half-built template. The result will be an application that has multiple forms, all having the same look and feel. You do the Form_Load, Form_Current, Form_Open, etc. in the template and then customize the copies. However much you do in the template is that much that is already done in the copy, so you reduce your workload.

Looking back, I see that this post got longer than I anticipated. Take it as one old man's ramblings about how I might approach the problem. If it isn't your style, that's OK too. After all, you did ask for ideas. Here's mine.

 

[Sean Jackson]

Shit Doc,

Great reply mate, really appreciate the detail.

As the old adage goes, you don't know what you don't know.
I've been struggeling to find aN overview of access DB best practice, to make sure I'm taking everything into account, what you've written here, seems pretty exhuastive, and there where a couple of things you've mentioend that I'm still vague on..."a-google-ing I will go".
So much appreciate your effort.

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?

Any thoughts?

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

 

[stopher]

Shit Doc,

Great reply mate, really appreciate the detail.

As the old adage goes, you don't know what you don't know.
I've been struggeling to find aN overview of access DB best practice, to make sure I'm taking everything into account, what you've written here, seems pretty exhuastive, and there where a couple of things you've mentioend that I'm still vague on..."a-google-ing I will go".
So much appreciate your effort.

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?

Any thoughts?

Sean

From what I've read so far it seems the only table that is going to get "big" is your set table. Access will happily manage a few million records in a table. There isn't a limit to the number of rows as such. It's real limit is the 2GB filesize limit that you would be constrained by. This thread here shows some attempts to reach the limit. Seems one guy got to circa 65,000,000 rows. Read here about Access 2016 constraints.

Like Doc said, focus on getting your design right. If you do run into size issue down the line you have options. You can split your "big" table into a main and archive where the archive would be in a separate database (noting you can link tables from different dataabases). Or you can move your all tables to a server based solution e.g. SQL Server, and keep Access as your front end. Spec for SQL Server 2016 is circa half a million terabytes.

 

[gemma-the-husky]

Where's the sweet spot of table size vs number of tables

but the other generalisation is that tables are long and thin, not short and wide like a spreadsheet

so you get

members table: MemberID, Member Name.
lots of rows, one for each member

classes table: ClassID, Class Name
lots of rows, one for each class

enrolled table: EnrolmentID, MemberID, ClassID
lots of rows, one for each enrolment

attendance table: AttendanceID, EnrolmentID, Date
lots of rows, one for each class attendance

In this example the Red Items, could be autonumbers. Their purpose is to give a unique reference to each row, and to relate tables to one another. The blue items represent the linking entry referred to above. The PrimaryKey - PK of one table becomes the Linking Data - the ForeignKey - FK in the linked table

You could use names, rather than numbers, but numbers are more efficient, and easier to manage. Also it means the names are not part of the linking data, so you can change a class name, say without affecting anything else. If you use the Class Name as the PK, and FK, then if you want to change the name, you also have to change the FK values everywhere they are stored. It's doable, but I prefer not to have to.

You can now add other information in a logical place.

So for a member, you might want to store his DOB, Address, Phone Number, Membership Category and so on. Membershgip Category is an interesting one. If you only want his current category, you can store it in the members table. If you want to store a history of changes to his category, then you need another table.For a class, you might want to store the date and time, the instructor name, and so on.

In general if you only want 1 of something, it can go in a table. If you want several of something, you need a different table.

finally, relationships can only be 1 to many.

if you have a relationship that's many-to-many - such as your members and classes (members can be in many classes, and classes contain many members) you MUST devolve the many-to-many into 2 1-to-many relationships by adding a junction table.

Sorry if this is too basic, and I hope it helps.


finally - class attendance is an interesting one.
you have a class with 20 members, and only 12 attend one week. Do you store a record for all 20 each week, and mark some as attended, and some as not. Or do you just store the attendees and "work out" that the others didn't attend. The database isn't a spreadsheet with column for course date, and rows for members. It's a database. You can present it as a grid, but you don't have to store the non attendees. If you store the attendees, then it's easy to find the non-attendees, as these are course members who aren't in the attendees list.

It can be hard thinking of your data in this sort of way.

 

[CJ_London]

Cross Posted here

http://www.accessforums.net/showthread.php?t=63338

and here

https://answers.microsoft.com/en-us...-spot-of/1a620c5c-324f-498d-b52a-e5228c56b305

with much the advice

 

[The_Doc_Man]

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.

Since I don't haunt the other Access forums, I didn't catch the cross-posts. Just be aware that if you cross-post a lot, folks will often let you be "someone else's problem" and will ignore you. I didn't do that and I will answer your question for you.

The question about the set table is whether a lot of people have the same sets. If so, you have not broken this down far enough. What is the BASIC thing that goes in a set? Would you call it an "exercise" or something like that? If so, you have set table and an exercise table and a JUNCTION table that lists all exercises in a set, with foreign keys to the Set and Exercise tables and perhaps a specific repetition number if one set might include 10 push-ups and another might have 15 push-ups.

Then you have a link to your SET table and as many people as use the same set will just point to the same Set ID number. There is no reason that you would give a name to a particular Set if the ID number is unique. You would only use the IDs for table internal linking in the SetID ==> Set Components <== Exercise tables.