Good or bad idea? One join table for many possible pairings

[TheTominator]

I have a project with over 10 tables that are linked together with one to many and many to many relationships.

Two of the tables are directly connected to at least five of the other tables.

This comes about because one of the tables contains financial information that can be associated with individual records in other tables. Another table represents a library of media (container fields) that can be associated with individual records in a handful of other tables.

Join tables are required in some cases instead of linking directly to return-separated ids in a text field to support the type of reporting and analysis needed.

Instead of creating many dedicated join tables, I would like to use one "connection" table that contains enough fields to permit any pairing.

COMMON

_pk_common_id

_fk_a_id

_fk_b_id

_fk_c_id

_fk_d_id

_fk_e_id

...

To allow arbitrary joins between tables A, B, C, D, E ...

In practice each record in COMMON would have only two foreign key id fields occupied at a time.

My question:

Is there any reason why I shouldn't do this?

-Tom

Edited August 14, 2009 by Guest

[T-Square]

Tom--

This certainly is a different problem. I imagine that from a technical perspective, your approach would work probably fine.

I wouldn't do it, though, because it will complicate your solution and make it harder to manage on an ongoing basis. Using standard join tables follows traditional database design practices, and makes it easier for others (and you, later on) to understand what you're doing.

Your solution also doesn't really gain you anything with regard to data storage, since you always will have two key fields per join record.

Ultimately, I think the real question is: How many of these arbitrary joins are you likely to use, really?

In my own projects, I have found that there is a phase where I am in Global Solutions Mode, and I work out a Grand Unifying Solution to Every Conceivable Oddity. Unfortunately, the GUSECO ends up being obtuse, and accounts for problems I don't really have. And in that complexity, I have years of nightmares trying to remember what I was doing and why. In your case, I wonder how many of the arbitrary joins you mention will actually come up, and if you couldn't just use standard join tables for those. This is related to my second question...

How do you propose to make use of every one of these arbitrary joins?

On the one hand, you could use your Grand Junction Table in scripts to locate particular sets of data on the far side of GJT. I envision some messy scripts, though.

On the other hand, you could leverage the data in the Relationship Graph. But if you do that, you don't really gain anything over separate join tables.

This is just my own initial opinion on this.

Cheers,

David