database design questions

[Phaelax]

I'm making a kind of address book thingy and seem to have confused myself on the best way of organizing the tables. At the moment, I have two tables, Contact and Number.

Here's a basic representation of my tables:

 ___________
| Contact   |
|-----------|
| cid       |
| fname     |
| lname     |
| address   |
| email     |
|___________|


 ___________
| Number    |
|-----------|
| nid       |
| cid       |
| number    |
| desc      |
|___________|

The reason a contact's number are stored in a separate table is because each contact may have 0 or more numbers associated with it.

Is this a good design? Cause the way I'm looking at it now, I have to hit the database with an extra call for each contact. One query to get a list of all contacts, then I'll have to loop through each contact to do an additional query to get their numbers. Seems inefficient to me. Is there a better way to query for the results?

SELECT contact.*, number.* FROM contact, number WHERE number.cid=contact.cid

But not only does that return a lot of redundant data (repeating the contact info for each number belonging to the same person) but it also won't return any contacts if they don't have any numbers associated.

There's gotta be a better way than running a query to return a list of 200 contacts, then having to make another 200 queries to get all the numbers associated with each one of those contacts.

[Taywin]

It is fine as long as your data is sparse (less than 50% of contacts have a number) and you have small data set. In order to get a contact with number, use JOIN. However, both will have a different field set or you would have many null fields value.

# get all that have contact
SELECT contact.*, number.*
  FROM contact LEFT JOIN number on contact.cid=number.cid;

# get all that do not have contact
SELECT contact.*
  FROM contact LEFT OUTER JOIN number on number.cid=contact.cid
  WHERE number.cid is null;

You could add more WHERE clause when you need only 1 record at a time.

[Phaelax]

I'd say 99% of the contacts will have numbers, but maybe only about 10% will have more than 1.

[joshmac]

From a database design standpoint, I would say yes, this is an acceptable design for what you are wanting to accomplish. However, another option would be to include phone numbers in your contact table without the other table like so:

| Contact    |
==============
| cid        |
| fname      |
| lname      |
| address    |
| email      |
| home_phone |
| cell_phone |
| work_phone |