Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Design (all)

    I *thought* I knew how to normalize a database but this one has me stumped:

    I have a "Sites" table. One Site can have many "Callouts", so I have a "Callouts" table. Both Sites and Callouts can have "Positions", so I have a Positions table. The Positions table has a raft of other tables associated with it. Positions can either be Permanent or Callout (casual). So, to relate the Positions table to Sites or Callouts, I've added a field in Positions called "IsPermanent" and a linking field called "CalloutOrSiteID". (See picture of Relationships screen attached). The "CalloutOrSiteID" field seems ugly, but do you think its workable?
    Gwenda

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Design (all)

    No, I think you need to back up and rethink the design. A circular relationship like that will cause you problems. You need to get at the central piece of data in designing relationships. Is the central fact the site or the position? If the base fact is the site, then it has positions, and those positions are filled with assignments, right? The positions may be permanent or casual, and that is a characteristic of position for the site but not the site alone. I would link the tables like this:

    Site {1:M} Positions - Joined on SiteID - PK in Site and FK in Positions
    Positions {1:M} Assignments - Joined on PositionID - PK in Positions and FK in Assignments
    Assignments {0:M} CallOuts - Joined on AssignmentID - PK in Assignments and FK in CallOuts

    I'd use a single table for assignments and only populate the fields needed for callout assignments, leaving the rest blank or populating them with some sort of N/A value. That way, you can inherit the necessary information through the links.

    I'm also concerned about Licenses and Skills. Are those mutually exclusive requirements of a position or can both be needed? How are you representing them in the Positions table? You have an ID in each table, but they can't all point to the same key, so you need to name the keys to identify them and reduce confusion ( and memory lapses ).
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Design (all)

    Thanks Charlotte. I'm actually working in MSDE instead of Access, but I wanted to post here in the hopes you'd answer.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table Design (all)

    The principles are the same, regardless of the specifics of the database engine. I hope my ideas helped.
    Charlotte

Posting Permissions

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