Zones & Access Times

[Merkling, Steven]

I need to store information on Zones and Access Times in a database however I am having some problems with the design.

I have multiple employees.

Each employee can access any one of 32 Zones.

Each Zone is broken into 96 individual time slots per day giving the end user the ability to grant or deny access to any specific Zone in 15 min increments.

I would like the end users to be able to grant or deny access for each of these time slots each day of the week.

So my problem seems to be one of sheer volume

32 Zones * 96 Time Slots Day in each Zone = 3312 Total Time Slots per Day * 7 Days Per Week = 23,184 Time Slots Per week.

This is just for 1 employee.

100 employees = 2,318,400 Time Slots.

Any Ideas on how to structure this data in an easy to understand and easy to access fashion?

Thanks in Advance
-Merk

 

[cafe]

Let's get a few things straight before we look at the table structures.

Is this correct:

- Any employee (say Fred Smith) can select any one of the 32 zones (without
restriction).

- Then (for the selected zone), he can select any one of the 7 days of the
week (without restriction).

- Then (for the selected day), he can select any one of the 96 timeslots
(without restriction).

- Then (for the selected timeslot), he can say "ALLOW access!", or "DENY
access!", by ticking a checkbox, or whatever.


Questions:

- Does Fred need to nominate who or what he is allowing or denying access

to or for<? Or is that just implied?

- By day of the week, to you mean "generically" (eg. Tuesday), or
specifically (eg. Tuesday 9-Dec-2003)?

- Is it likely that the 96 timeslots would ever be made longer or shorter
(ie. less or more timeslots)?

TC

I need to store information on Zones and Access Times in a database

however I am having some problems with the design.

I have multiple employees.

Each employee can access any one of 32 Zones.

Each Zone is broken into 96 individual time slots per day giving the end

user the ability to grant or deny access to any specific Zone in 15 min
increments.

I would like the end users to be able to grant or deny access for each of

these time slots each day of the week.

So my problem seems to be one of sheer volume

32 Zones * 96 Time Slots Day in each Zone = 3312 Total Time Slots per Day

* 7 Days Per Week = 23,184 Time Slots Per week.

 

[Merkling, Steven]

- Any employee (say Fred Smith) can select any one of the 32 zones (without
restriction).

----- Only Fred Smith (the boss) or an administrator would be able to grant or restrict access to any one of the 32 Zones for any employee.

- Then (for the selected zone), he can select any one of the 7 days of the
week (without restriction).

----- Yes

- Then (for the selected day), he can select any one of the 96 time slots
(without restriction).

----- Yes

- Then (for the selected time slot), he can say "ALLOW access!", or "DENY
access!", by ticking a checkbox, or whatever.

----- Yes by ticking a checkbox

Questions:

- Does Fred need to nominate who or what he is allowing or denying access

to or for<? Or is that just implied?

----- Fred Smith would do this for each employee he wanted to grant access to a Zone(for a certain time slot). If he did not specifically grant access for a particular employee to a particular zone for a particular time slot they would not be granted access for that time slot.

- By day of the week, to you mean "generically" (e.g. Tuesday), or
specifically (e.g. Tuesday 9-Dec-2003)?

----- "generically" (e.g. Tuesday)

- Is it likely that the 96 time slots would ever be made longer or shorter
(ie. less or more time slots)?

----- No they will never be made longer or shorter this will be a built in limitation of the system.


Thanks
-Merk

Let's get a few things straight before we look at the table structures.

Is this correct:

- Any employee (say Fred Smith) can select any one of the 32 zones (without
restriction).

- Then (for the selected zone), he can select any one of the 7 days of the
week (without restriction).

- Then (for the selected day), he can select any one of the 96 time slots
(without restriction).

- Then (for the selected time slot), he can say "ALLOW access!", or "DENY
access!", by ticking a checkbox, or whatever.


Questions:

- Does Fred need to nominate who or what he is allowing or denying access

