1

I'm working on a database that stores address information. Each address has a Street, Number, Bus (as in the apartment they live in) and CityCode parameter. The CityCode parameter is a FK to the City table which has the postal code/zip code as PK but I think this is not important for this question.

I'm not sure what to do with the Address table. A Person will have an Address so these 2 tables will have a relationship. A combination of a Street, Number and Bus is as far as I know a unique entry in a database leading me to think that Street, Number and Bus could be a PK consistent of 3 columns.

On the other hand I would have to add 3 extra columns instead of 1 into my Person table to relate to the Address of the Person.

I've done some looking around and what I found is that PK's should be as short as possible for good performance. On the other hand, having just an auto-increment Id as PK makes it harder to view the raw data without joining tables.

What should I do in this case?

RDFozz
  • 11,631
  • 4
  • 24
  • 38
Krowi
  • 205
  • 3
  • 7
  • Answers will vary and be opinion-based, but sounds like a surrogate key would be better (e.g. an identity column). You can always enforce uniqueness of those three columns with a unique constraint or unique index, but even that doesn't seem sensible - what if two people live in the same house? What if two people live at the same address but in different apartments? Why is bus part of this key? The bus that is closest to their address seems like an unrelated thing that could change even if their address doesn't. Unless bus means something else other than public transportation they use... – Aaron Bertrand Sep 18 '15 at 11:45
  • @AaronBertrand: Bus is as the apartment they live in, I edited the question. Could you edit or repost your comment please? – Krowi Sep 18 '15 at 11:48

2 Answers2

2

Using those 3 fields for a primary key is not a good idea (or did I misunderstood your question). There are 3 reasons for that:

  1. The length of the key would be big.
  2. The data would be stored in the Person table too.
  3. Streets can sometimes be renamed or renumbered.

The best is to use a special field like you do for the Person data. As soon as you need a value that is not defined by yourself as PK then you should think twice on using it as a PK.

About your ZIP code. Not all countries have a single ZIP code for a single city. As long as you only need cities in your country then this is not a problem.

Marco
  • 3,710
  • 5
  • 23
  • 31
  • Good points. About the city's ZIP code, found this to be true after a little more research after posting this question but thank you for pointing out. – Krowi Sep 18 '15 at 12:09
0

It depends on how you will use the Address table.

I.e. one author could write several books and some books could be written by several authors - so you have to store the information between two tables and additionally setup a 'junction' table to store the relation between these two table. To retrieve information you'll use more complex selects, like joins ore nested queries. In such queries dictionary tables, like Postal Codes, will charge much more resources.

So, if you plan using nested queries or joins on the Address table I'd de-normalize it for performance reason (use it without dictionary tables and store all you can in just two-three main tables). If you don't plan complex queries and can select the information just from the Addresses table, I'd normalize the Address table with a few dictionary tables.

Leo D
  • 1
  • 1