I have inherited a database used for tracking things like people, organizations, events, registrations, donations and more. One of the things I'm struggling with is revising the database schema. It's obvious I'll have tables for all the things listed above, and there will be a linking table called ROLES to provide a many-to-many relationship between people and orgs, but:
a. some PEOPLE are not associated with an ORGANIZATION
b. some PEOPLE have ROLES with more than one ORGANIZATION
c. some ORGANIZATIONS have multiple LOCATIONS and/or multiple DEPARTMENTS
d. all ORGANIZATIONS have at least one LOCATION
e. some ORGANIZATIONS have zero or one DEPARTMENTS
Example Orgs might include a University or a Bank with multiple branches or a Foundation which administers multiple Funds.
Problem 1: Are locations and departments attributes of an organization, or are they separate tables? (I'm leaning toward the latter.)
f. Some Organizations have one or more Locations and each Location has one or more Departments. (ORG > LOC > DEPT)
g. Other Organizations may have one or more Departments spread across one or more Locations. (ORG > DEPT >LOC)
Problem 2: This seems to imply that ROLES is the linking table between PEOPLE and DEPARTMENTS (not ORGs), right?
h. The database is used to track DONATIONS, which may be given by PEOPLE (or ROLES), ORGANIZATIONS, and/or DEPARTMENTS.
Problem 3: That means the foreign key in DONATIONS could be from any of 3 tables. That seems messy, not to mention a violation of Database Normalization.
Does anyone have any ideas or examples? At this point, I am willing to pay for a consultation... (click my user icon to the right, then send a private message)