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?
– Caffé
It would work, but I don’t know if it’s the best way out.
– Edi. Gomes
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?
– Caffé
I agree with Caffé, generic relationships will give you a headache in the future, especially when you need to index these relationships.
– Thiesen