Modeling users who are also experts

[elsenorjose]

I am attempting to model two groups of overlapping entities for a small helpdesk/issues logging application. On the one hand, I have users who will make requests of subject matter experts. On the other, I have subject matter experts who are also users. I don't want to use a recursive referential model for obvious reasons (what happens if Jane Doe quits and it takes months to replace her, no history if roles change, etc.) but I want the user id to match the SME id so I can create queries where userid=smeid. How do I model this in Access so that the user IDs match the SME IDs? In the example below I have 4 users but only 3 SMEs. John Smith is not a SME but Joe Smith is. Since I don't know necessarily in what order these tables might be updated (I might add 3 users in a week but only 1 SME) how do I resolve the problem of a userid not matching a SMEID? Does any of this make sense? In this example, Joe Smith's user id does not match his SME ID so I can't query for issues in which Joe was the user making the request and also the SME assigned to it.

tblUser
UserID Name
001 John Doe
002 Jane Doe
003 John Smith
004 Joe Smith
... ...

tblSME
SME ID Name
001 John Doe
002 Jane Doe
003 Joe Smith
... ...

 

[alvechurchdata]

If it's possibe I'd advise you to merge these tables and generate a single "Person" table which has two extra columns named "User" and "SME" so that you can record which role or roles each person is filling.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[elsenorjose]

That's what I'm trying to avoid. The problem with that design is it doesn't reflect real life very well. For instance, what if Jane Doe quits, and it takes 2 months to replace her? I can't delete her record, (ON DELETE RESTRICT is required here. ON DELETE CASCADE would be a terrible mistake, because deleting one employee record could cause many records to be accidentally removed.) so I have to go through some strange manipulations to keep the integrity of the table. This structure also doesn't allow historical information to be tracked about previous roles. I recall seeing somewhere a transition table or something like that in which I use an intermediate table (UserRole) and join the keys that way but it's been a while and I can't recall the exact modeling theory.

 

[elsenorjose]

Ok, I figured out what I need to do, I just need help doing it. I need to set up a referential integrity check. Here's my thought process:

A user CAN BE a SME but a SME MUST be a user before s/he can be a SME. If I try to add a SME with no corresponding user record, my application should update the user table and set the SME ID to the userID just created. If I add a SME that has a corresponding user record, my application should set my SME ID to = the corresponding userID. I can add users to my heart's content because there is no rule that says a user MUST be a SME but anytime I add a SME, I MUST check for a corresponding user record to set my SME ID. How do you create triggers in Access? That's what I need isn't it? Thanks.

 

[PHV]

How do you create triggers in Access?
You may consider the BeforeInsert, AfterInsert, BeforeUpdate and/or AfterUpdate event procedures of the Form object.
There is no way I know to implement triggers at the Table level in a Jet database.
You may consider using MSDE for that purpose.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alvechurchdata]

The problem with that design is it doesn't reflect real life very well

Surely Jane Doe is a person and representing her with a single record is a better representation than using two records and having to write code to maintain consistency between them.

For instance, what if Jane Doe quits, and it takes 2 months to replace her?

Clear both the "User" and "SME" flags so that she's marked as inactive but you've still got the reference to all child records.



Geoff Franklin

http://www.alvechurchdata.co.uk

 

[stevexff]

It's the role that's important here. You only need one 'party' record for each person. Have a role table, with role id, party id, role type, start date, and a nullable end date. That way people can have multiple roles, and because of the start date and end date, you can terminate a role without impacting another. You can even handle people leaving and rejoining (maybe after maternity leave). It also give you a history of roles.