Better cardinality for a table with many relationships

Question

Well, I have a table of cash flow, this table can be related to a process, request, bill to receive, bill to pay... among several other tables that relate and that can result in a launch in the box, the problem is when creating the relations in this table, I do not know if create several Foreign Keys, one for each table (in cash flow), or a relationship table (HAS) which are the alternatives I know, I believe it would be a problem for bank maintenance one of these two, since in the system the modules are dynamic and at any time there may be a new one, and need to launch into the cash flow and have this launch tracked (related).

My idea would be a single relationship table that would serve for all relationships, and that would be linked to another that would indicate what the relationship table would be, Ex:

Relationship table:

CREATE TABLE IF NOT EXISTS `SIS_REL` (
  `PK_rel` int(11) NOT NULL,
  `FK_rel_parent` int(11) NOT NULL COMMENT 'a',
  `FK_rel_child` int(11) NOT NULL COMMENT 'b',
  `FK_table_parent` int(11) NOT NULL COMMENT 'c',
  `FK_table_child` int(11) NOT NULL COMMENT 'd',
  PRIMARY KEY (`PK_rel`)
);

Table that defines what the relationship will be:

CREATE TABLE IF NOT EXISTS `SIS_rel_group` (
  `PK_rel_group` int(11) NOT NULL AUTO_INCREMENT,
  `rel_table` varchar(30) NOT NULL,
  PRIMARY KEY (`PK_rel_group`)
);

But I don’t know if it would work and if it would be feasible to see the side of data modeling and performance patterns, I see that some systems like the SAP everything is related to everything (or almost), but I have no idea how they do in the bank, but surely they must have found a good solution, I thank you from now on.

Answer 1

There is a solution that has been found by some frameworks but I’m not sure if it might be the solution to your problem.

The idea is to make the table that will have relation with several other tables actually have a generic relation, that is, a relation that allows any other table to be linked to it.

To make this work it is necessary that you have two fields, one that gets the name of the related table and the other that gets the value of the primary key that is being related.

Example:

CREATE TABLE IF NOT EXISTS `fluxo` (
  `PK_fluxo` int(11) NOT NULL,
  `rel_table` varchar(64) NOT NULL,
  `rel_id` int(11) NOT NULL,
  PRIMARY KEY (`PK_fluxo`)
);

From there whenever you want to relate a row from any table to any flow table you have to enter the table name the primary key from it.

Imagine you want to list row 127 of the table contas_a_pagar to a stream, you have to execute the following query:

INSERT INTO fluxo (rel_table, rel_id) VALUES ('contas_a_pagar', 127);

As always, there are pros and cons. And the main one against is SELECT which cannot be done easily using JOIN. To do SELECT you need to do it in two steps.

The first SELECT:

SELECT * FROM fluxo;

Then you need to iterate over the rows and save all the tables and Ids. Then you build a SELECT to get the relations:

SELECT * FROM contas_a_pagar WHERE PK_contas_a_pagar = 127;

And in the application you have to join the results using the primary key of contas_a_pagar.

Django that kind of approach to solve the Generic Relationship problem.

I hope you helped, or at least server inspiration for you to solve your problem.