On Apr 27, 2009, at 5:11 PM, edgarsmolow wrote:
A "persons" table is used in various parts of the app to track individuals. The "rel" column is the relation of the person to respondent (i.e, the person filling out the web form). In addition, the respondent's children and his/her choices for financial trustees are tracked, too. One or more trustees are designated as first choice, second choice, etc. in the seq column (it's actually an integer: 1, 2, 3,...) A child can be a trustee; or the person might designate his/her brother, sister, close friend, etc.The issue I'm struggling with is that currently, when a trustee that happens to be a child, is deleted from the Choices table, it has the unintended effect of also deleting the corresponding row from the persons table (though a row remains in the children table).
I can understand why a Child would be a subclass of a Person, but why are you using inheritance like this? Why is a Choice a subclass of a Person? I think your problem stems from the way you have the inheritance setup of the classes.
How do I define the tables so that a choice (trustee) that's added also adds a row to the person table (which currently works!), but a choice can be deleted WITHOUT deleting the person row? Use case: the respondent no longer wants that person as a trustee, but data on the person should remain the database.
I think you're overloading inheritance poorly here. If you're setting up a system where you have people, and people can have trustee's, its also possible that a child might be the trustee of two different people (parents that have separate accounts), so merely having a 'child' that can link to just one parent isn't suitable.
I'd suggest instead to have a single Person table, move the is_minor field from Child into it. Have a separate many-to-many table, perhaps called 'trustee' like so:
trustee:
parent_id = foreign key person.id
child_id = foreign key person.id
child_of = boolean
Then to find all the trustees for a person, you just join to the
trustee table back to the person table. This also lets you have two
parents for a child (should they both have separate accounts), and a
trustee to one account could easily have trustee's of their own to yet
a different account.
Choices should be a separate table not inheriting from Person. What choices are these, the ones relating to how a child is a trustee of their parent? If so, just set the choices table up like the trustee one, or perhaps add another field to the trustee table I mentioned to record the choices for a specific trustee link.
Cheers, Ben
smime.p7s
Description: S/MIME cryptographic signature
