Table design for people with multiple phones/addresses of the same typ

  • (2 Pages)
  • +
  • 1
  • 2

21 Replies - 2188 Views - Last Post: 20 January 2011 - 03:56 PM Rate Topic: -----

#1 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 04:06 PM

Trying to design a database that, for one thing, holds contact info for staff. The tables (going to focus on the phones, but having the same issue with addresses), so far, are as shown:

ContactInfo
ContactID
Name
DOB
AddressID
Phones (Links to a table, just not sure which one would be correct right now)

Phones
ContactID
PhoneTypeID
PhoneNumber

PhoneTypes
PhoneType
PhoneTypeID

What's confusing me is what if someone has multiple cell phones or has two jobs, and therefore two office numbers, for example. This would create two records with the same ContactID and PhoneTypeID, which seems like a bad thing to me. Do I need to change my tables around some how? This seems like a normalization issue to me, but not sure how to break it down.
This is my first database so I might be getting terms wrong. I'm learning as I go at this point.

Is This A Good Question/Topic? 0
  • +

Replies To: Table design for people with multiple phones/addresses of the same typ

#2 macosxnerd101  Icon User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12176
  • View blog
  • Posts: 45,242
  • Joined: 27-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 05:21 PM

You could make PhoneID, ContactID, and PhoneNumber Composite PKs. That way, each row should still be a unique entity.
Was This Post Helpful? 1
  • +
  • -

#3 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 06:03 PM

View Postmacosxnerd101, on 16 January 2011 - 04:21 PM, said:

You could make PhoneID, ContactID, and PhoneNumber Composite PKs. That way, each row should still be a unique entity.


OK, that makes sense. But what foreign key would I list in the contact table for the phones? Wouldn't I have to make multiple columns to list each possible key from the Phones table? Sorry, this is a stupid question and I feel I know the answer, it's just eluding me. I do appreciate the help though.
Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12176
  • View blog
  • Posts: 45,242
  • Joined: 27-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 06:10 PM

The PhoneID and ContactID fields would be both PKs and FKs in the Phones table. The Contacts table doesn't need any PKs. Since Phones.ContactID is an FK to Contacts.ContactID, you can still join. For the Phones table, each row is a PK essentially. So multiple rows means multiple entities, and multiple composite PKs.

This post has been edited by macosxnerd101: 16 January 2011 - 06:55 PM
Reason for edit:: Incorrect information

Was This Post Helpful? 1
  • +
  • -

#5 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 06:23 PM

View Postmacosxnerd101, on 16 January 2011 - 05:10 PM, said:

The PhoneID and ContactID fields would be both PKs and FKs in the Phones table. The Contacts table doesn't need any PKs. Since Phones.ContactID is an FK to Contacts.ContactID, you can still join. For the Phones table, each row is a PK essentially. So multiple rows means multiple entities, and multiple composite PKs.


I think I get what you're saying but just want to clarify a couple of things. In the first sentence you said PhoneID, did you mean PhoneTypeID? And you said the Contacts table doesn't need any PK. Shouldn't every table have a PK or did you mean FK?
Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12176
  • View blog
  • Posts: 45,242
  • Joined: 27-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 06:30 PM

1) Yes- I meant PhoneTypeID, not PhoneID. Sorry for the confusion. :)

2) I didn't say that. PhoneTypeID and ContactID are both FKs in the Phones table. But make all three fields PKs as well, forming a Composite PK.
Was This Post Helpful? 0
  • +
  • -

#7 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 06:38 PM

Is this an Access db? Check out this template: http://office.micros...C001225343.aspx

Phones table is a 'junction' table between ContactInfo and PhoneTypes with Phones table ContactID and PhoneID as foreign key fields (they are primary in the other tables). It is expected that Phones table would be many-to-one relationship with each of the other two tables. Don't put any phone ID in ContactInfo table, ContactID goes in Phones table. Same goes for addresses. Remove the AddressID and Phones fields from ContactInfo.

Not really seeing need for composite key here. If this is an Access db, could add an autonumber field to Phones to generate a unique identifier, but again, it would NOT be saved to the ContactInfo table but it might be useful for some other situation yet to come.

Might check out this tutorial on relational database principles: http://forums.aspfre...les-208217.html

This post has been edited by June7: 16 January 2011 - 06:59 PM

Was This Post Helpful? 1
  • +
  • -

#8 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 06:48 PM

View Postmacosxnerd101, on 16 January 2011 - 05:10 PM, said:

The PhoneID and ContactID fields would be both PKs and FKs in the Phones table. The Contacts table doesn't need any PKs. Since Phones.ContactID is an FK to Contacts.ContactID, you can still join. For the Phones table, each row is a PK essentially. So multiple rows means multiple entities, and multiple composite PKs.


That was the part that I was referring to.
So to visualize this, the Phones table would look like this:

Phones
ContactID (FK from Contacts Table) }
PhoneTypeID (FK from PhoneTypes table) }(PK for Phones table using all 3 columns as a composite key)
Phone }

