normalizing the PhoneNumbers table

jarrette · #1 (**permalink**) 11-17-09, 15:45

Here's the problem...
I have a PhoneNumbers table

PhoneNumberID (pk)
AreaCode
Prefix
Suffix

Phone numbers can be owned by different objects (patients, offices, Pharmacies, Users, etc.). I don't like the idea of having multiple nullable foreign keys inside the PhoneNumbers table....

PhoneNumberID (pk)
OfficeID (fk nullable)
PatientID (fk nullable)
PharmacyID (fk nullable)
SiteUserID (fk nullable)
AreaCode
Prefix
Suffix

if just seems sloppy.

The only other alternative I can think of is adding fields to each of the foreign objects:

PatientID
FirstName
LastName
PhoneNumber1ID
PhoneNumber2ID
PhoneNumber3ID

Which I hate even more!

Is there something I'm missing here? There's got to be a cleaner way...

Brett Kaiser · #2 (**permalink**) 11-17-09, 16:09

usually U treat phone numbers as pretty much unique to a person/business

So I don't worry too much about redundant data

Also, a Phone number ID????

WHY on earth do you want to do this?

And is this a table?

Code:

PhoneNumberID (pk)
OfficeID (fk nullable)
PatientID (fk nullable)
PharmacyID (fk nullable)
SiteUserID (fk nullable)
AreaCode
Prefix
Suffix

If it is, you seriously need to re-evaluate that

That looks like it might be at least 4 different tables

What was the question again?

jarrette · #3 (**permalink**) 11-17-09, 16:18

Are you asking why I would have a unique identifier in a phone number table? I always have a unique identity primary key in every table. There is a many to many relationship between patients and phonenumbers, how could you NOT have a primary key?

Brett Kaiser · #4 (**permalink**) 11-17-09, 16:25

OK, I see you address 1 of the as a Patient Table

My Advice

Don't have a PhoneNumber table...just lose it altogether

Phone's belong to people..allow them to have as many as they want, and for what ever type of usage they want

Code:

CREATE TABLE Person (
		  PersonID int IDENTITY(1,1) PRIMARY KEY
		, LastName varchar(100)
		, FirstName varchar(50)
		, MiddleName varchar(50)
		, OtherStuff varchar(256)	-- This means more Person Specific Stuff
)

CREATE TABLE PhoneType (
		  PhoneType				char(2)		PRIMARY KEY
		, PhoneTypeDescription	varchar(256)	
)


CREATE TABLE PersonPhone (
		  PersonID				int
		, PhoneNumber			char(25)	-- Use Constraints to eliminate -,.
		, PhoneType				char(2)
		, InternationalInd		char(1)	
		, CONSTRAINT [PersonPhone_PK] PRIMARY KEY CLUSTERED 
(
	  PersonID ASC
	, PhoneNumber ASC
	, PhoneType ASC
)
)

ALTER TABLE [dbo].[PersonPhone]  
	WITH CHECK ADD  CONSTRAINT [PersonPhone_FK1] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])

ALTER TABLE [dbo].[PhoneType]  
	WITH CHECK ADD  CONSTRAINT [PersonPhone_FK2] FOREIGN KEY([PhoneType])
REFERENCES [dbo].[PhoneType] ([PhoneType])

jarrette · #5 (**permalink**) 11-17-09, 16:28

I LIKE THAT! VERY SEXY SOLUTION!

Now I will make tables PatientPhoneNumbers, ClinicPhoneNumbers, PharmacyPhoneNumbers, etc?

jarrette · #6 (**permalink**) 11-17-09, 16:31

I notice you have no PersonPhoneID to uniquely identify each PersonPhone in your table...What happens if you have 3 phone numbers for a given person and you edit one of them on a page, how do you update a record that you have no identity for?

Brett Kaiser · #7 (**permalink**) 11-17-09, 16:35

No....

You have made the relationship that people have phones...

Now...people are patients (sometimes),

So you would the have a patient table

CREATE TABLE Patient (
PersonID
, patient type data)

Patients then have appointments or visits, so you track their stays or visits in another table

