How to associate similar data with different tables in a database?

Question

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_emails and 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 number.

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 ads and emails / 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, ad1 and ad2 both have an email [email protected]. If the record is updated to [email protected], 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 ad_phone_numbers and 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?

Answer 1

You probably won't need the used_emails and used_phone_numbers tables, since you can always obtain that information from your ads table with a simple select distinct query. In this case, using arrays would be convenient, since you can always use unnest to produce separate records.

For separate records in phone number, should you end up using records, you could either use a composite type (e.g. CREATE TYPE phone_number AS (country_code int, number text)) or fragment the phone into two fields using regexp_matches (e.g. regexp_matches(phone, '^ *(\+1|\+[2-9]\d)? *(.*?) *')).

Your ads table should have a deleted boolean flag (or timestamp field), so that ads are not deleted but rather obsoleted, so that historic information can be queried (such as used emails and phones) but still filtered out when not needed.