Ben, Your schema doesn't reflect the actual business case. The database must store information about people who are in various roles regarding the respondent (account holder).
1. Accounts are for a family. Spouses would have only one account; never separate ones. 2. A trustee is not necessarily a child; he/she could be the respondent's brother or sister or a friend or even a financial institution. 3. The child_of column describes a child; not a trustee. In some cases, since a child could be named as a trustee, a trustee is a child. 4. A respondent must make other choices, in addition to designating trustees, such as beneficiaries and guardians. That's what the choices table is for. Edgar On Apr 28, 12:12 pm, Ben Bangert <[email protected]> wrote: > 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 > 3KViewDownload --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
