How to design database having multiple interrelated entities
- by Sharath Chandra
I am designing a new system which is more of a help system for core applications in banks or healthcare sector. Given the nature of the system this is not a heavy transaction oriented system but more of read intensive.
Now within this application I have multiple entities which are related to each other.
For e.g. Assume the following entities in the system
User
Training
Regulations
Now each of these entities have M:N Relationship with each other.
Assuming the usage of a standard RDBMS, the design may involve many relationship tables each containing the relationships one other entity ("User_Training", "User_Regulations", "Training_Regulations"). This design is limiting since I have more than 3 entities in the system and maintaining the relationship graph is difficult this way.
The most frequently used operation is "given an entity get me all the related entities" . I need to design the database where this operation is relatively inexpensive.
What are the different recommendations for modelling this kind of database.