to or for<? Or is that just implied?

- By day of the week, to you mean "generically" (e.g. Tuesday), or
specifically (e.g. Tuesday 9-Dec-2003)?

- Is it likely that the 96 time slots would ever be made longer or shorter
(ie. less or more time slots)?

TC

I need to store information on Zones and Access Times in a database

however I am having some problems with the design.

I have multiple employees.

Each employee can access any one of 32 Zones.

Each Zone is broken into 96 individual time slots per day giving the end

user the ability to grant or deny access to any specific Zone in 15 min
increments.

I would like the end users to be able to grant or deny access for each of

these time slots each day of the week.

So my problem seems to be one of sheer volume

32 Zones * 96 Time Slots Day in each Zone = 3312 Total Time Slots per Day

* 7 Days Per Week = 23,184 Time Slots Per week.

 

[TC]

Ok. This will need careful handling to ensure that you get reasonable data
storage (not 50 gazillion records per person), AND, a workable user
interface.

Let's start by designing the tables on the asumption that >everything< will
be stored in a table. Then we'll see what that gives us, & whether it will
be workable. This is what you'd get, with that approach. (Note: the names of
the non-key fields in each table are just descriptive. You would want to
choose names that are not Access reserved words.)

tblZone - 1 row per zone.
ZoneID (pk)
name, description, whatever

tblDayNames - 1 row per generic day-of-week.
DayID (pk)
name (eg. "Tuesday")

tblTimeslot - 1 row per timeslot.
TimeslotID (pk)
start time
end time

tblEmployee
EmpID (pk)
name, date of birth etc.

Now we need a table defining which employees can access which timeslots on
which days in which zones:

tblAccess
ZoneID ( com-
DayID posite
TimeslotID primary
EmpID key )

If you found that 4-field composite key unweildy, you could change it to a
unique index, & add an autonumber as the primary key.)

The presence of a record in that table, shows that the specified employee
does have access to that timeslot, on that day, in that zone. The absence of
a particular record, shows he does not have that access.

As you rightly pointed out, this will be a lot of records! (You said
2,318,400 for 100 employees). However, 2+ million records is well within
Access'es capabilitiues (as long as everything is PK-d properly), >and<,
your calculation assumed that each employee has a record for each timeslot
in each day in each zone. If you only stored the "yes, he has access!"
records - as shown above - this would cut that figure down quite a bit.

On reflection, I really can't see any way to cut the records down much
further. Certainly you could eliminate the tblDayNames and tblTimeslots
tables - and I would probably do that - but this saves you a negiligble # of
rows in the scheme of things. You still have the same # of rows in
tblAccess.

The only other way I can see, is to store time >ranges<, and cauclate time

slots< at runtime. So if an employee had access to timeslots 1 (8:00-8:15

or whatever), 2 (8:16-8:30), 3 (8:31-8:45) and 4 (8:46-9:00), perhaps you
could store just 1 record for that employee, showing that he had access from
start-time 8:00 to end-time 9:00? Then for forms & reports could convert
those ranges back to timeslot #s, at runtime, for display/reporting
purposes. If the employee had disjoint time ranges (say 8:00-9:00 and
10:00-10:30), he would just have several 'time range" records for the day
and zone in question.

Personally I would be tempted to prototype the "time range" idea. But you
would need to keep a careful eye out, for unwanted coding complexity. The
advantage of the time slot method is that the coding would be very simple.

HTH,
TC

- Any employee (say Fred Smith) can select any one of the 32 zones (without
restriction).

----- Only Fred Smith (the boss) or an administrator would be able to

grant or restrict access to any one of the 32 Zones for any employee.

- Then (for the selected zone), he can select any one of the 7 days of the
week (without restriction).

----- Yes

- Then (for the selected day), he can select any one of the 96 time slots
(without restriction).

----- Yes

- Then (for the selected time slot), he can say "ALLOW access!", or "DENY
access!", by ticking a checkbox, or whatever.

