Best cardinality for a table with many relationships

Question

Best cardinality for a table with many relationships

Navigation

#1 by (2 votes)

1

Well, I have a cash flow table , this table can be related to a process, order, account receivable, payable ... among several other tables that relate to and which can result in a posting in the box, the problem is when creating the relationships in this table, I do not know if I create several foreign keys, one for each table (in cash flow), or a relationship table (HAS) that are the alternatives that I know, I believe that it would be a problem to maintain the bank one of these two, since in the system the modules are dynamic and at any moment there may be a new one, and that you need to throw in the cash flow and have this release tracked related).

My idea would be a single relationship table that would serve all relationships, and that would be linked to another that would indicate the relationship table, 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 do not know if it would work and if it would be viable to look at the data modeling and performance standards, I see that some systems like SAP all relate to everything (or almost), but I have no idea how they do it in the bank, but surely they must have found a good solution, thank you in advance.

mysql modelagem

asked by anonymous 08.01.2015 / 21:53

1 answer

Pass connection string to dataset via code Android SQLServer Connection Failed

score 2 · Answer 1

There is a solution that was found by some frameworks but I'm not sure if that might be the solution to your problem.

The idea is to make the table that will relate to several other tables actually have a generic relationship, that is, a relationship that allows any other table to be bound to it.

To do this it is necessary that you have two fields, one that gets the name of the related table and another that receives the value of the primary key 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 here whenever you want to relate a row from any table to the flow table you have to enter the table name the primary key of it.

Imagine that you want to relate line 127 of the contas_a_pagar table 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 counter is SELECT that can not be done easily using JOIN. To do the SELECT it is necessary to do it in two steps.

The first SELECT:

SELECT * FROM fluxo;

There you need to iterate over the rows and save all the tables and IDs. Then you build a SELECT to get the relationships:

SELECT * FROM contas_a_pagar WHERE PK_contas_a_pagar = 127;

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

Django uses this kind of approach to solve the problem of Generic Relationship.

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