Table with relationship to multiple tables

Question

I want to persist contact details for persons. This contact details can belong to a client, or a next of kin member, or whatever other entity. I'll be using Doctrine2 in a Symfony project, that will handle the data integrity.

It is a good practice to have such contact table which stores contacts for any kind of 'parent' entity or should a I have different tables like client_contact & next of kin?

Answer 1

Your current diagram is great as long as everyone only has one address, and a max of two phone numbers (apart from a fax).

One simple way this schema falls apart is if you have a client with a street address for their location, but a PO Box for their mailing address. This is a fairly common setup for rural communities in the United States. When I lived in Jackson, Wyoming I had a street address which had a different zip code from my PO Box. In addition my work address was in another city. The core point is people can easily have more than one address, which conflicts with your schema.

Personally I would break apart the address from the phone number. This gives you more freedom over your data. Entities can have addresses without phone numbers and phone numbers without addresses without risking a foreign key to a row full of nulls. Furthermore I would add a foreign key to the address and phone number tables that is just a link to a description. That way you don't need to remember "PhoneNumber2 is always a cell phone, unless this is a business in which case it is the point of contact's direct line" or other such nonsense. The foreign key to the description table will make it clear that Client X has two home landlines and no cell phones. You gain flexibility and clarity.

Overall, I still would keep one address and one phone number table that the various entities would link to via foreign keys. There is no need to duplicate schema. Keep in mind it might be advantageous to have the foreign key to the address/phone table on the entity for some entities and other entities might use a join table instead. That can be a per entity judgment call which can be adjusted over time with relatively little pain.

Also you might want to associate the "next_of_kin" table with someone (your clients?), because it seems kind of weird for them not to have kin... :)

Answer 2

I want to persist contact details for persons.

Since persons could include both staff and clients, the natural step would be to have a single table of contacts for all persons. As oNare suggested in a comment above, just create a contactType field in which you can assign either person type. This solution has the benefit of adding in future, if a need arises, any new person types or subtypes.

The only reason you should have clientContact and staffContact in separate tables is some compelling business reason that suggests those contacts have to be isolated from each other for security, data compatibility, access rights, data entry, and so forth.

EDITS

I still don't see the benefit of contactType, as any entry in 'contact' will be referenced by a FK either in 'staff' or 'client'.

Which table has the 'staff'? If 'staff' and 'client' are in the same table, sure there is no benefit as long as you have another way of distinguishing the two. Your schema does not show 'staff'.

If a client or staff entry is removed, its contact will be deleted using the FK.

That can be controlled depending on the database design and maintenance. Look into enabling/disabling cascading deletes whether you want to keep such records or not. Depends on your apps requirements.

Answer 3

What you have in the image is great. It's a place to put contact information for both of those other entities. I don't even understand why you'd need to have a contact type... You don't want to create two tables that contain the same columns under almost any circumstances.