Design Question


#1

I am sure some of you can give me an insight into this. This is more
towards the database design for the scenario below:
Say for example,

I have a person table and this person can have different address types.
One could be Home and the other could be say Office.
Should be model this

Table people
id
fname
lname

Table addresses
id
person_id
addr1
addr2

or

Table people
id
fname
lname

Table addresses
id
addr1
addr2

Table persons_addresses
person_id
address_id

Are there any tutorial that explains these concepts?
Any help is highly appreciated.

Thanks
Silvy Mathews


#2

Hi, silvy,

there should be myriads of tutorials about database design in the web.
Search for “database design” “normalization” and you’ll find them.

Regarding your question:

It depends:

Your first example models a one-to-many relationship and would be fine
if a duplication of addresses of people sharing the same address is ok
for you. Lets say you’ve got the brothers J.J. and J.R. Smith living
together at Nr. 10 FooWay, than you have to put this address twice in
your address table. Because your address with id=1 could obviously
reference only ONE Person in its foreign_key (person_id). Things are
different in a many-to-many relationship (your second example). People
there may have many addresses as well as addresses might reference many
people. n:n-Relationships come with the overhead of the join-table
(person_id, address_id) but provide you with the best normalization in
your example (IMHO).

Regards
Jan