locked
Modify existing table design or Group by in UI?? RRS feed

  • Question

  • User-699381365 posted

    Hello,

    I am in the process of creating/modifying the existing table design for an workflow. I Would need a suggestion to group by the conflicts. I am thinking to maintain the same structure and group it/resolve in UI. Or should I go for any other sub table??

    Existing workflow - One Service (One service id with one servcie code) can have multiple Conflicts (One to many)

    New workflow - Multiple service id (with one service code) can have multiple Conflicts for each service id

    the bottom line is, its all the same service and I have to resolve all the conflicts at once for a service.

    Service table                      Conflicts table

    Service ID(PK)                    Conflict Id (PK) 

    ServiceCode                        ServiceID (FK)           

    THanks,

    RG

    Tuesday, April 21, 2020 7:06 PM

All replies

  • User-719153870 posted

    Hi rgovindarajan,

    New workflow - Multiple service id (with one service code) can have multiple Conflicts for each service id

    In the new workflow, the relationship between Service table and Conflicts tables has become many to many relationship. In many to many relationship, the FK constraint will be invalid.

    I'm not sure what you will do to "maintain the same structure and group it/resolve in UI", but i think modify the exising tables would be a better choice and easier for maintenance and querying.

    Create a middel table for your Service table and Conflict table and store the relationship in it. Please check How to Handle a Many-to-Many Relationship in Database Design for more detailed information.

    Best Regard,

    Yang Shen

    Wednesday, April 22, 2020 2:30 AM