Referential Integrity and Unique Id's

[Kerensky18]

I am currently a student earning a certificate in database management and as
a side project I'm designing a database for a local convention that I am a
volunteer at. The database is to track radio sign outs for volunteer security
personal. Currently I've three tables, one to keep track of all the staff
each one has a unique badge ID, the second is a radio table which has 1 field
for radio numbers 1-100, the third is an intersection entity with the
following fields: Staff number, Radio Number, Date-Out, Time-Out, Date-In,
Time-In. My relationships are from Staff Number under Staff table to Staff
number in the intersection field and from Radio number to Radio Number. They
are both 1-many relationships with integrity enforced.

My question is can I maintain the radio number being unique for each
instance so 1 radio can't be signed out to two people? Currently it will only
allow 1 radio number period. My fear is if I take out the unique required key
than some of the people may not sign radios back in and we will have one
radio signed out to multiple people. Any help with this issue would be
greatly appriceated.

 

[Jamie Collins]

I am currently a student earning a certificate in database management and as
a side project I'm designing a database for a local convention that I am a
volunteer at. The database is to track radio sign outs for volunteer security
personal. Currently I've three tables, one to keep track of all the staff
each one has a unique badge ID, the second is a radio table which has 1 field
for radio numbers 1-100, the third is an intersection entity with the
following fields: Staff number, Radio Number, Date-Out, Time-Out, Date-In,
Time-In. My relationships are from Staff Number under Staff table to Staff
number in the intersection field and from Radio number to Radio Number. They
are both 1-many relationships with integrity enforced.

My question is can I maintain the radio number being unique for each
instance so 1 radio can't be signed out to two people?

The design you allude to is called a valid-time state table in the
literature (e.g. see: http://en.wikipedia.org/wiki/Temporal_database).
It is tricky to implement in SQL because it requires sequenced
constraints e.g. to prevent a given radio being signed out to two
people you would need a constraint to prevent overlapping periods...
Cut to the chase: this requires table-level CHECK constraints to be
able to implement such a design in Jet. See a previous (long) post,
with worked example in Jet, on the subject:

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

BTW you have probably made more work for yourself by having separate
columns for both 'time' and 'date'; you'll be forever joining them
again. Anyhow, Jet has but one temporal data type named DATETIME (the
clue's in the name <g>).

Jamie.

--

 

[Kerensky18]

Thank you for your help. Reading those two articles has given me some more
options though to implement them I'm going to need to learn access more. This
is only the second week of the class dealing with access. Last quarter was
focused on relationship charts.

 

[Jamie Collins]

Thank you for your help. Reading those two articles has given me some more
options though to implement them

I strongly recommend the Snodgrass book referenced in the Wikipedia
article and can be downloaded for free in pdf format from
http://www.cs.arizona.edu/people/rts/tdbbook.pdf.

Jamie.

--

 

[Pat Hartman]

Technically the relationship between people and radios is 1-m. ie a radio
can be signed out to only one person at one time. The issue arises if you
need to maintain a history of who has had custody of which radios at which
time. If you don't need the history, get rid of the junction table and put
the check in/out fields in the radio table. This way, you can implement the
1-m with the out/in fields in the radio table and not need the junction
table at all. You can tell if a radio is available by testing the in/out
dates. A radio is available if both dates are null or both dates are
populated. When you check out a radio, you place the badgeID in the radio
table, the check out date/time, and null out the check in date/time. You
can then implement the history table as just that - a history table and you
won't need to worry about the radioID occurring more than once. For
purposes of history, I would log the transaction in history at check in time
when it is complete rather than logging it at check out and then having two
places to update when the ratio is returned.

 

[Jamie Collins]

Technically the relationship between people and radios is 1-m. ie a radio
can be signed out to only one person at one time. The issue arises if you
need to maintain a history of who has had custody of which radios at which
time.

You
can then implement the history table as just that - a history table and you
won't need to worry about the radioID occurring more than once.

IIRC this design is called 'bitemporal' in the literature i.e. one
table is for the current state and the other shows the history not bot
the current state. The history table still needs a sequenced primary
key constraint plus you also need a 'distributed' foreign key to
ensure the current state isn't in the history table, to avoid having
to maintain two tables in sync. My view is that the absence of table
triggers and the fact that SQL stored procs can only execute a single
statement in Jet makes this design impractical: table constraints
become so tight that you have to DROP and CREATE them on the fly
(resulting in schema locks on the table -- ouch) or omit the
constraints are leave things wide open to data integrity corruption.
Rather, I think the single valid-state table design is just about do-
able in Jet, assuming that updates other than in the current state can
be done with the database off-line.

Jamie.

--

 

[Kerensky18]

IIRC this design is called 'bitemporal' in the literature i.e. one
table is for the current state and the other shows the history not bot
the current state. The history table still needs a sequenced primary
key constraint plus you also need a 'distributed' foreign key to
ensure the current state isn't in the history table, to avoid having
to maintain two tables in sync. My view is that the absence of table
triggers and the fact that SQL stored procs can only execute a single
statement in Jet makes this design impractical: table constraints
become so tight that you have to DROP and CREATE them on the fly
(resulting in schema locks on the table -- ouch) or omit the
constraints are leave things wide open to data integrity corruption.
Rather, I think the single valid-state table design is just about do-
able in Jet, assuming that updates other than in the current state can
be done with the database off-line.

Jamie.

I do need to pull the history of who had which radios when in case a radio
is found to be damaged and so I can archive the sign outs. I appreciate the
help that you have given.