----- Yes by ticking a checkbox

Questions:

- Does Fred need to nominate who or what he is allowing or denying access

----- Fred Smith would do this for each employee he wanted to grant access

to a Zone(for a certain time slot). If he did not specifically grant access
for a particular employee to a particular zone for a particular time slot
they would not be granted access for that time slot.

 

[Merkling, Steven]

Yes I agree that the coding for the "time range" idea could get very ugly very quickly.
Especially when someone needs access from 800AM to 1000AM then from 200PM to 300PM then from 8PM to 1200PM etc.
It could get pretty hard to make that user friendly!!!

I think I am going to go with the time slot method. For several reasons.
1) there more than likely will be multiple people maintaining this application.
2) It should be easier to develop a GUI for the time slot method.

Also I have a little advantage in that there will "never" (I hate to use that word in database design however I think it is warranted here.) be more than 96 time slots per day for each zone.
No one here can imagine more than 48 time slots per day for each zone so if I design it for 96 it should be well within the spec. I know this ties the application to 96 time slots but I think that is OK because I also need to provide a GUI for this thing and I don't want to make the GUI so complicated it can't be used.

so what I am thinking is very close to your recommendation.

tblZone - 1 row per zone.
ZoneID (pk)
name, description, whatever

tblDayNames - 1 row per generic day-of-week.
DayID (pk)
name (e.g. "Tuesday")

tblTimeslot - 1 row per timeslot.
TimeslotID (pk)
start time
end time

tblEmployee
EmpID (pk)
name, date of birth etc.

tblAccess
ZoneAccessID (pk)
ZoneID
DayID
EmpID
TimeSlot1Access (yes/no)
TimeSlot2Access (yes/no)
TimeSlot3Access (yes/no)
|
|
\ | /
\|/
TimeSlot94Access (yes/no)
TimeSlot95Access (yes/no)
TimeSlot96Access (yes/no)

Thanks for your help on this

Also feel free to comment on what I described above.

-Merk
Ok. This will need careful handling to ensure that you get reasonable data
storage (not 50 gazillion records per person), AND, a workable user
interface.

Let's start by designing the tables on the asumption that >everything< will
be stored in a table. Then we'll see what that gives us, & whether it will
be workable. This is what you'd get, with that approach. (Note: the names of
the non-key fields in each table are just descriptive. You would want to
choose names that are not Access reserved words.)

tblZone - 1 row per zone.
ZoneID (pk)
name, description, whatever

tblDayNames - 1 row per generic day-of-week.
DayID (pk)
name (eg. "Tuesday")

tblTimeslot - 1 row per timeslot.
TimeslotID (pk)
start time
end time

tblEmployee
EmpID (pk)
name, date of birth etc.

Now we need a table defining which employees can access which timeslots on
which days in which zones:

tblAccess
ZoneID ( com-
DayID posite
TimeslotID primary
EmpID key )

If you found that 4-field composite key unweildy, you could change it to a
unique index, & add an autonumber as the primary key.)

The presence of a record in that table, shows that the specified employee
does have access to that timeslot, on that day, in that zone. The absence of
a particular record, shows he does not have that access.

As you rightly pointed out, this will be a lot of records! (You said
2,318,400 for 100 employees). However, 2+ million records is well within
Access'es capabilitiues (as long as everything is PK-d properly), >and<,
your calculation assumed that each employee has a record for each timeslot
in each day in each zone. If you only stored the "yes, he has access!"
records - as shown above - this would cut that figure down quite a bit.

On reflection, I really can't see any way to cut the records down much
further. Certainly you could eliminate the tblDayNames and tblTimeslots
tables - and I would probably do that - but this saves you a negiligble # of
rows in the scheme of things. You still have the same # of rows in
tblAccess.

The only other way I can see, is to store time >ranges<, and cauclate time

