Modify existing table design or Group by in UI??

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

Answer 1

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