I have to add a new table according to some new requirements, the model currently consists of two tables:
The relation is that every detail has associated one summary, so now I need to add a new table called
SUMMARY_ESP, which has the a
FK ( SUMMARY) and two more columns, something like this:
ID | SUMMARY_ID | ESP_ID | PRIORITY_ESP | PTY_ID | PRIORITY_PTY 1 | 123 | 34 | 1 | 122 | 1 2 | 123 | 35 | 2 | 111 | 2 3 | 123 | 30 | 3 | null | null 4 | 1111 | 34 | 4 | null | null
Other tables info:
DETAIL TABLE ID_DET | AMOUNT | DATE | ID_SUMMARY | EXTERNAL_ID 1 | 1000 | 14/05/2018 | 1111 | 4 2 | 2000 | 18/07/2016 | 1111 | 4 3 | 1200 | 11/07/2017 | 123 | 1 4 | 1300 | 21/09/2018 | 123 | 2 SUMMARY TABLE ID_SUMMARY | PRIORITY| PROFILE | CLASS | AREA 123 | 1 | 1 | 5 | 3 1111 | 2 | 1 | 5 | 3 33 | 3 | 2 | 5 | 9 4 | 4 | 8 | 5 | 10
So according to this,
PTY_ID are unique, the thing is at some point to know the what is the
ESP_ID of certain detail, but since the relation is with
SUMMARY table, I have no idea which one was when it was added, so I was asked to create a new column to the
DETAIL table called
EXTERNAL_ID, so I can know what is the code from the
So if the row is the first one, it can be either
122 in the new column according to some previous logic, but I'm worried about the implications this might have in the future, because somehow I might be duplicating information, also I would need to make some weird logic in order to get the priority depending on whether it's
The new table along with
SUMMARY are somehow parameters table, their values do not change that often and only the
PRIORITY column would change,
DETAIL instead is more transactional, and it has insert and update everyday according to some business logic.
I was thinking of adding the ID of the new table as a FK to the
DETAIL table, but at the end would be the same, because it'll be hard to maintain and update would be harder, also it's like a circular dependency, so I'm kind of stuck with this , so any kind of help would be really helpful, below the complete model, with the current idea.
Also I can't add those new columns to the table
SUMMARY, because there could be more than one associated to the same code in that table and since it's the PK I cant add two rows with the same code.