To delete, or not to delete....

[mike55]

Hi all,

 

If I had a fully normalised database with the following two tables Employees(ID[PK], Name, Access[FK-AdminPrivilages]) and AdminPrivilages(ID[PK], Details)where Access is the foreign key for the column ID in AdminPrivilages.

 

How do I best handle the removal of a row from the AdminPrivilages table without affecting the Employees table?? In the past I would have added an extra row to AdminPrivilages: status, which would be set to 0 or 1 to indication if the row was valid/active(0) or had been deleted(1). However I'm not sure if this is the best approach?

 

Any suggestions on how best to deal with the deleting of data from a normalised database, or how do you handle the delete in such a situation?

 

Mike55

[PlausiblyDamp]

If the AdminPrivilages table is effectively a child relationship to the Employees table then you can delete entries from it without having to worry about the Employees table. If you simply flag a permision as no longer being valid you would also need to code in a system in which every time you assign a new permision you will need to check if it already exists and re-enable it if it does exist or create a new entry if it doesn't - failure to do this will result in lots and lots of redundant entries in the AdminPrivilages table.

[mike55]

Does this not mean that, I am effectively creating gaps in the employees table and breaking the normalisation. Furthermore, will not the database throw exceptions when It sees that I have removed the foreign key data for a particular row or group of rows?

 

Mike55

[PlausiblyDamp]

My bad - I just realised the tables are structured the other way round. You will probably find a better solution involves creating a third table (something like EmployeePrivilages) which ties the other two tables together.

e.g. have two columns EmployeeID and PrivilageID both of which are Foreign key relationships. That way there is no direct requirement for an employee to be modified if a privilege is deleted. Plus you then get the flexibility of allowing multiple privileges per employee if required.

[penfold69]

Yeah, a normalised database with this sort of structure is unusual.

 

You would normally have:

 

Employee_Table:

[EID]

... other employee details ...

 

Permission_Table:

[PID]

.. other permission details ...

 

Employee_Permissions_Table:

[iD]

[EID]

[PID]

 

 

This, as PlausiblyDamp mentions, allows you to have multiple permissions per employee, and multiple employees per permission quite easily.

 

If you delete a "permission", you should delete all rows with a matching [PID] in the "link" table. Also, if you delete an employee, you should delete all rows in the "link" table with a matchine EID - this way the FK constraints all work correctly.

 

B.

[mike55]

Thanks for the help, ye have definately given me something to think about for my database.

 

Mike55