slots< at runtime. So if an employee had access to timeslots 1 (8:00-8:15

or whatever), 2 (8:16-8:30), 3 (8:31-8:45) and 4 (8:46-9:00), perhaps you
could store just 1 record for that employee, showing that he had access from
start-time 8:00 to end-time 9:00? Then for forms & reports could convert
those ranges back to timeslot #s, at runtime, for display/reporting
purposes. If the employee had disjoint time ranges (say 8:00-9:00 and
10:00-10:30), he would just have several 'time range" records for the day
and zone in question.

Personally I would be tempted to prototype the "time range" idea. But you
would need to keep a careful eye out, for unwanted coding complexity. The
advantage of the time slot method is that the coding would be very simple.

HTH,
TC

- Any employee (say Fred Smith) can select any one of the 32 zones (without
restriction).

----- Only Fred Smith (the boss) or an administrator would be able to

grant or restrict access to any one of the 32 Zones for any employee.

- Then (for the selected zone), he can select any one of the 7 days of the
week (without restriction).

----- Yes

- Then (for the selected day), he can select any one of the 96 time slots
(without restriction).

----- Yes

- Then (for the selected time slot), he can say "ALLOW access!", or "DENY
access!", by ticking a checkbox, or whatever.

----- Yes by ticking a checkbox

Questions:

- Does Fred need to nominate who or what he is allowing or denying access

----- Fred Smith would do this for each employee he wanted to grant access

to a Zone(for a certain time slot). If he did not specifically grant access
for a particular employee to a particular zone for a particular time slot
they would not be granted access for that time slot.

 

[TC]

Yes I agree that the coding for the "time range" idea could get very ugly very quickly.
Especially when someone needs access from 800AM to 1000AM then from 200PM

to 300PM then from 8PM to 1200PM etc.

It could get pretty hard to make that user friendly!!!

No, I am saying that you could >store< the data like that (to minimize the
number of records), but still display & enter it in timeslot fashion. The
user interface would convert from one to the other, as required.

I think I am going to go with the time slot method. For several reasons.
1) there more than likely will be multiple people maintaining this application.
2) It should be easier to develop a GUI for the time slot method.

Certainly the GUI would be simpler >to program< if the underlying data was
also in timeslot format.

Also I have a little advantage in that there will "never" (I hate to use

that word in database design however I think it is warranted here.) be more
than 96 time slots per day for each zone.

No one here can imagine more than 48 time slots per day for each zone so

if I design it for 96 it should be well within the spec. I know this ties
the application to 96 time slots but I think that is OK because I also need
to provide a GUI for this thing and I don't want to make the GUI so
complicated it can't be used.

Maybe put the "magic number" 96 in a global constant, and write your code so
that it will work with any (reasonable) number in there. Then, you could
change that easily, down the track.

so what I am thinking is very close to your recommendation.

tblZone - 1 row per zone.
ZoneID (pk)
name, description, whatever

tblDayNames - 1 row per generic day-of-week.
DayID (pk)
name (e.g. "Tuesday")

tblTimeslot - 1 row per timeslot.
TimeslotID (pk)
start time
end time

tblEmployee
EmpID (pk)
name, date of birth etc.

tblAccess
ZoneAccessID (pk)
ZoneID
DayID
EmpID
TimeSlot1Access (yes/no)
TimeSlot2Access (yes/no)
TimeSlot3Access (yes/no)
|
|
\ | /
\|/
TimeSlot94Access (yes/no)
TimeSlot95Access (yes/no)
TimeSlot96Access (yes/no)

In theory<, each timeslot should be in a seperate table, with a one-to-many

from tblAccess to that new table. >In practice<, this might be a reasonable
candidate for denormalization, as you have done above. However, it would
definitely make it more difficult to make the number of timeslots
configurable via a global variable.

Personally, I would mock-up a basic UI, using the tables above. But you'll
need to be very careful to monitor the effects of the denormalization of the
timeslots into tblAccess.

Cheers,
TC

 

[Merkling, Steven]

