September 7, 2008 at 4:59 pm
Hi
I've hit upon a bit of a problem and I'm hoping there's a simple solution.
I've got 3 tables tPerson, tPersonChild, tChild.
tPersonChild has 3 fields PersonID, ChildID and Relationship (ie Mother, Uncle etc)
I have a cascading delete from tPerson to tPersonChild which works.
BUT, if I introduce a cascading delete between tPersonChild and tChild, the logic seems a bit.....wrong?
My reasoning is this, if PersonA had 2 sons (ChildA, ChildB) then all works well and if PersonA is deleted, so are ChildA and ChildB.
BUT, if there was also a PersonB who was the uncle to ChildA, you wouldn't want ChildA to be deleted just because PersonA was deleted.
If there is a standard way around this problem, I'd love to see it!
Cheers guys
September 7, 2008 at 5:16 pm
Seems to me like you only need on person table where you keep both parents and child in it.
But then again I don't have enough info to really guide you in the right direction.
What are the requirements on that db?
September 8, 2008 at 8:15 am
I assume that a child is also a person. If that is not true then most of what follows will not help you.
You should have a person table with all Pesons in it.
You should have a PersonsRelation table with 4 main fields. These are ParentId, ParentRole, ChildId, and ChildRole. You may also have other relationship attributes such as CreatedDate, UpdatedDate and perhaps a surrogate primary key. The natural key of the relationsip table is a composite of ParentId, ParentRole, ChildId, and ChildRole.
I also usually set up a separate Role lookup table with all allowable roles (e.g. Parent, Child, Uncle, Aunt, Niece, Nephew, Step-Brother, Step-Sister, ....)
The ParentId and ChildId are foreign keys to the Person table and can be defined with On Delete Cascade. That way when you delete a person you will delete all of that person's relationships but not the other Persons participating in the relationship.
If you really want to delete all persons that are participating in any relationship with the deleted person then you will need either a trigger or to control the table with a set of stored procedures for Insert/Delete.
I am not sure you really want to do that however. You could lose meaningful relationships and persons. You could also set up a potential performance nightmare as the number of 2nd level, 3rd level, 4th level, etc. deletes climbs.
If your rules include that a person must be a participant in at least one relationship then you can do a couple of things. For example, you could have a periodic job to delete orphan (pardon the pun) records from the person table.
September 8, 2008 at 9:17 am
A single-table hierarchy should handle this, unless you are tracking something more complex than geneology.
If you had a table with PersonID, Gender, FatherID, MotherID (and any other columns you need, like Name), you wouldn't need any of the other relationship stuff.
If you do need more complex relationships, move those to another table, but keep Mother and Father in the main table. Until we start genetically engineering human beings, a person can only have one biological father and one biological mother.
With that, you can determine who is the uncle of whom pretty easily. Find all rows where at least one parent is the same as a grandparent, and where Gender = 'M', and you have the uncles. Go one generation higher and you have great uncles. And so on.
Cousins, etc., can all be queried the same way.
If you need to track legal relationships (as opposed to genetic ones), you can do that in a separate table. Make sure it has DateStart and DateEnd columns, and that it does not include any genetic relationships. Should only be things like adoptions, marriages, etc.
I'm not sure under which circumstances you would "delete" a person, unless their data should never have been in the database in the first place or something like that. If you do, I'd set any FatherID/MotherID columns that were that person's ID to Null, and delete them completely out of the legal relationships table, but wouldn't chain it any further than that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 8, 2008 at 1:09 pm
Many thanks guys, you've made me rethink my approach to this. My reason for having the tPerson and tChild tables seperate is that the tPersons are parents 99% of the time and tChild are always children of tPersons.
The info stored on tPersons is vast but tChild info is only about 10 fields which is why I wanted to keep them apart.
I've worked on projects in the past where there's a single person table for all, so I might take another look at that approach....
Thanks again to all for taking time to reply....
September 8, 2008 at 1:25 pm
You can always test that design if you want... just be sure that a children will never be considered a person in the system so that a major refactoring is avoided.
I'd go like this :
Persons cascades to PersonsChildren cascades to children.
Then check if you can add a cascade from children to personsChildren. If that gives you a circular refference error, then just manually code the delete in the same SP.
September 9, 2008 at 9:02 am
mitch (9/8/2008)
Many thanks guys, you've made me rethink my approach to this. My reason for having the tPerson and tChild tables seperate is that the tPersons are parents 99% of the time and tChild are always children of tPersons.The info stored on tPersons is vast but tChild info is only about 10 fields which is why I wanted to keep them apart.
I've worked on projects in the past where there's a single person table for all, so I might take another look at that approach....
Thanks again to all for taking time to reply....
How many characteristics (columns) are you storing in the tChild table that are different from the tPersons table, and how many are the same? I'm going to guess that the columns are at least highly similar if not identical. If that's true, it should be one table.
The method I like best is to have a single People table, and then have unique columns in sub-tables. You might have a tPeople table, and then tPeopleParents and tPeopleChildren, to store the characterics that parents have which children don't and vice versa. If you do this, plan carefully, because you don't want to have to deal with things like having "Place of Work" in the Parents table, and then run into a child who is employed and the inability to record that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply