I am a bit out of the way of Database design, so forgive me in advance if the answer to this is obvious:
In its simplest form, the database I'm designing contains the following relations and attributes:
Employee: EmployeeId (PK), EmployeeName
Visit: VisitId (PK), StartTime, EndTime, Priority
Site: SiteId (PK), SiteName
Employee/Visit has a N:M relationship, in that many employees can go on a visit, and a visit can be performed by many employees. This results in:
EmployeeVisit: EmployeeId (PK/FK), VisitId (PK/FK)
... which sits between the two relations
Visit/Site has a 1:N relationship, in that each Site can be visited many times, but each Visit can be to only one site.
Additionally there is an identifying relationship as a Visit cannot exist without a Site.
As far as I can see, it is in at least 1NF. But I believe it may violate 2NF and here's why:
Visit: VisitId (PK) SiteId (PK/FK), StartTime, EndTime, Priority
2NF states that all non-key attributes must be dependent on all parts of the primary key. In this case that would mean not JUST VisitId and not JUST SiteId. But StartTime, EndTime or Priority seem to be uniquely identifiable with only VisitId. Are any of these fields functionally dependent on SiteId (PK/FK)?
At the same time, SiteId must form part of the primary key because of the identifying relationship.
How to resolve this? I've considered creating a further table between Visit and Site as follows
VisitedSite: VisitedId (PK/FK), SiteId (PK/FK).
I've also considered just changing SiteId within the Visit table to be a non-primary key, but still setting it as not null.
It's also possible that I've forgotten a fundamental of ER Database Design...