And I don't need to have a spot in the Contacts table for phone numbers and addresses because they are linked to those tables by the ContactID FK in those tables? I think I understand this now. Did I miss anything? Thank you.

P.S. Previewed this post and noticed whitespace was taken out so the "}" don't line up. Not sure how to fix this so just imagine them lined up vertically with the one in the PhoneTypeID line.
Was This Post Helpful? 0
  • +
  • -

#9 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 07:10 PM

Quote

Phones
ContactID (FK from Contacts Table) }
PhoneTypeID (FK from PhoneTypes table) }(PK for Phones table using all 3 columns as a composite key)
Phone }
No, Phones table PhoneTypeID field is FK to the PK in PhoneTypes table (PhoneTypeID field also). No composite should be needed.

This post has been edited by June7: 16 January 2011 - 07:14 PM

Was This Post Helpful? 0
  • +
  • -

#10 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 07:50 PM

View PostJune7, on 16 January 2011 - 06:10 PM, said:

Quote

Phones
ContactID (FK from Contacts Table) }
PhoneTypeID (FK from PhoneTypes table) }(PK for Phones table using all 3 columns as a composite key)
Phone }
No, Phones table PhoneTypeID field is FK to the PK in PhoneTypes table (PhoneTypeID field also). No composite should be needed.


Wouldn't that mean people would only be able to have one type of phone? That was the problem I was trying to address.
Was This Post Helpful? 0
  • +
  • -

#11 macosxnerd101  Icon User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12176
  • View blog
  • Posts: 45,242
  • Joined: 27-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 08:50 PM

@dylan.mann: Sorry for that confusion there! I edited that post to scratch that statement.

@June7: If not a composite key, would you suggest the phone_number as the PK since it should be unique?
Was This Post Helpful? 0
  • +
  • -

#12 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Re: Table design for people with multiple phones/addresses of the same typ

Posted 16 January 2011 - 09:12 PM

Thanks macosxnerd101, that line had confused me.

June7, this is going to be used in a MySQL database, though I'm trying to use as much standard SQL as possible in case the DBMS ever gets changed.

Also, I'm pretty sure I'd want to use a composite key since it's pretty common for people to share a house number. I think the smallest composite key I could get away with would be ContactID and phone number since a single person wouldn't have the same number twice. Though I think I'll use all three to cut down on programming in case someone ports their number from, say, a home number to a cell phone.
Was This Post Helpful? 0
  • +
  • -

#13 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 18 January 2011 - 08:01 PM

I have direct experience only with Access databases. Access has an autonumber datatype that could be used as a PK in any table. My understanding is that SQL db structure doesn't have that. However, as I understand this particular db, the Phones table does not require a PK, at least not for the purpose of linking to corresponding FK in another table (unless there are tables dependent on Phones that we are not aware of). Assigning the three fields as a composite PK could serve to prevent a duplication of an entry, as a PK value must be unique. This is the only reason I can see to set up the composite PK in Phones table. Although validation and check for duplication could be handled by code.

The Phones table will hold record for each phone that a client has, as many as desired - unlimited, even multiples of the same phone type, regardless of whether or not there is a PK. No, I don't suggest the phone number as PK, clients could offer same number (a switchboard receptionist as an alternate contact maybe).

Don't see what shared building number in address has to do with Phones table record.

This post has been edited by June7: 18 January 2011 - 08:28 PM

Was This Post Helpful? 0
  • +
  • -

#14 macosxnerd101  Icon User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12176
  • View blog
  • Posts: 45,242
  • Joined: 27-December 08

Re: Table design for people with multiple phones/addresses of the same typ

Posted 18 January 2011 - 08:30 PM

SQL Servers have the UNIQUE and AUTO_INCREMENT keywords as well, which do the same thing as autonumber in Access.

Quote

Assigning the three fields as a composite PK could serve to prevent a duplication of an entry, as a PK value must be unique. This is the only reason I can see to set up the composite PK in Phones table. Although validation and check for duplication could be handled by code.

My thoughts exactly. I agree about validating often, and having the constraints at the database layer is probably the best way to ensure valid data in the tables. :)
Was This Post Helpful? 0
  • +
  • -

#15 dylan.mann  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 26
  • Joined: 03-December 10

Re: Table design for people with multiple phones/addresses of the same typ

Posted 18 January 2011 - 10:43 PM

Quote

Don't see what shared building number in address has to do with Phones table record.


Shared building number?

I think what was messing me up was I was thinking I needed to have a placeholder in the Contacts table for the phones. Now I realize that all I have to do is a query like:
Select * FROM Phones, ContactInfo
WHERE Phones.ContactID = ContactInfo.ContactID


(Yes I know it should be refined, I just haven't memorized SQL and don't have my cheat-sheet with me)

This post has been edited by dylan.mann: 18 January 2011 - 10:52 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2