I have a table persons with the columns per_id, per_passport, per_name and per_address.

Two or more persons can live in the same address (like families or room-mates) so if this happened, the data is repeated. I think it should be avoided due to a normal form.

So should I create a table addresses to store all the addresses of the persons?

Also some persons may be in the home address or in the work address. It means persons can have multiple addresses. So should I create a per_add intermediate table?

I have the same problem to store email addresses and phone numbers.

I use PostgreSQL.

closed as off-topic by Jack Douglas Oct 27 '15 at 17:55

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    "to store all the addresses of the person" seems to indicate that you have more than one address per person. In that case, yes it would be better to create an additional table – a_horse_with_no_name Oct 27 '15 at 8:16
  • 2
    Disappointed to see that this question has been closed as off topic. Dealing with addresses is a really common problem in database design, so I think this question is very much on topic. – beldaz Feb 22 '16 at 0:23
  • 1
    A separate address table is useful if you actually use the address as an entity (e.g. to send a mailshot, where multiple people at the same address would receive only one between them), but your user interface would have to reflect that (so that when a user is changing an address, there would be way to make a correction to the address for all people at the address, and a way to move one person to a different address). (Otherwise, to just automatically remove duplicates would be a small optimization that would probably not be justified by the additional complexity.) – John B. Lambe Jun 8 '18 at 9:41

If the address is a single attribute there's nothing to be gained from moving out to another table. You're not suffering from a low normal form here, since there's no functional dependency from a non key attribute to the address. Things might be different if, say, you were storing the address as a set of attributes.

  • 3
    "nothing to be gained from moving out to another table" — except that it reduces duplication in exactly the same was it would if there were multiple attributes. I'm not saying the OP should do it — that depends on the business case — just that the number of attributes isn't the deciding factor. – Jack Douglas Oct 27 '15 at 17:54
  • 1
    @JackDouglas: That approach can lead to complications, since it suggests some natural key in the address that makes it simple to map multiple users to the same address. But this can be upset by spelling variations, abbreviated St, Av, etc. I'd say better not to treat addresses as entities unless you do it really carefully. – beldaz Oct 27 '15 at 19:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.