Page 1 of 4 123 ... LastLast
Results 1 to 15 of 56
  1. #1
    Join Date
    Nov 2009
    Posts
    23

    normalizing the PhoneNumbers table

    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...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2009
    Posts
    23

    ??

    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?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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])
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2009
    Posts
    23
    I LIKE THAT! VERY SEXY SOLUTION!

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

  6. #6
    Join Date
    Nov 2009
    Posts
    23
    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?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Thumbs down

    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2009
    Posts
    23
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jarrette View Post
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jarrette View Post
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2009
    Posts
    23
    Quote Originally Posted by Brett Kaiser View Post
    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.

  12. #12
    Join Date
    Nov 2009
    Posts
    23
    Quote Originally Posted by Brett Kaiser View Post
    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?

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jarrette View Post
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Nov 2009
    Posts
    23
    Quote Originally Posted by Brett Kaiser View Post
    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.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jarrette View Post
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •