fun with referential integrity

[Vladimir Sekissov]

Good day,

etxuwig> .... or should I make it easier on myself and simply disallow
etxuwig> cyclical dependencies? This seems like a fairly limiting
etxuwig> restriction. How do the real pros do it? Does anyone know?

For my opinion cyclical references are a bad thing. I usually model
bidirectional relationships using another table D with composite
primary key and foreign keys to B and C.

Best Regards,
Vladimir Sekissov

etxuwig> I decided to actually use my rdbms contrib today. (:
etxuwig> 
etxuwig> I had a number of tables that I wanted to connect in a
etxuwig> multi-level pattern of cascading delete. Due to some bad
etxuwig> design decisions previously, I ended up having to define a
etxuwig> cyclical pattern of dependencies, but pressed on with
etxuwig> unwarranted confidence.
etxuwig> 
etxuwig> (Just to clarify, what I was aiming for was this: if one
etxuwig> deletes an object A, then all B and C should also be deleted
etxuwig> if A.id == B.a_id, and B.id == C.b_id. The cyclical
etxuwig> dependency was that one should also delete B (where B.id ==
etxuwig> C.b_id) anytime one deletes C (don't ask).)
etxuwig> 
etxuwig> I discovered that rdbms did not, in fact cascade down more
etxuwig> than one level. That is, when deleting A, all related B were
etxuwig> deleted, but the Cs remained. I went into rdbms and made the
etxuwig> obvious fix, which immediately threw me into an endless loop
etxuwig> -- not good.
etxuwig> 
etxuwig> After a few hours, I realised that the solution for
etxuwig> cascading delete was obvious: delete A first, then do the
etxuwig> cascade, and the same thing downwards. This worked, too.
etxuwig> 
etxuwig> The real problem is of course with cascading update, which
etxuwig> suffers from the same issues, but requires a different
etxuwig> solution. I have to maintain a visited list, I guess, if I
etxuwig> am to allow for the possibility of cyclical dependencies. Of
etxuwig> course the visited list must support the notion of nested
etxuwig> transactions, which means several levels of visited lists.
etxuwig> 
etxuwig> .... or should I make it easier on myself and simply disallow
etxuwig> cyclical dependencies? This seems like a fairly limiting
etxuwig> restriction. How do the real pros do it? Does anyone know?
etxuwig> 
etxuwig> /Uffe
etxuwig> -- 
etxuwig> Ulf Wiger, Senior Specialist,
etxuwig>    / / /   Architecture & Design of Carrier-Class Software
etxuwig>   / / /    Strategic Product & System Management
etxuwig>  / / /     Ericsson AB, Connectivity and Control Nodes