October 27, 2015 at 7:25 am
Hi,
I would really appreciate some advice on best approach here.
I´m building a "report tool" where users can log in and register opening hours.
The "end user"/website visitor will see a simple overview for specific 5 days intervals
where each user is listed in rows with columns for each date in that interval containing opening hours info.
The users registering opening hours will be able to report their opening hours for up to 12 months in advance.
They can specify different intervals and then register specific opening hours for Mondays, Tuesdays...
For instance a summer period: 1 june - 30 aug and autumn period 1 sept 31 oct etc.
I see 2 different approaches:
1. The users register their different periods in one table and with corresponding openinghours in a different table with columns for each opening/close (mondaysopen, mondaysclose,
tuesdayopen,tuesdayclose etc.)
This would properly be a convenient way to store the info also when it´s time for the users to make changes to bring it back into the form to edit.
As for viewing the info from the frontend/visitor this would require quite a bit. I would loop through the specific 5 day interval and lookup corresponding periods registered for each user and join
the table containing opening hours for each week day, then match the right day with the current day in the "5 days interval loop)
2. The users register all info in the same table without caring about weekdays for instance: datetimeopen, datetimeclose.
This would be really convienient on the front end as I would simply select the dates for the specific 5 days interval and then loop through the days and "print" info if this exist.
on the other hand I fear it might "heavy" on the back end as I would potentially loop through 365 days as a user choose to register same opening hours for the whole year.
I hope this makes sense, grateful for any advice/comments!
Thanks!
November 4, 2015 at 1:11 pm
I personally like the Normalized way (no redundant data)
Have everything organized in separate tables... As for queries, If you have the right structure and indexes, it shouldn't be a problem... there are always ways to write performing queries or improve the performance of queries.
That's my 2 cents.
enjoy!
November 5, 2015 at 3:14 am
jghali (11/4/2015)
I personally like the Normalized way (no redundant data)Have everything organized in separate tables... As for queries, If you have the right structure and indexes, it shouldn't be a problem... there are always ways to write performing queries or improve the performance of queries.
That's my 2 cents.
enjoy!
Fully support the above! If your database will grow huge and reports will need to become very complex and large, you may see in creating dedicated reporting/data warehouse database where you can denormalize some data for reporting and any other analyse purposes
November 19, 2015 at 1:23 pm
Thank you for your replies!
The project has been delayed but I will try to follow your advice
Thanks!
November 19, 2015 at 9:27 pm
A couple of succinct (and old) MS Access articles that will help you choose a flexible normalized design:
Description of the database normalization basics
How to design a sample table to support questionnaire applications in Access 2002
November 21, 2015 at 12:16 pm
I like option 1 because it can make both viewing and editing simpler for the users.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply