Design Issue

[Guest]

I am sure I am looking too deep into this but I have the following data and
can not figure out how to oraganize it so I don't save duplicate data.
This is for Key Traking.
The fields I have are.
Positon/Location (Job Title or Area)
Discontinued (Positon/Location no longer used)
Name (Staff Name)
Ring # (Key Ring #)
#of Keys ( # of Keys on Ring)
Storage (Storage Location)
Discontued (Storage Location no Longer Used)
Key # (Key #)
Tag # (Storage Tag of Key #)
Description (Discription of what Key Does)
Disconituned (If it has been removed from use)

As for right now I have the follwoing:
tblPositionLocation
PLID (Auto Number)
Positon\Location (Text) - No Duplicates
Discontinued (Yes\No)

tblStorage
STID (Auto Number)
Storage Area (Text)
Discontinued (Yes\No)

tblKey
Key# (Text)
Tag# (Text)
Description (Text)
Discontinuded (Yes\No)

tblUser
Name (Text)
Ring# (Number)
#ofKeys (Number)

This is how the data is stored:
Pos/Loc - Name - Ring # - # on - Key# - Tag# - Description
- Storage
Engineer - John Smith - 100 - 8 - A156 - 65 - 1.554 Door
Key - MW Key
Engineer - John Smith - 100 - 8 - A223 - 82 - 1.663 Door
Key - MW Key
Engineer - John Smith - 100 - 8 - B159 - 33 - 2.233 Door
Key - MW Key
Engineer - John Smith - 100 - 8 - C156 - 58 - File Key
- MW Key Engineer - John Smith - 100 - 8 - 12589 - 26 -
Fire Box Key - MW Key
Engineer - John Smith - 100 - 8 - A758 - 88 - 6.221 Door
Key - MW Key
Engineer - John Smith - 100 - 8 - A233 - 10 - 1.112 Door
Key - MW Key
Engineer - John Smith - 100 - 8 - Z662 - 92 - 1.113 Door
Key - MW Key

You can have more than one name assocatied to an Positon\Location however
only one Name can be assiged to a Ring#(No Duplcate Ring#'s) However a person
can have more than one Ring3 assigned to them. The Key# - Tag# - Description
shoudl only be entered once for each key however a key can be assigned to
more than one Ring#. Storage only needs listed once however you can have
more than one thing in that storage area. (I think I may have just confued
myself even more)

This is what I would like the form to look like
Postion/Location (Drop Down)
Engineer
Name (You Enter) Ring # (Drop Down) # on Ring (Auto Fill)
John Smith 100 8
Storage Location (Drop Down)
MW Key Box
Then sub from with Key Info ( You enter the Key # and it auto fills Tag &
Descripton)
Key# Tag# Description
A156 - 65 - 1.554 Door Key
A223 - 82 - 1.663 Door Key
B159 - 33 - 2.233 Door Key
C156 - 58 - File Key
12589 - 26 - Fire Box Key
A758 - 88 - 6.221 Door Key
A233 - 10 - 1.112 Door Key
Z662 - 92 - 1.113 Door Key

 

[Guest]

Since no-one has come to your aid can you list the names of the tables with
their primary key and foreign key (PK / FK) ONLY (just to keep things simple).

 

[Guest]

tblPositionLocation
PLID - PK
Positon\Location
Discontinued

tblStorage
STID - PK
Storage Area
Discontinued

tblKey
Key# - PK
Tag#
Description
Discontinuded

tblUser
UserID - PK
Name
Ring#
#ofKeys

 

[Guest]

I assume your tables are related? Shouldn't there be foreign keys (FK) ??

based on the description:

Each location can have many names
Each name can have many rings
Each ring can have many keys
Each key can be on many rings (does this mean you have multiple copies of
the same key?)

Where does storage come into it?


What about storage?

 

[Guest]

Storage is the location of where the keys are placed at the end of the work
day.
Yes we have mulitple copies of each key.
sorry in tblName I made a few changes to and sent you the wrong info.
tblName looks like this:
PLID
Name
Ring# - PK
#OfKeys
Key#
Tag#
Description
STID

Rleationships

tblKey - Key# - tblName - Key#
tblPositionLocation - PLID - tblName - PLID
tblStorage - STID - tblName - STID

 

[Guest]

Maybe you should have a table which lists each key and its lock, and have a
"many" table for the copies and their storage location (assuming each copy of
a key can be stored in a different location).

There isn't any point in each ring having multiple copies of the same key so
it would be 1-to-many relationship.

Location -> names -> rings -> copies <- key

something to think about.

 

[Guest]

They key can be on many rings but there is never two of the same on each ring.
The ring is assigned a storage location not an individual key.
I have a set of keys ring # 109 It has 18 keys and is stored in the MW.
Now about 5 of those keys are also assigned to other key rings but the
Description of each Key and Tag # of each key never changes.
I have 1059 the Restroom Key but so does all 500 + staff that have key rings.

 

[Guest]

Hello Michelle,

I see you started your post yesterday, so i don't know if you are still
checking it but hope this helps. It looks like you are on the right track,
but here are a few things to consider;

First, I think your Ring#'s should have their own table. This way you could
assign key#'s from the key table to each ring in the ring table. Then you
assign ring#'s to names in the Users table. That way, if an employee leaves
you can just reassign that ring# (and all the associated keys) to another
employee.

Second, since a ring can have many keys#'s and a key# can be on many rings
you have a many to many relationship, This means that you will need a third
table to complete the relationship. Something like;

tblRK
RKID (PK)
RingID (FK from the Ring table)
Key# (FK from the Key table)

Third, you really don't need a field for for # of keys in the users table.
This value can easily be calculated based on the keys that are assigned to
that person's ring(s). You would do the calculation in a query or in an
unbound field on a form. It's not a good idea to store calculated values in a
table.

So,you should have;

A one to many relationship between the tblPosition and TblUser.

A one to many relationship between tblUser and tblRings.

A one to many relationship between tblStorage and tblRings.

A many to many relationship between tblRings and tblKeys, with the
relationship defined by tblRK (or whatever you want to call it).

HTH
Post back if you have more questions.

 

[BruceM]

Aside from the other good points that have been posted so far about a
many-to-many relationship between keys (key numbers, that is) and rings, and
about the relationships in general, an observation about design is that a
table should contain information about a single entity such as Person. A
person's attributes include FirstName, LastName, EmployeeID, and so forth.
If each employee is issued a single ring you could include the ring number
along with other employee information, but the specific keys belong in a
separate related table.
If you have relationships between the tables, it is not apparent from what
you have posted, as has been pointed out elsewhere in this thread. It may
be about how you have conveyed the information, but it could be that some
design issues have missed the mark. Check this page, particularly Table
Design in Tips for Casual Users. Also, check out the Links link on the
right side of the page:
http://allenbrowne.com/tips.html

 

[Guest]

How are you doing?

Sounds horrible.

They key can be on many rings but there is never two of the same on each ring.
The ring is assigned a storage location not an individual key.
I have a set of keys ring # 109 It has 18 keys and is stored in the MW.
Now about 5 of those keys are also assigned to other key rings but the
Description of each Key and Tag # of each key never changes.
I have 1059 the Restroom Key but so does all 500 + staff that have key rings.