Results 1 to 9 of 9

Thread: Relationship

  1. #1
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Red face Unanswered: Relationship

    I'm creating database for keeping track of patient. So far i have the table:

    tblPatient and tblContact ( 1 to M)

    Right now i'm storing Patient address in patient table and contact address in contact table.

    But how can i indicate if patient have diff Home address and Work address ?

    Do i need another table to for address ? And if do create table for addresss would i like it to patient or contact or both.

    Thanks in advance
    Skharva

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    DB Modeling

    Seeing as you are storing like information (addresses for patients and contacts) I would recommend storing the information in a table.

    tblAddress(AddressID, Address, Address2, City, State, ZIP, County)
    PK(addressID)

    Then in your tblPatient table add a FK reference to tblAddress for Home address and one for Work address. Also add a FK reference to Contact under the same criteria.

    By doing this you will gain some added benefits from a couple of perspectives.
    1. By storing the possibility of duplicate information in one table. i.e. the work address is the same as the home address for a patient, and the address is the same for the contact for that patient, you will only have to enter the address information one time.

    2. By reducing the additional information in the contact and patient tables (address info) it will make queries run faster and indexes easier to build.

    3. By Storing the address in its own table again with the indexing it will make searching the information go faster.

    HTH. Here is an example schema to help you visualize

    ' Create Table : 'tblAddress'
    ' AddressID :
    ' Address :
    ' Address2 :
    ' City :
    ' ST :
    ' Zip :
    '
    CREATE TABLE tblAddress (
    AddressID LONG NOT NULL,
    Address LONGTEXT NOT NULL,
    Address2 LONGTEXT NOT NULL,
    City LONGTEXT NOT NULL,
    ST LONGTEXT NOT NULL,
    Zip LONGTEXT NOT NULL,
    CONSTRAINT pk_tblAddress PRIMARY KEY (AddressID));

    '
    ' Create Table : 'tblPatient'
    ' PatientID :
    ' PrimaryAddress : (references tblAddress.AddressID)
    ' WorkAddress : (references tblAddress.AddressID)

    CREATE TABLE tblPatient (
    PatientID LONG NOT NULL,
    PrimaryAddress LONG NOT NULL,
    WorkAddress LONG NOT NULL,
    CONSTRAINT pk_tblPatient PRIMARY KEY (PatientID),
    CONSTRAINT fk_tblPatient FOREIGN KEY (PrimaryAddress)
    REFERENCES tblAddress (AddressID),
    CONSTRAINT fk_tblPatient2 FOREIGN KEY (WorkAddress)
    REFERENCES tblAddress (AddressID));

    '
    ' Create Table : 'tblContact'
    ' ContactID :
    ' PrimaryAddress :
    ' WorkAddress :

    CREATE TABLE tblContact (
    ContactID LONG NOT NULL,
    PrimaryAddress LONG NOT NULL,
    WorkAddress LONG NOT NULL,
    AddressID LONG NOT NULL,
    CONSTRAINT pk_tblContact PRIMARY KEY (ContactID),
    CONSTRAINT fk_tblPatient FOREIGN KEY (PrimaryAddress)
    REFERENCES tblAddress (AddressID),
    CONSTRAINT fk_tblPatient2 FOREIGN KEY (WorkAddress)
    REFERENCES tblAddress (AddressID));

  3. #3
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up Re: DB Modeling

    Originally posted by mjweyland
    Seeing as you are storing like information (addresses for patients and contacts) I would recommend storing the information in a table.

    tblAddress(AddressID, Address, Address2, City, State, ZIP, County)
    PK(addressID)

    Then in your tblPatient table add a FK reference to tblAddress for Home address and one for Work address. Also add a FK reference to Contact under the same criteria.

    By doing this you will gain some added benefits from a couple of perspectives.
    1. By storing the possibility of duplicate information in one table. i.e. the work address is the same as the home address for a patient, and the address is the same for the contact for that patient, you will only have to enter the address information one time.

    2. By reducing the additional information in the contact and patient tables (address info) it will make queries run faster and indexes easier to build.

    3. By Storing the address in its own table again with the indexing it will make searching the information go faster.

    HTH. Here is an example schema to help you visualize

    ' Create Table : 'tblAddress'
    ' AddressID :
    ' Address :
    ' Address2 :
    ' City :
    ' ST :
    ' Zip :
    '
    CREATE TABLE tblAddress (
    AddressID LONG NOT NULL,
    Address LONGTEXT NOT NULL,
    Address2 LONGTEXT NOT NULL,
    City LONGTEXT NOT NULL,
    ST LONGTEXT NOT NULL,
    Zip LONGTEXT NOT NULL,
    CONSTRAINT pk_tblAddress PRIMARY KEY (AddressID));

    '
    ' Create Table : 'tblPatient'
    ' PatientID :
    ' PrimaryAddress : (references tblAddress.AddressID)
    ' WorkAddress : (references tblAddress.AddressID)

    CREATE TABLE tblPatient (
    PatientID LONG NOT NULL,
    PrimaryAddress LONG NOT NULL,
    WorkAddress LONG NOT NULL,
    CONSTRAINT pk_tblPatient PRIMARY KEY (PatientID),
    CONSTRAINT fk_tblPatient FOREIGN KEY (PrimaryAddress)
    REFERENCES tblAddress (AddressID),
    CONSTRAINT fk_tblPatient2 FOREIGN KEY (WorkAddress)
    REFERENCES tblAddress (AddressID));

    '
    ' Create Table : 'tblContact'
    ' ContactID :
    ' PrimaryAddress :
    ' WorkAddress :

    CREATE TABLE tblContact (
    ContactID LONG NOT NULL,
    PrimaryAddress LONG NOT NULL,
    WorkAddress LONG NOT NULL,
    AddressID LONG NOT NULL,
    CONSTRAINT pk_tblContact PRIMARY KEY (ContactID),
    CONSTRAINT fk_tblPatient FOREIGN KEY (PrimaryAddress)
    REFERENCES tblAddress (AddressID),
    CONSTRAINT fk_tblPatient2 FOREIGN KEY (WorkAddress)
    REFERENCES tblAddress (AddressID));


    Thanks this will help alot

    Based on your information i have following tables

    tblClient : ClientID, Name, AddressType, PrimaryAddress, WorkAddress, HomePhone, WorkPhone)

    tblAddress: AddressID, Address, Address2, City, State, Zip, County

    Now how will i link this ? By Address ID ?
    Skharva

  4. #4
    Join Date
    Dec 2003
    Posts
    268
    Close, you dont need to know what the address type is though. Seeing as the reference to the address is via either the Home address or Work address that deliniation is made there.

    Yes linkd the two together via the AddressID. That will be stored in both the workAddress and HomeAddress.

  5. #5
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Originally posted by mjweyland
    Close, you dont need to know what the address type is though. Seeing as the reference to the address is via either the Home address or Work address that deliniation is made there.

    Yes linkd the two together via the AddressID. That will be stored in both the workAddress and HomeAddress.

    Thanks

    One more clerfication.

    So i wil be linking AddressID with HomeAddress and WorkAddress right ?
    Skharva

  6. #6
    Join Date
    Dec 2003
    Posts
    268
    Yep

  7. #7
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183

    Thumbs up

    Originally posted by mjweyland
    Yep
    Thanks alot
    Skharva

  8. #8
    Join Date
    Sep 2003
    Location
    Raleigh, NC
    Posts
    183
    Originally posted by Skharva
    Thanks alot
    Ok, i'm still bit confuse. How can i link 2 fields from tblClient to one field in tblAddress.

    I have tblClient with "PrimaryAddress" and "WorkAddress" fields and tblAddress with "AddressID" ad PK....Do i need AddressID field in tblClient ?
    Skharva

  9. #9
    Join Date
    Dec 2003
    Posts
    268
    When you set up your relationships, you can add a table twice. It will add a an "_2" suffix to the table. You can then link AddressID to both the Work address and the home address.

Posting Permissions

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