My application has users and ads. A user has a unique email. An ad belongs to a user (so, the
ads table has a user_id FK, that is simple). Every ad can also have multiple emails and phone numbers associated with it as a way to contact it's owner. Finally, the user should remember all his ever
used_phone_numbers, even from deleted ads, that will be used to provide hints during the creation of a new ad. Also, a phone number should be stored as two attributes:
country_code and a
My first attempt (omitting all fields that are not related to the problem):
- users table: email_id (unique index) (but it can also be a simple string)
- emails table: address (unique index)
- phone_numbers table: country_code, number (unique index on a combination of both fields)
- 4 join tables (2 between
phone_numbers, and 2 for users similarly)
Problems with this solution: emails and phone numbers are unique, but they rather should be treated as different records with the same data. For example,
ad2 both have an email
firstname.lastname@example.org. If the record is updated to
email@example.com, the update in such design is propagated to the both ads (and also to their users) but it should not.
Possible solution is to remove unique indices. But then the behavior described above still would be possible. Another solution I can think about is to create separate tables, something like
user_used_phone_numbers, but the downside here is the duplicated schema. There are many other combinations in my head but any of them does not feel correct. And I am also using Postgres so another option is to use arrays of strings for emails and arrays of hstores for phone numbers but it is less strict.
What would be the best solution to this problem?