Sean Jackson
New member
- Local time
- Yesterday, 18:47
- Joined
- Jun 14, 2016
- Messages
- 4
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
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