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?
Best Answer
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:
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.