Referential Integrity

[atticus1802]

I have created a large databse for my company detailing employees information as well as desk information. At the moment I have two tables one detailing the employee information, name, address etc, the second detailing desk information, desk number, laptop lock code etc.

These are linked together via a relationship, the employee number is unique and has been tied to both the desk and the employee. The trouble Im having is that not every employee has a desk as they work out the office and not every desk has a user. I cannot enforce referential integrity as the employee number is not valid for every desk as some are kept empty for when employees visit.

This is currently working ok except when employees leave their information is deleted but as referential integrity is not enforced the employee number will still be linked to the desk. If it is enforced the employees information and the desk information are both deleted. What I would like to achieve is that if an employee leaves and their information deleted in the form that the empoyees number will be automatically removed from the desk number without deleting the desk information.

Hope this explains my situation clearly.

 

[HiTechCoach]

I have created a large databse for my company detailing employees information as well as desk information. At the moment I have two tables one detailing the employee information, name, address etc, the second detailing desk information, desk number, laptop lock code etc.

These are linked together via a relationship, the employee number is unique and has been tied to both the desk and the employee. The trouble Im having is that not every employee has a desk as they work out the office and not every desk has a user. I cannot enforce referential integrity as the employee number is not valid for every desk as some are kept empty for when employees visit.

This is currently working ok except when employees leave their information is deleted but as referential integrity is not enforced the employee number will still be linked to the desk. If it is enforced the employees information and the desk information are both deleted. What I would like to achieve is that if an employee leaves and their information deleted in the form that the empoyees number will be automatically removed from the desk number without deleting the desk information.

Hope this explains my situation clearly.

i see a big design issue. I would suggest reading about the rules of data normalization.


Two are just not enough to handle this properly.

What I would recommend is this:

• People table
• Assets Table - one record for each item
• PeoplesAsset table - juction table to link people to assets

This way you have a master list of all people. A master list of all assets (desk, phone, laptop, etc).

The junction table PeoplesAsset will have the fields to link back to both the People and the Assets and define the relationship between them

tblPeopleAssets
- PeopleAssetsID - autonumber primary key
- PeopleID - foreign key to link to the people table
- AssetID - foreign key to link to the asset table
- startdate - when the person was assigned the asset
- enddate - when the person returned the asset - null => then still have it

Hope this helps ...

 

[atticus1802]

Ok Im not an expert in this but I do understand the reasoning a junction table would allow two full tables to be linked together.

If i was to enforce Referential Integrity deleting a record via a delete record button on a form would this delete all linked information i.e the people, assets and peoples assets?

I would prefably like it to delete only the people and clear the link between them. The desk will always have the same assets so would like to quickly be able to assign new staff to desks without reentering any information.

 

[PNGBill]

This sounds like a hotel database where you have rooms and customers.
Customers are either on file (like any cust table) or they have booked in and are in a room.

If the hotel also has serial numbers of say TV's that are in each room then you have your pc's data.

I don't know how this will all work but it does for hotels. They can access the database and find out who is in what room or what room cust X has been assigned to.
When a cust checks out the cust table does not loose a record but the "transaction table" has flagged a record as not current.

 

[HiTechCoach]

Ok Im not an expert in this but I do understand the reasoning a junction table would allow two full tables to be linked together.

If i was to enforce Referential Integrity deleting a record via a delete record button on a form would this delete all linked information i.e the people, assets and peoples assets?

I would prefably like it to delete only the people and clear the link between them. The desk will always have the same assets so would like to quickly be able to assign new staff to desks without reentering any information.

I never delete records. Otherwise you have no historical data to analyze. If you are just going to delete the data, then why take the extra time to enter it into a computer? That is what experience has taught me from years of developing management reports/KPI/marketing/etc. some might not agree.

I would also not delete the links. That is the reason for the start end end dates. So you will have a history.

Example using the table design I suggested:

Reassign a desk.

1) Open the Asset form
2) find the desk
3) On the Asset form use a sun form to display the junction table.
4) in the Sub form select the record for the current person assign the desk and enter the ending date.
5) to assign to a new person, create a new record, select the people from a combo box. Enter the start date.

At most you only data entry might have to type in two dates (or if today use ctril-; )