Avoid Circular Dependency

Question

I am developing a travel management application. The design in question is something like following :

Each person in a tour is designated as a Traveler. Each Traveler has a Passport. Now, a Traveler can be a MainMember or a SubMember, depending on whether or not he is the head of the family. A MainMember decides stuff like TourPackage, total amount for his travelling family, etc. A SubMember is dependent on the MainMember while travelling. So, if a MainMember is deleted, all its SubMembers have to be deleted as well.

So, A Traveler has a Passport. (One to One relationship) A Traveler is either a MainMember or a SubMember. (one-to-zero/one between Traveler-MainMember and Traveler-SubMember) A MainMember may have several SubMembers. (one-to-many) A SubMember has only one MainMembers. (many-to-one)

My current ERD is something as follows.

Traveler ER Diagram

As you can see, the three tables - Traveler, MainMember and SubMember - have formed a circular dependency. I am not sure if it will hurt my application, though. If I delete a Traveler who is a MainMember, then 1. A record from Traveler is deleted. 2. Its relevant MainMember record is deleted. 3. The SubMember records dependent on MainMember are deleted. 4. The Traveler records of SubMembers are deleted.

Though it doesn't seem to be a problem, as a Traveler-MainMember delete will always delete only Traveler-SubMember(s). Still, I have a bad feeling about this.

Can anyone guide me to a better design?

UPDATE -

While waiting for the replies, I came up with another design, based on @Daveo's reply. Basically, Traveler contains self-referential foreign key. It would be used by the SubMember records to identify their parents.

Here is the ERD for that.

ERD with Self Referential table

Now, as there was no issue of circular dependency in my previous design, as pointed by @Branko, I would like to know which design is better?

Also, which design would be better to implement through Hibernate? I think the second approach might lead to complexity while implementing through Hibernate.

I would also appreciate some pointers regarding the implementation patterns (inheritance in Hibernate entities, etc.) for the design that you prefer.

use inheritance for MainMember and SubMember (extending Traveler) — guido, Feb 21 '13 at 09:54
@guido - That, indeed, is my thought when I implement that POJO entities. But, how does that solve the circular dependency in the database design? Or I have misunderstood your statement. I will appreciate if you elaborate it as an answer. — bhootjb, Feb 21 '13 at 10:41
have a look here http://openjpa.apache.org/builds/1.0.0/apache-openjpa-1.0.0/docs/manual/jpa_overview_mapping_inher.html#jpa_overview_mapping_inher_tpc — guido, Feb 21 '13 at 11:55
@ypercube - Yepp. Please refer to the updated question, and state your opinions. — bhootjb, Feb 21 '13 at 14:10
Regarding the second design: if you're gonna have separate tables for _main_ and _sub-traveler_, why not utilize that and properly model the relationship between them, so no two _sub_ nor two _main_ travelers can be connected? The circular reference (this time, for real ;) ) from `Traveller` to `Traveller` doesn't honor these restrictions. I think @Daveo's idea was to merge all 3 traveler tables into one, which would be "all classes in same table" approach for implementing inheritance, and yes, cause a lot of NULLs, but could have some advantages as well (see the links in my answer). — Branko Dimitrijevic, Feb 21 '13 at 14:22
@BrankoDimitrijevic OK. I see your point. Regarding Daveo's idea, I knew what he meant, but I borrowed only the self-referential part to make my second design. — bhootjb, Feb 21 '13 at 14:33
@BrankoDimitrijevic - Now, my understanding in inheritance states that the first design follows vertical inheritance, which seems to be considered as an elegant, but quite inefficient approach. Will that matter in only three tables of my database? Or is that "premature optimization" on my part? — bhootjb, Feb 21 '13 at 14:35
_"but quite inefficient approach"_ - It is less efficient in some cases and actually more efficient in others. You'll have compromises either way, the only question is what you think is a better tradeoff in your particular case. Personally, I'd recommend you stick with your original approach, unless you identify a specific reason not to. — Branko Dimitrijevic, Feb 21 '13 at 15:06

score 7 · Accepted Answer · edited May 23 '17 at 12:09

