Kraj actually has a question!

[Kraj]

I've got an unusual table structure issue and I thought I'd get some outside opinions.

I'm putting together a contacts database. Each contact has a record in the Contacts table; each contact also can have multiple addresses, phone numbers, email, etc., so each of those gets their own table. Now for the weird part: some of the contacts have assistants, who in turn have their own set of addresses, phone numbers, etc. Relating the assisstant to the contact is problematic.

If I make a completely different table for assisstants, I'm basically duplicating a table for no real reason other than to differentiate the type of contact. It works, but it feels totally wrong to me.

If I treat assisstants as just another contact, then I run into all sorts of problems. Consider:

CONTACTS (Contact_ID, Last_Name, First_Name, Assisstant, etc.)

The assisstant field would end up referencing Contact_ID, which would mean as far as relationships are concerned two fields in the same record are the same, even though the data would be different. I could completely ignore referential integrity and it should technically work, but that also feels totally wrong to me.

Does anyone have any thoughts/suggestions? Has anyon encountered a situation like this before?

 

[Banana]

I'm no expert and am still learning- I wonder what if you made a third table-

tblAssistant
AssistantKey
Assistant_ID (FK to Contact_ID in Contact table)
Supervisor_ID (FK to Contact_ID in Contact table)

then add a business rule that Assistant_ID cannot ever be same as Supervisor_ID. That would also run contrary to the convention of keeping the key name in various tables and I don't even know if it's a good idea to have two fields in single tables to be referened by a PK in another table. I only thought that a third table would make querying much easier, and with the business rule, there shouldn't be a problem.

If I'm wrong, I hope to learn why!

 

[RuralGuy]

I see nothing wrong with keeping them all in one table. Take for example: Self Joins

 

[KenHigg]

Hum...

How about this:

1. A table for details. Reqardless of whether the record was for a contact or an assistant, the data would go here

2. A table contacts

3. A table for assistants

So, A contact or an assitant record would 1 to 1 to a detail record and contact to assistant tables would be one to many...

Or you could combine the contact and assistant tables and have a fld that would link the two or have a junction table that links the two...

Of course, IMHO, this is a good drill to practice up on normalization but in the real world, for a Access type db it's a bit overkill - Just have a contact table and an assistant table...

Relational dbs / 5th normal form aren't the end all / ultimate solution in the practical world - There is a balance between relational data and replication to be struck... IMHO

 

[Len Boorman]

Within your Contacts table how about a field for Contact Type.

1) Main Contact
2) Assistant

You could then recover Contacts for a contract and determine immediately if they were Assistant or Main Contacts

Len

 

[neileg]

Definitely go down the self join route. You may find eventually that assistants have sub-assistants and the self join model will accomodate that.

 

[Kraj]

Thanks everyone for your input!

I think the self-join will work out perfectly. You never quite know, of course, until you try to make some forms and queries and see if all the links function properly. But at least the software will allow me to create the relationship, and that's a good start. If I have to go another route, you've confirmed my alternate plan is sound and offered some other ideas as well, so I should be good to go no matter what. Thanks again!

FYI Banana - I tried that idea early on just to see if it might work but Access won't let you do it that way. If you try to make a second field reference the same FK, it errors and just says "You've already made that relationship" or something of the sort. Just thought you might like to know.

 

[Banana]

Kraj, thanks for sharing. I'm glad I learned something about self-join.

The funny thing was that I already knew you could add multiple instances of same tables to a query, but I had written it off as a bad way to normalize your data. Now it seems there was a good reason for that and being able to rename the tables is a bonus.

 

[neileg]

The funny thing was that I already knew you could add multiple instances of same tables to a query, but I had written it off as a bad way to normalize your data. Now it seems there was a good reason for that and being able to rename the tables is a bonus.

I have a db that creates pedigrees (like a family tree). The main query uses the same table 31 times!

 

[Pat Hartman]

I can't tell for sure which solution you have chosen so I'm going to reiterate the standard one -
Single table for contacts with self-referencing relationship.
Contact table includes a column that refers to a contact's "parent" if he has one. So to tell the difference between an assistant and a prime contact, the assistant will have a ContactID in the "boss" column and the prime contact's "boss" column will be null.