Better cardinality for a table with many relationships

Asked

Viewed 691 times

1

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.

  • How about creating in each table (process, request, receivable, payable account) a cash flow relationship instead of the reverse?

  • It would work, but I don’t know if it’s the best way out.

  • 1

    You reported the problems of your two options and I can add a few - first option: multiple null fields in the cash flow because they don’t make sense for all entities; second option: an accidental complexity in data modeling, that would take the tables away from business modeling (a generic relationship table would hardly describe the business well). What would be the problem of the option I suggested?

  • I agree with Caffé, generic relationships will give you a headache in the future, especially when you need to index these relationships.

1 answer

2

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.

  • I am doing some tests with this solution, it seems to be a good idea, we are looking for failures that may happen in the future using this model, soon I will give a feedback.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.