Database with people, addresses, companies and telephone numbers
Posted by: Andrea Croci
Date: March 20, 2013 02:49PM

Hello Everybody,

I'm pretty sure this question has been asked several times and it should actually be a textbook question. However I haven't found it anywhere and so, sorry for posting again.

The problem I'm having is to design a database to store data for people, companies, addresses and telephone numbers.

Almost all of the relationships would be M:N (a person can work at several companies and a company can have several people working for it, a person can have several addresses and so can a company, whereas at one address can live several people and be located several companies, etc.). The only exception I can come up with is that a telephone number normally is located fisically at only one address, but one address may have more than one telephone numbers (or none, obviously).

Now, I can come up with two possible solutions:

1) Make a connection table for each M:N relationship (like Person and Address, where person_id and address_id are the foreign keys in this table and the combination is unique). In this case for the person/address/telephone number relationships I guess I would need all three tables: person_address, person_telephone and address_telephone, otherwise I loose information on which telephone number is at which address of, say, a certain person. Same for the other relationships, or am I missing something?

2) Make one big table with person_id, address_id, company_id and telephone_id (and maybe some additional information which is not that relevant for my question). This table would not be normalized, it would have some columns set to NULL for some rows and some redundant data. I'm also assuming in this case I would have to create an index on all foreign keys. Probably it would also be better to give it an extra primary key like "person_company_address_telephone_id".

The questions are:

- Is there any other way that I'm not seeing?

- Which solution is better from a maintenance, data consistency and performance point of view?

Thank you very much if anyone can shed some light on this (or point to an existing solution).

Andrea.

Options: ReplyQuote


Subject
Written By
Posted
Database with people, addresses, companies and telephone numbers
March 20, 2013 02:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.