1

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.

3
  • 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
    – user1822
    Oct 27, 2015 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, 2016 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.) Jun 8, 2018 at 9:41

1 Answer 1

1

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.

2
  • 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. Oct 27, 2015 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, 2015 at 19:29

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