-1

I have to add a new table according to some new requirements, the model currently consists of two tables: DETAIL and SUMMARY.

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, SUMMARY_ID , ESP_ID and 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 SUMMARY_ESP.

So if the row is the first one, it can be either 24 or 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 ESP_ID or PTY_ID.

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.

Current Idea

0

Edit

I do not know the specific business logic of how a new row being added to the detail table updates/inserts/relates to a row in summary_esp. However purely from a design point of view I do not think that adding the ID from SUMMARY_ESP to the DETAIL table as an FK will cause issues in terms of maintaining/updating/duplicating data. If you also removed the id_summary from DETAILS, as you could get this via join to summary_esp, then I cannot see any way a data anomaly could occur from this normalisation.


Old answer

It seems to me that additional requirement you have does not conform very well to your existing data model. So without redesigning the whole process with limited info from this is what stands out to me.

The issue I can see is with what you say here:

So according to this, SUMMARY_ID , ESP_ID and 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.

Also from what I gather that details to summary_esp is a one to one relationship and summary to summary_esp is a one to many relationship.

Going on what I can see in your question a cleaner option may be to put ID_DET into the summary_esp table and have it be a nullable FK to Details which is only filled in for whichever rows confirm to whatever criteria you would use to populate EXTERNAL_ID in reverse. This way it removes the summary_id dependency which leads to multiple rows and you would join on id_det to get your ESP_ID.

  • Thanks for your response, I'm trying to understand what you say and what about the case that two rows from detail are related to the same row in SUMMARY_ESP, basically the detail table is transactional, so it changes a lot according to some business logic, but the other two are more static , the only thing that changes is the priority – Alex Jan 4 at 14:17
  • @Alex Oh so I think I was seeing this the wrong way around. I thought SUMMARY_ESP would be a transactional table and DETAIL would be static. You say in the question that every detail has one summary and I see multiple rows for the same summary_id in SUMMARY_ESP. Adding that you have a case where you can have 2 rows in DETAIL which match 1 row in SUMMARY_ESP then I am. I am going to assume then that many details can have the same summary? As a 1 to 1 between detail and summary is impossible with the other constraints. – user169346 Jan 4 at 16:21
  • Exaclty, I just edited the question, to add some data sample – Alex Jan 4 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.