Relationships

[PhillipsAndrew]

One to one relationships..

Using some of the examples on this site, I've created a many to many relationship using a junction table.. It works very well.

One of the tables (tbl_PersonInfo) contains person info (PersID (PK), PersName etc)
One table contains departments (tbl_Departments) that person is associated with (can be more than one).
The junction table links these together.

My problem is that I want to use the Person Info table to relate to another table which contains information on privileges (tbl_Privileges) (what they can order etc... so it contains lots of yes/no fields). I've created a relationship (one to one) between tbl_PersonInfo and tbl_Privileges based on PersID (PK).

tbl_PersonInfo
PersID (PK)
Name

tbl_Departments
DeptID(PK)
DepartmentName

tbl_Junction
PersID
DeptID

tbl_privileges
PersID
CanOrderOver1000 (yes/No)
CanOrderOver5000 (Yes/No)



My problem is that as I enter new people (and their departments) onto the database using a form (based on query between tbl_PersonInfo and tbl_Departments), how can I get it to automatically put the PersID into tbl_Privileges?

I don't want to do this from the same form but want to do it from a separate form later on....

Does any of this make sense? I'm sure it's something simple but would appreciate any help. It seems to me that I'll use the personnel info in lots of other places too, how does it automatically update its relationship?

Thanks,
P

 

[jeremie_ingram]

Depending on what exactly you have in the tbls, most 1-1 relationships indicate that you have decomposed the table structure 1 step too far. Can you include these within the same tbl? If the example you gave is a good indication of what the tbl_privileges will hold, why not create a field the allows for selection of the upper limit only? (i.e. 1000, 5000, 10000)
If you will refrence these levels of permissions from various tables, then I would create one table to list them all. If this is the only table that uses the refrence, then you could create a look-up field and enter each possible selection instead of having it refrence a tbl. Having it refrence another table for the information is better, in the event you may want to change these item in the future.

So the difference would be like

tbl_PersonInfo
PersID (PK)
Name
PrivLevel (look up to tbl_privileges)


tbl_privileges
PrivPkey (autonum) (bound column)
PrivLevels (data would be 1000, 5000, 10000) (displayed column)

As long as the field is not refrenced anywhere else, or set as required, then you could enter this after entering the initial information or even leave it blank.

 

[Pat Hartman]

Rather than implementing privileges as a flattened (non-normalized) structure, why not normalize it and implement it as a many-to-many. Presence of a row in tbl_Assignedprivileges indicates the person has that privilege. The correct implementation also has the advantage of being easily expandable should that be necessary.

tbl_privileges
PrivilegeTypeID
PrivilegeDesc

tbl_Assignedprivileges
PersID
PrivilegeTypeID

 

[PhillipsAndrew]

relationships

Thanks for the replies.... I've changed some things and hopefully can make progress now!

Thanks again,

P