Table Design (all)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote