First of all, I’m on swine flu watch, as one of my groupmates was pretty seriously ill yesterday, and my teacher forced her to stay in class until the midway point while he lectured (when he had first told us we had a quiz and the presentations and then she could go) and then we all shared a mic in giving our presentation. I’m hoping that she didn’t have the flu and thus, I won’t get it, but…this year, you can’t be too careful.
Anyway, if you didn’t catch my edit yesterday, this is what you missed:
So the quiz got postponed. However, our presentation didn’t go overly well today. The Star1/Star2 problem we were having I pointed out in discussions, but our main programmer couldn’t talk Access into doing what we wanted. We were fairly dinged on that one, but it was an Access toy database problem. But here’s a big hint to professors: when you require 3-5 tables in the assignment, it’s not fair to chew us out for including 3-5 tables when we could have done it in one.
To explain, here’s our Entity Relationship diagram in our presentation:
(You can click on that to make it bigger.)
Here’s how the prof said we could (and should!) have done it.
| id | name | married to? |
|---|---|---|
| 1 | John | 3 |
| 2 | Jeff | 5 |
| 3 | Jill | 1 |
| 4 | Jim | 6 |
| 5 | Jacob | 2 |
| 6 | Jean | 4 |
Besides the fact that this table assumes poly relationships aren’t acceptable (which, of course, in the US is true, but not in other places in the world), I’m not sure how you’d handle marriage dates, as obviously John and Jill would share a marriage date. And since our assignment said not to track divorces, I guess if John and Jill got a divorce, then we remove both their entries from the table. But what happens if Jill meets Johann and he sweeps her off her feet and they get married? Would I need to re-enter Jill at that time? Wouldn’t it be easier if her record already exists?
And that’s still not excluding the fact that the prof told us to use 3-5 tables in the assignment. I was pretty sure we could do marriage in two tables, but that still didn’t meet the three to five requirement. And as for the poly thing, he said we could make our own assumptions when I went up there and asked about things like gay marriage. So we made an assumption that poly was okay. Grr. And interrupting the flow of our presentation by asking us questions in the middle of it? *sigh* I dunno, I guess it’s a teaching style, but it seemed a little rude.
Grr. yeah. This is still eating me today, but i’ll try to drop it.
For what it’s worth, I would have three tables:
define table individuals
( individual_id, firstname, lastname )
define table marriages
( marriage_id, marriagetype {marriage, civilpartnership, shackedup}, begindate, enddate )
define table individuals-in-marriages
( individual_id, marriage_id )
This then means you can cope with any kind of marriage or civil partnership, including polyamorous ones (if that ever becomes legal). For all non-poly relationships, there should be exactly two rows in the i-in-m table. You’d also need some programme logic (a bigamy trigger?) to make sure that no-one was in more than one marriage for any defined time period…
The third table is a classic SQL trick, where you use an extra table to define information that would otherwise need a many-to-many join.