You make a good point. I think I should stay away from any denormalization if there is not a specific need to denormalize.

One other "addition" that may come up here is to be able to track who had access to what zone in the past. ie if Employee1 had access to Zone 1 in Jan then was taken off the list in Feb we would like to track when they were denied or approved for access.

Should I just add 2 columns in the tblAccess table like this or add another table?

tblAccess
ZoneAccessID (pk)
ZoneID
DayID
EmpID
TimeslotID
AccessGrantedDate
AccessDeniedDate

Thanks
Merk

Yes I agree that the coding for the "time range" idea could get very ugly very quickly.
Especially when someone needs access from 800AM to 1000AM then from 200PM

to 300PM then from 8PM to 1200PM etc.

It could get pretty hard to make that user friendly!!!

No, I am saying that you could >store< the data like that (to minimize the
number of records), but still display & enter it in timeslot fashion. The
user interface would convert from one to the other, as required.

I think I am going to go with the time slot method. For several reasons.
1) there more than likely will be multiple people maintaining this application.
2) It should be easier to develop a GUI for the time slot method.

Certainly the GUI would be simpler >to program< if the underlying data was
also in timeslot format.

Also I have a little advantage in that there will "never" (I hate to use

that word in database design however I think it is warranted here.) be more
than 96 time slots per day for each zone.

No one here can imagine more than 48 time slots per day for each zone so

if I design it for 96 it should be well within the spec. I know this ties
the application to 96 time slots but I think that is OK because I also need
to provide a GUI for this thing and I don't want to make the GUI so
complicated it can't be used.

Maybe put the "magic number" 96 in a global constant, and write your code so
that it will work with any (reasonable) number in there. Then, you could
change that easily, down the track.

so what I am thinking is very close to your recommendation.

tblZone - 1 row per zone.
ZoneID (pk)
name, description, whatever

tblDayNames - 1 row per generic day-of-week.
DayID (pk)
name (e.g. "Tuesday")

tblTimeslot - 1 row per timeslot.
TimeslotID (pk)
start time
end time

tblEmployee
EmpID (pk)
name, date of birth etc.

tblAccess
ZoneAccessID (pk)
ZoneID
DayID
EmpID
TimeSlot1Access (yes/no)
TimeSlot2Access (yes/no)
TimeSlot3Access (yes/no)
|
|
\ | /
\|/
TimeSlot94Access (yes/no)
TimeSlot95Access (yes/no)
TimeSlot96Access (yes/no)

In theory<, each timeslot should be in a seperate table, with a one-to-many

from tblAccess to that new table. >In practice<, this might be a reasonable
candidate for denormalization, as you have done above. However, it would
definitely make it more difficult to make the number of timeslots
configurable via a global variable.

Personally, I would mock-up a basic UI, using the tables above. But you'll
need to be very careful to monitor the effects of the denormalization of the
timeslots into tblAccess.

Cheers,
TC

 

[TC]

I'm not necessarily saying to definitely NOT use the denormalization that
you proposed. It might make data entry easier. Perhaps draft-out both
approaches?

If you need to track access to who had what in the past, the two extra
fields might be ok. Make sure you can handle the case where a particular
person/zone/day/timeslot access was granted today, revoked tomorrow, >then
re-granted the day afterwards<, rovoked the day after, & so on.

The general design principle, is this. Say (as a general example) that you
only store the current department for each person:

tblPerson
PersonID (PK)
DeptID (FK to Departments table)

If now, you need to save the complete histroy of each person's departments,
the change is:

tblPerson
PersonID (PK)

tblPersonDepartment
PersonID ( composite )
DeptID ( primary )
DateFrom ( key )
DateTo

HTH,
TC

You make a good point. I think I should stay away from any denormalization

if there is not a specific need to denormalize.

One other "addition" that may come up here is to be able to track who had

access to what zone in the past. ie if Employee1 had access to Zone 1 in Jan
then was taken off the list in Feb we would like to track when they were
denied or approved for access.