Still Struggling...

[BruceM]

This will probably be my last posting for a while, as I will be away next
week and need to finish up a few things before I go.

Here's the structure, mistakes and all:

tblKeys 1:M tblMasterKeys
KeyID PK (Autonumber)
KeyName
MstrKeyID (FK, number long integer to tblMasterKeys)

I still think the relationship is between keys and locks, not keys and other
keys. A key opens one lock. Ignoring master keys for the moment, you have
One Lock : Many Keys, so LockID is a FK in tblKeys.

A master key opens several locks. One master key can open several locks,
and each lock can be opened by several different keys including the master
key. If there is to be a junction table I think it would be between locks
and keys. Except for the master key this is a one-to-many relationship, but
making it many-to-many even though the first "many" is applied infrequently
is OK. There may be other ways to model this relationship, but this is one
that occurs to me.

tblMasterKeys
MstrKeyID PK (Autonumber)
MstrKeyName

tblKeysEmployees M:M tblEmployees
KeyID (1/2 PK, FK to tblKeys, number, l.i.)
EmpID (1/2 PK, FK to tblEmployees, #, l.i.)
AllowedtoRetain Yes/No
Approvedby
DateIssued Date/Time
DateLost Date/Time
DateRtrnd Date/Time

The question here is how the lock fits into the picture. If you may want to
see a listing of who can open a particular lock you need a way to associate
locks with employees.

tblKeysRequests 1:M tblKeysEmployees
RequestID PK (Autonumber)
KeyID FK, #, l.i. to tblKeysEmployees
QtyRqstd
DateRqstd Date/Time
DateIssued Date/Time
DateIssued (Hmmm...I just noticed that this is the same as
tblKeysEmployees)
Funny how you can gloss right over things.
Comments

tblLocks (I thought this was a junction table but now I don't know)
KeyID PK/FK
LocationID PK/FK

It is not a junction table that I can see. One lock has many keys, not the
other way around (except for master keys). In the case of a master key it
is one of the many keys that can open a lock.

tblLocations 1:M tblLocks
LocationID PK (Autonumber)
LocationTypeID FK to tblLocationTypes
LocationName
Remarks

tblLocationTypes 1:M tblLocations
LocationTypeID PK (Autonumber)
LocationType

tblCampuses 1:M tblLocations
CampusID PK (Autonumber)
CampusName

tblWings 1:M tblLocations
WingsID PK (Autonumber)
WingName

You may be cutting it a bit fine here. I have to disagree with Sean that
all fields need to be filled in. It happens all the time that a Middle
Initial field is not filled in, for instance. For another example,
Apartment Number does not apply if the person lives in a house, but I see no
reason in a database of this scale (i.e. not super large) to separate this
datum into its own table. Find a way to identify where the lock is located.
A lock will be located on a campus, and maybe in a building (unless it is an
outdoors lock to an athletic field gate or something). If a building, it
may have wings, or maybe not. You can use a lookup table to insert a value
into, say, the Wing or Building field in the Locks table. You can even
limit the Wing listing to just the Wings in the selected Building. However,
you may not need to involve these lookup tables into relationships.

So I think tblKeysEmployees defines the relationship between keys and
employees.

Regarding other parts of your previous post:


No, this is not quite true. One key can be assigned to one or many people.


...*another* junction table?! Please...is there any way around this?

This is in place of the KeyEmployee junction table, which I maintain is not
exactly the relationship you should have.

It would be nice to have a list of unassigned keys but...it's a little
overwhelming right now. This just keeps growing and growing. I'm already
onto
page 2 for printing the relationship diagram. Let me live with this for
awhile. I know yesterday I posted, "Oh well, c'est la vie" as far as
junction
tables but today I can't deal with another junction table.

You would use SQL to insert a list into a list box, or maybe into a
continuous form. You do not need another table. The data are already there
(or will be when this is all set up).

BTW:

tblKeys
KeyID (PK)
LockID (FK to tblLocks)
KeyCode (S2-A, etc., or whatever)
AssignedTo (FK to tblEmployees)
AssignedDate
Retired (Yes/No)

I like the field name KeyCode. I'm not so sure about the Retired (Yes/No)
field although that does have to be taken into account. I will probably
end
up doing it that way. Let me think about it some more.

I meant Retired to refer to the key, in case that is not clear. The point
is that if a key is lost or damaged or worn out the Retired field can be
used to exclude it from a listing of available keys.

Let me know if this structure resolves *any* of the issues we have.

Perhaps Sean will have some further insight, and perhaps he and I are not at
cross purposes. I repeat that I think it will be a good thing to take what
you have and start another thread. I would have asked some questions of the
group by now, as I am uncertain in some cases how to model this situation.
Instead I have been stretching my mind and my capabilities trying to figure
this out. It has been good practice, but I am about at the limit of what I
can suggest with confidence.

I will check in again, or search for other threads from you, when I return.
In the meantime, best of luck. I am confident you will get this figured
out, even if you doubt it from time to time.