0

I'm developing an ERD for a graduate student manager program (it's for a university class, so it's a fairly trivial implementation).

In this snippet of the model, I'm trying to work out the 'application' and 'committee' entities/relationships. Basically, a committee - comprised of staff members - can be formed and assigned to review an application. Here are the entities I've come up with:

  • application: Comprised of a student's application data submitted through a form (student id, date submitted, degree, etc.).
  • committee: Self-explanatory. Group of staff members that can review applications.
  • staff_member: Any faculty member.
  • committee_membership: An associative entity I've created to resolve the many-to-many relationship between committee and staff member since a committee can have many staff members, and a staff member can belong to many committees.

Is this an effective implementation of what I'm trying to do? I'm still trying to wrap my head around associative entities and when they are needed. It seems strange to have the 'committee' table with a single column. Also - and I know I've given you limited information - do my relationships look generally correct?

enter image description here

4

This is just a quick appraisal of your ERD, so don't take it as a complete list of what may need to be added or changed to make it complete for your needs...

First, I would suggest that your single-column committee entity should have a name, and perhaps a description. You may also need other information attached to that table, like the date the committee was formed, and when it expired. But definitely some kind of human-friendly name at the minimum. If you were tasked with printing out a report on the committees and their work during the past year, you'd certainly want more than just an ID to identify each one, right?

Second - where do you plan to keep the outcome of the committee's deliberations? You are probably going to want to record somewhere whether the application was approved, not approved, or whether it was sent back for modification/more information. You might also want a notes field on the application to explain the committee's reasons for their decision, whatever that decision may be.

When you do these sorts of exercises, it helps to keep in mind what some likely final uses of the data may be, and then keep asking yourself "what if...?" for a while to try to come up with likely columns for each entity. Will reports need to be created for this data? What type of reports? What if an application is marked needing further information and resubmission - does it have to go back to the same committee for the second round of approval, or might it fall to a new committee to handle the re-application review?

  • Thanks, that's a helpful analysis! Now that you've sparked the question, I have to wonder where I could efficiently store the outcome of the committee's decision. My immediate thought would be to have a status column, such as a CHAR(1) with a check constraint, in the application field (as well as a notes field as you described). Can you foresee any problems with that approach? – Tyler Daniels Oct 3 '13 at 13:22
  • 1
    I think that would be a fine approach. The only issue I can see with that is, with just one char as the status, you will need to plan on providing some sort of human readable translation, either in your code or as another table in the database. Ie, Status | id (char that is being used), Desc ("Approved", "Not Approved", "Incomplete"... etc.) Otherwise, if someone else is trying to read your data, they may find your one char status a bit cryptic. – TeresaMcgH Oct 3 '13 at 16:33
3

Without any additional columns on the 'committee' table, it's existence can seem strange, but this is correct normal form. Other information could be added to this table such as a committee's name, creation date, etc... which would make the table more useful and possibly less strange looking.

2

In addition to what was said before, the intersection table should have both FK columns defined as mandatory (not null) unless you introduce a sequence number column to represent the PK (which is not required by the business here).

The M-M concept is simple. In your case how else could we represent that committee C1 has members M1, M2, ..., Mn? We can't create a fixed number of columns (against normalization) and we don't even know how many will there be. So we need to represent each member's relationship to a committee in a separate row. The same is true if you look at it from the member's side. We need to store information like M1, C1, C2, C3,...,Cn. The same argument holds. As a result you came up with the intersection table.

Many resources exist that show M-M - Here M-M Pattern

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.