4 Replies Latest reply on Nov 9, 2011 3:56 PM by s

    flexible schema design


      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)



      Steve Moore

      Cumberland, Maine

        • 1. Re: flexible schema design

          Hello Steve,


          Problem 1: The distinction between entities and attributes is never absolute. It will depend on how you need to use the data. In the case you describe, in which all organizations have "at least" one location, you are implying they could have more than one, therefore it makes sense to have a Locations table. In the second case, in which some organizations have zero or one department, the department could be an attribute, but since some organizations can have multiple departments, departments should be a table.


          Problem 2: Yes, it seems that Roles is the join table between People and Departments. That table allows the Many to Many type of relationship.


          Problem 3: Don't assume you will achieve 100% database normalization. You are relating multiple entities to an entity. You could have a Donations table with four foreign keys (people, roles, organization, deparment) and that could work. If you want to make it more normalized, then create a Donors table. But you will still need to relate it to one of the four entities. So you end up shifting and/or adding complexity, not eliminating it.





          1 of 1 people found this helpful
          • 2. Re: flexible schema design

            I'd use one table for PARTIES, then separate tables for DEPARTMENTS, ORGANIZATIONS, and PEOPLE ( and ROLES I guess, but is the Person giving the donation or the "Role"?). The relationship between the Party and the other tables is always 1 to 1. Then use the Party ID for all relationships, like to Donations.


            It's not clear what you're using Locations for, so I can't speak to that, but Departments will have and Organization ID field which will be the Organization's Party ID.

            1 of 1 people found this helpful
            • 3. Re: flexible schema design

              Hi José,

              Thanks for replying.

              If Roles is the join table between People and Departments, what to do about an organization which has no departments? Create an "artificial" entry in the Departments table and just give it the same name as the Organization?

              Perhaps if I rename Departments to Divisions, I can use it to cover a broader range of possibilities.



              Steve Moore

              • 4. Re: flexible schema design

                Hi David,

                Thanks for your reply.

                Sorry, I am not catching on to what PARTIES is used for.


                A donation may be credited to a contact, an organization or both. And by organization, I mean a specific Department or Division of the organization.


                At first, I thought the Donations table should contain a foreign key from Roles. Except that there may be contacts not associated with an Organization, and therefore they wouldn't have any entries in Roles. So now I think Donations should have two foreign keys: one from Contacts and one from Divisions. I guess it wasn't as hard as I thought. Thanks!



                Steve Moore