I have a project where there is a main contact DB (hidden), and then a separate DB for each role that contact could be. The separate role DB's are because each role is handled by different departments, which will have their own layouts etc. that need to be separate and tidy from the other departments.

There will be issues when creating new records, because the user will have to first check to see if that contact already exists, but I can deal with that.

What I am curious about is which structure to go with:

1. In the contact table have a foreign key for each role

2. Have a join table called 'role' to link all the separate DB's together.

Note some roles need history, while others do not. The ones that need history I would probably go with the join table, but to be consistant should I just run it all through the join table. One benefit is the join will show full role status of a contact, in any of the separate DB's.

any thoughts?

There is no need to have separate files for departments. Everything can be done with privilege sets for the different roles.

Thanks Vaughan, I thought about that, but separate files is much tidier in this case. The history with this client is whole sections come and go, so to rip a single section out of one master file, is tough. Much easier to turn hosting on that file off.

I'm with Vaughan on this. I don't see how you're going to "turn hosting on that file off." Won't you have those records referenced in the other files?

I don't understand the requirement that each department assigns roles to a client independently and shouldn't see the role that another department assigned to the contact. Typically it's Contact->Assignment<-Role using a join btw Contact and Role. There's no reason that you couldn't have the DeptID in the Assignment join table. If you did, then you'd be able to "see" the roles that a particular dept assigned to the Contact.

As for the issue of creating a new Contact, I usually have the user Find in Contacts before allowing a new contact. You'll see have duplicates (some people are more diligent about finding existing before creating new. You could limit new contact creation to an admin). There's a demo of a popup select technique I've posted here that shows the Find>Select>New functionality.

Why do depts need their own layouts? Perhaps all they need are filtered data? For example, only show contacts in the join table that have "my" dept ID.

Oops, I forgot to mention that the whole system is a data separation model, so turning of the file is not affecting data access, it is just an interface file.

Not so much the departments, but its the roles that need unique fields and layouts. Certain users/dept handle particular roles.

Thanks for the input.