PostgreSQL Database Design – How to Model Multiple Types of Contacts

database-designpostgresql

I'm not sure if this can be called a polymorphic association – what is needed is to for each Message to have a Sender Contact and a Recipient Contact.
Both Sender Contact and Recipient Contact could be one of 2+ types. Currently SMS and Email are supported but more may come.

I have this urge to eliminate a possible duplication that would appear in case of having two different tables – sender_contacts and recipient_contacts. Also type column is a kinda of a DB-modeling smell.
But I'm not a DB modelling expert and rather looking for an advise from an experts how to properly model those relationships.

Any input is appreciated.

Thanks.

Best Answer

It depends on the data associated with each entity, the semantics of the data and the queries that will be used for the data.

For example, if “sender contact” and “recipient contact” can be the same and have the same attributes, it would be best to keep them in a single table. If they are strictly different, keeping them in different tables might be better.

Similar with contact types: I would have a common table that holds contacts for all types so that this table can be the target of a foreign key constraint. If just a few attributes differ (e.g. “phone number” versus “e-mail address”) just have them all in the same table and add a check constraint that makes sure they are NOT NULL according to the type. If a lot of attributes differ, you can consider adding foreigb key references to type specific tables.

Try to compose the SQL statements that will be used with these tables most often. If they look natural and simple enough and can be supported with few additional indexes, you probably got it right.

Related Question