We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system.
To illustrate the question, two examples would be:
ass_users_eventswould contain only the
Event_IDcolumns, both with foreign key relationships.
ass_projects_taskswould contain only the
Task_IDcolumns, both with foreign key relationships.
NB1: Every object table actually uses a combination of an Auto-Incrementing Integer Primary Key, plus a UUID column with a unique index which is the actual record ID. For the purpose of this question, we're using only UUID's so there is no chance of collision.
NB2: The reason we use this format rather than a straight Foreign Key column/index is that the reality is much more complex than this example, there are many different joins to many different tables and we don't want the ORM doing a lot of unnecessary work every time a record is loaded.
The problem is we're starting to create these associational tables for pretty much every new object type in the system for many of the other existing objects in the system and in the long run this doesn't seem sustainable, we'll end up with hundreds of association types.
A potential solution we're considering is getting rid of ALL of the current associational tables and instead creating one table with the following structure:
obj_2_type. Every column would be indexed, likely as composite indexes (i.e.
INDEX object_1 (obj_1_type,obj_1_id) and
INDEX object_2 (obj_2_type,obj_2_id)).
The examples above in the ass tables would become:
This solution gives us the flexibility to build as many relationship types as we want between different objects, and there's ample indexing on the ass table to remain performant. My question is is there a downside to using indexing only during joins, vs having defined foreign key relationships in smaller tables, but potentially hundreds of them?
EDIT: I think there are some misconceptions below so this might clear up a couple things:
I use polymorphic object structures, but every object is stored in it's own table, i.e. user, product, category, event, etc.
As far as the proposed ass table, it will only have 4 functional fields, with very straightforward datatypes (plus one
ai_colas primary). The datatypes would be varchar(10) for the
typecols and CHAR(32) / BINARY(16) for the id's. SELECT's will only ever have one join, to the ass table, i.e.
SELECT event.* FROM event INNER JOIN ass ON ass.obj_1_type = 'event' AND ass.Object_ID_1 = event.Event_ID AND ass.obj_2_type='location'
there's no reason I would ever ask for two of these objects at once, I would only be filtering results from one table by existence of ass links.
UUID's are the real Unique ID's here, but every table uses
ai_colwhich is an INNODB construct for increasing the performance of their clustered index.
This is a read heavy environment, I'm not near as concerned with insert/delete performance.
We use handlersocket for simple read AND write queries removing the SQL overhead.
The integrity of this table is acceptable at Eventually Consistent in this specific use case.
EDIT 2: To make this crystal clear, here's the why... if I have 200 objects each in their own tables, that I want to be able to link in all possible ways, I would either have to have 199 foreign key relationships in each object table, or I would have to create 40,000 tables with all the possible combinations and then all the business logic to go with that. Using a the method we're considering, there's one table and I have RI taken care of at the application level, which I indicated is ok at eventually consistent.