Single join table or multiple

[crayfish]

I am trying to decide whether to have a single join table with a field in each record describing what tables it joins or separate tables for each kind of join relationship.

Normally I would do something like this:

Class (table)

Class_Enrollment (join table)

Students (table)

Books (table)

Books_checked_out (join table)

Students (table)

In the database I am currently designing, there are numerous kinds of join relationships, so it seems like it would be a cleaner design, if instead of having multiple join tables, I simply created one join table with two keys and a field that describes what kind of relationship they keys have.

Join_Table Fields:

Key_1

Key_2

Type_of_Join

So the above database would now look like this:

Class (table)

Join_table (table)

Students (table)

Books (table)

Join_table (table)

Students (table)

The Type_of_Join field in the first example would be (Class-Students) and the Type_of_Join in the second example would be (Books-Students)

Am I missing anything here? If I use the single join table, will I set myself up for any challenges down the road?

Any help would be greatly appreciated.

[TheTominator]

For one of my large solutions I went with a single Join table, but I created a separate field in the join table for each table it can connect to. This method has worked for me so far. Aside from additional fields, I think it is easier to implement and keep track of.

In your situation it would be.

JOIN

-----

_kp_JOIN_id

kf_STUDENT_id

kf_CLASS_id

kf_BOOK_id

Only two of the kf_*_id fields would be non-empty at a time.

This lets you connect up the JOIN table unambiguously in the relationship diagram and not have to worry about the value of the Type_of_Join field being handled correctly. I'll admit that I considered but didn't implement the Type_of_Join-using method in my solution so I'm not sure how it would fare in the long run.

Note that in the examples you have provided so far, I would think the common JOIN table is not warranted unless there is more complexity such as a Books-Classes connection.

[Vaughan]

I cannot see any benefits, only downsides.

It's not like a table is a lot of work. Why muck around with hacks?

Determine the data architecture, build it, move on to the next challenge. Creating a way to use one table for multiple joins will only complicate the solution and make maintenance more difficult in the future.

I think you're focusing on building a "clever" design instead of solving the client's problem. I cannot see a client saying "Gee, you spent more time working out a way to save a couple of join tables than it would have taken to leave them in *and* made the solution harder to maintain at the same time. Thanks."

I have seen this before and I'm working on a solution like this now. It's common when there has been no written specification of the client's needs -- not even a one page spec. The developer thinks "I've got some spare time, I'll do something that looks like fun" instead of building something that will solve the client's problems. If it's not on the spec then don't spend time on it, no matter how fun it might be.

[frinholp]

As at present I am re-designing my table structure, this was going to be my next question.

Vaughan - you have been a saviour twice in one day. Once without knowing it - until now of course!

Cheers

Edited July 9, 2010 by Guest