Table design possibilities

Question

In a simple table design where I prefer to keep the addresses of persons (persons is the base table with person_id as the PK) in a separate table, is it better to store the person_id in the address table or store the address_id in the person table? The address tables does need a separate PK, since it is used to store addresses of other entities such as businesses. Storing the addresses which have the same attributes, irrespective of whether it is for individuals or businesses, in one table, let us also use it for analysis.

Answer 1

The main criterion for the decision is if the following is required:

• Multiple persons should be able to share the same address (address_id in person-table).

• One person should be able to have multiple addresses (person_id in address-table).

If both is true you will need an additional table containing person_id and address_id. If both is forbidden you can add a unique constraint on the foreign key column.

Answer 2

If every person requires an address, but an address does not necessarily belong to a person (because it could belong to a business), make the address_id part of person. Then you don't need NULL values for the foreign key field.