Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Location
    Indiana
    Posts
    6

    relationship issues

    Ok, for some reason I can't think through this one. I'm getting brain burn when I try to work through a relationship I am building.

    Using Access.

    There are several Hospitals. [A,B,C,D,E]
    Each Hospital has several X-Ray machines.
    Each equipment sends to a Primary Destination and a Secondary Destination.
    { Destination examples [ZZ, XX, YY] each destination has a unique IP Address, Name, port, etc.}
    The destination is dependent on which Hospital the equipment is at.
    For example, each X-Ray machine in Hospital A sends to ZZ as Primary and XX as Secondary. Each X-Ray machine in Hospital B sends to YY as Primary and ZZ as Secondary.
    For each piece of equipment I need to pull out both Destinations (and unique IP, etc.)

    Dunno why I'm fumbling over this.

  2. #2
    Join Date
    May 2005
    Posts
    33
    Create a table MachineDestinations:
    id, Hospital_Id, Machine_Id, Destination_Id, isPrimary

  3. #3
    Join Date
    Feb 2006
    Posts
    7
    I think you have the following tables...

    Hospital
    HospitalID
    <Hospital Data>

    XRayMachine
    XRayMachineID
    <XRay Machine Data>

    Hospital_XRayMachine
    HospitalID
    XRayMachineID
    DestinationID_Primary
    DestinationID_Secondary

    Destination
    DestinationID
    IPAddress
    Name
    Port
    Etc...

    If what you are telling me is that a machine in each hospital always has a primary and secondary destination, I think it makes sense to break the normalization rule. However, if the number of destinations is not static, then George is absolutely right... you'll need a row for each hospital/machine/destination.

  4. #4
    Join Date
    Feb 2006
    Location
    Indiana
    Posts
    6
    Here is how I have it setup so far. And each machine does have exactly 2 destinations, a primary and a backup. I just want all the Destinations in one table and was confusing myself. Probably because I haven't had formal education and did not know about normal form before delving into it last night.

    Hospital
    HospitalID
    DestinationID_Primary \ I can now see how this violates it
    DestinationID_Secondary /
    <Hospital Data>

    XRayMachine
    XRayMachineID
    HospitalID
    <XRay Machine Data>

    Destination
    DestinationID
    IPAddress
    Name
    Port
    Etc...

    I figured since the destinations were dependent on the hospital the machine belongs to that is where the lookups should be. It appears I need a linking table between Hospital and Destination. Still trying to figure that out.

Posting Permissions

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