design question

[RipperT]

A design question:

I have a database listing (among other things) inmate bunks in a prison.

1-102B

where 1 is the housing unit number, 102 is the cell number and B is the bunk
(B for bottom, T for top)
Design rules suggest that I use a separate table for housing units (8 of
them), a separate table for cell number (120 in most units) and perhaps
another for bunk. I would like each in a drop down list from which the user
will select the appropiate housing unit number, cell number and bunk for the
given inmate.

Problem: some units have no top bunks, but they may be added later. Some
units have top bunks but no inmates lock there for security reasons, but this
may change later. And unit 8 is set up differently with just 36 cells and no
top bunks. What's the best way to remain flexible? Is there a way to have a
different drop down list of cells be available based on which housing unit
the user selects first?

Also, no two inmates can be assigned the same bunk (for obvious reasons).
How can I ensure this doesn't happen if using multiple tables?

Thanx,

Rip

 

[Tim Ferguson]

Design rules suggest that I use a separate table for housing units (8
of them), a separate table for cell number (120 in most units) and
perhaps another for bunk.

I don't think they do, you know. The rules do suggest that you should
have separate _fields_ for them, though. Something like this:

Table: Bunks (The PK is indicated by double underlining)

Unit CellNum Bunk NumOfBlankets HasDryingMechanism
==== ======= ==== ------------- ------------------
1 12 T 3 No
1 12 B 1 Yes
...
8 35 S 7 NULL

Problem: some units have no top bunks, but they may be added later.
Some units have top bunks but no inmates lock there for security
reasons, but this may change later. And unit 8 is set up differently
with just 36 cells and no top bunks.

Well, that is taken care of by the table above: you can add and remove
bunks as needed, or mark them unserviceable (if a cell is being renovated
or mended, etc).

The new table is the one called Allocations: it has a joint PK of (Unit,
CellNum, Bunk, PrisonerNumber), with FKs into the Bunks table and the
Prisoners table. The easy way is to keep track of current Allocations
only, archiving records into a History table when inmates are moved out.
The slightly harder way is to have one table with all Allocations, and to
keep note of the dates they move in and move out. This is harder in
Access because you don't have triggers to monitor for double-occupancy,
but it can be done.

Is there a way to have a different drop down list of cells
be available based on which housing unit the user selects first?

Get the tables and schema design right first, before even _thinking_
about UI controls like lists and text boxes! In this particular case,
it'd be very easy to base a list box RowSource on something like

SELECT Unit & "-" & CellNum & "-" & Bunk AS Expr1
FROM Bunks
ORDER BY 1;

and they would all appear in an easy order. But that is up to your users
and, anyway, it's a long way down the road yet.

HTH


Tim F

 

[John Vinson]

A design question:

I have a database listing (among other things) inmate bunks in a prison.

1-102B

where 1 is the housing unit number, 102 is the cell number and B is the bunk
(B for bottom, T for top)
Design rules suggest that I use a separate table for housing units (8 of
them), a separate table for cell number (120 in most units) and perhaps
another for bunk. I would like each in a drop down list from which the user
will select the appropiate housing unit number, cell number and bunk for the
given inmate.

Problem: some units have no top bunks, but they may be added later. Some
units have top bunks but no inmates lock there for security reasons, but this
may change later. And unit 8 is set up differently with just 36 cells and no
top bunks. What's the best way to remain flexible? Is there a way to have a
different drop down list of cells be available based on which housing unit
the user selects first?

Also, no two inmates can be assigned the same bunk (for obvious reasons).
How can I ensure this doesn't happen if using multiple tables?

You really need only one table, or perhaps two. A table of Units would
be worthwhile, and a table of Bunks with three fields - Unit, BunkNo,
and Bunk. Or, you might just have a text field for the bunk number -
"102T" and "102B" as values. Your bunk-assignment table should have
InmateID, Unit, BunkNo (and Bunk) fields; you can put a unique Index
on the combination of Unit, BunkNo, and Bunk to keep from assigning
two inmates to the same bunk.

You can in fact make the Bunk combo box specific to the Unit; see
http://www.mvps.org/access/forms/frm0028.htm for an example.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tim Ferguson]

Or, you might just have a text field for the bunk number -
"102T" and "102B" as values.

An intelligent key, John? For shame! <vbg>


B Wishes


Tim F