Let’s say a PostgreSQL database has three tables, users, entities and connections, where the latter is a join table to keep track of which users are connected to which entities (so it has the expected user_id and entity_id columns).

Scenario:

Most places recommend for unique e-mail indexes using either a lower(email) index, or a datatype citext.

I am currently designing a contact management database for a customer engagement platform using PostgreSQL. The goal is to store all contacts, which can be created via incoming leads or by syncing contacts from a CRM. Each contact can have multiple phone numbers and multiple emails, and one phone/email can be linked to multiple contacts (parent-child relationship).