As you can see, the three tables - Traveler, MainMember and SubMember - have formed a circular dependency.

No, this is not a circular dependency - no "node" in this graph can reach itself by following graph "edges" in their proper direction¹. It would be more accurately described as "merged" or even (proto) "diamond-shaped" dependency.

And no, it will not hurt your application.²

You are effectively implementing inheritance (aka category, subclassing, subtyping, generalization hierarchy etc.), using "each class in separate table" approach. This approach is clean, but cannot guarantee presence³ and exclusivity⁴ out of box. There are ways to amend it so it does, and there are other implementation strategies for inheritance that can, but they have their own pros and cons.

In my opinion, your model is just fine for what you are trying to accomplish and enforcing the presence/exclusivity at the application level is probably lesser evil than suffering the cons of trying to enforce it at the database level.

¹ "Direction" is from referencing to referenced table. Traveller doesn't reference MainMember nor SubMember and therefore breaks the cycle.

² Unless you are on a DBMS (such as MS SQL Server) that doesn't support referential actions on such "merged" dependencies (in which case you'd need to implement ON CASCADE DELETE through triggers).

³ Traveller cannot exist alone (cannot be "abstract"), it must be MainMember or SubMember.

⁴ Traveller cannot be both MainMember and SubMember.

Thanks for clearing this up. Even I was dubious whether it was a circular dependency (I had even drawn the "proto diamond shape" in air while bathing. ;-)). Your answer has confirmed it. — bhootjb, Feb 21 '13 at 14:06
Please have a look at another design that I made later on. I would like your opinion on which design is better, and why it is so. — bhootjb, Feb 21 '13 at 14:07

score 1 · Answer 2 · answered Feb 21 '13 at 10:05

1

I would only have two tables in the database.

Traveller and Password

Traveller will have a Parent_Id field which will link back to the Traveller table and will store who the Main/Head traveller is. Also store fields common to either Main/Sub member in this table like contact number

Then using inheritance and ORM create two different classes in your actual application. MainMember and SubMember MainMember would be all rows in Traveller where Parent_Id is null SubMember would be all rows in Traveller where Parent_Id is not null

answered Feb 21 '13 at 10:05

Daveo

19,018
10
48
71

But this approach will end up in a lot of null values in the single Traveler table. MainMember has 6 specific characteristics, and SubMember has 2. So, MainMember columns will remain null for SubMember, and vice versa. That was the main reason that I separated the two tables from Traveler - to store their special characterstics. – bhootjb Feb 21 '13 at 10:37
what's the problem with null values? – Daveo Feb 21 '13 at 10:46
Well. Let us consider a record of SubMember. 6 out of 17 (yeah, Traveler has a lot of properties) columns will essentially remain null, as those properties belong to MainMember. Also, a SubMember feels out of place in this situation - sticking MainMember's properties along with it. That is where my concern lies. What are your thoughts about it? – bhootjb Feb 21 '13 at 10:51
2

Each approach has advantages and disadvantages what it will depend on are your requirements. e.g. are you using an ORM, will other systems access the database or just this one, etc. See http://support.apple.com/kb/ta26729?viewlocale=en_us&locale=en_us for a explanation of the 3 different approaches you can take. I was suggesting "Single Table Mapping" which seems the easiest/quickest to develop and maintain – Daveo Feb 21 '13 at 12:55
2

also another page that explains it is here http://books.google.com.au/books?id=aCmI3jEAWC0C&pg=PA32&lpg=PA32&dq=vertical+inheritance+vs+horizontal+inheritance+database&source=bl&ots=KnJj1yxMoe&sig=WIBpaAjhtpoo8juHstXLQYbGCXE&hl=en&sa=X&ei=gxgmUef4HojmmAW05oCACQ&ved=0CHoQ6AEwCQ#v=onepage&q=vertical%20inheritance%20vs%20horizontal%20inheritance%20database&f=false – Daveo Feb 21 '13 at 13:00
I grabbed your clue of self-referential table, and came up with a design that implements it while also preserving my design. Tell me if it is a better design or not. – bhootjb Feb 21 '13 at 14:09

Avoid Circular Dependency

2 Answers2

Linked