CREATE TABLE PatientVisit (
PersonId
AppointmentDate
EndDate -- Same day for an office or out patient, later for hospita stay
Tyep
Other Patient Visit info

In Any case...You INHERIT The Phone through the Person for the patient

Now let's say you want to record Doctors, or Nurses, or staff

CREATE TABLE Staff (
PersonId
Title
Education
Responsibilites
other Staff type data
)

You the inherit Dr. Kildares Phone, through Person to the Person Phone table

If you want his Home phone, the PhoneType = 'Home'

ect

jarrette · #8 (**permalink**) 11-17-09, 16:39

So I would have to create a "person" in the "persons" table every time I create a "doctor" or a "patient" or a "user", etc? yuck

Brett Kaiser · #9 (**permalink**) 11-17-09, 16:40

Quote:

Originally Posted by jarrette

I notice you have no PersonPhoneID to uniquely identify each PersonPhone in your table...What happens if you have 3 phone numbers for a given person and you edit one of them on a page, how do you update a record that you have no identity for?

UPDATE PersonPhone
SET PhoneNumber = @PhoneNumerNew
WHERE PersonId = @PersonId
AND PhoneNumber = @PhoneNumerOld
AND PhoneType = @PhoneType

If that doesn't work

DELETE FROM PersonPhone
WHERE PersonId = @PersonId
AND PhoneNumber = @PhoneNumerOld
AND PhoneType = @PhoneType

INSERT INTO PersonPhone(PersonID , PhoneNumber, PhoneType, InternationalInd)
SELECT @PersonId, @PhoneNumerNew, @PhoneType, @InternationalInd

Brett Kaiser · #10 (**permalink**) 11-17-09, 16:41

Quote:

Originally Posted by jarrette

So I would have to create a "person" in the "persons" table every time I create a "doctor" or a "patient" or a "user", etc? yuck

No, not yuck...can a patient ever be a doctor?

jarrette · #11 (**permalink**) 11-17-09, 16:43

Quote:

Originally Posted by Brett Kaiser

UPDATE PersonPhone
SET PhoneNumber = @PhoneNumerNew
WHERE PersonId = @PersonId
AND PhoneNumber = @PhoneNumerOld
AND PhoneType = @PhoneType

If that doesn't work

DELETE FROM PersonPhone
WHERE PersonId = @PersonId
AND PhoneNumber = @PhoneNumerOld
AND PhoneType = @PhoneType

INSERT INTO PersonPhone(PersonID , PhoneNumber, PhoneType, InternationalInd)
SELECT @PersonId, @PhoneNumerNew, @PhoneType, @InternationalInd

I prefer going to 3nf for the phonenumber tables and having a single, primary key. I am a web developer and maintaining an "old phone number" through page posts isn't as easy as building a winforms app, plus what if they put the same number for "fax" and "home" phones? Seems much easier to just have a unique id and lose the headache.

jarrette · #12 (**permalink**) 11-17-09, 16:45

Quote:

Originally Posted by Brett Kaiser

No, not yuck...can a patient ever be a doctor?

How else can you generate a "personID" that is unique throughout the doctors, patients, and users tables without creating a new "person" in a "persons" table and using that identity field as a foreign key in the other tables?

Brett Kaiser · #13 (**permalink**) 11-17-09, 16:48

Quote:

Originally Posted by jarrette

I prefer going to 3nf for the phonenumber tables and having a single, primary key. I am a web developer and maintaining an "old phone number" through page posts isn't as easy as building a winforms app, plus what if they put the same number for "fax" and "home" phones? Seems much easier to just have a unique id and lose the headache.

"OldPhoneNumber"

Oye

You do a select

You get the phone number

They then updatet the column

It might be terminolgy, but you must SOMEHOW retainm the "Old" and "New" phone numbers in the web app

Right?

jarrette · #14 (**permalink**) 11-17-09, 16:49

Quote:

Originally Posted by Brett Kaiser

No, not yuck...can a patient ever be a doctor?

I'm assuming a doctor is a person
I'm assuming a patient is a person
I'm assuming a user is a person

therefore, If I insert a new doctor, patient, or user, I must create a new person. The resulting "PersonID" integer that is returned after creating a new person must be placed into the doctor, patient, or user table.

Brett Kaiser · #15 (**permalink**) 11-17-09, 16:49

Quote:

Originally Posted by jarrette

How else can you generate a "personID" that is unique throughout the doctors, patients, and users tables without creating a new "person" in a "persons" table and using that identity field as a foreign key in the other tables?

No...you don't need another ID for a patient or a doctor or a whatever....

you use the person id

By the fact that a person's id is in the Doctor (or staff) table, that means that person IS a doctor

By the fact that a persons Id is in the Patient Table, that means that person is a patient

By the fact that a person's id is in the GO-Go bar association inspectors, that person goes to GO-Go bars

Internet Services

Web Development

Digital Marketing

Consumer Tech