1

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

  1. User
  2. Training
  3. 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.

1

For such scenarios, it helps a lot if you have a strict convention for using single attribute IDs as primary keys, all of the same type all over your database model.

With this preparation, you could model an universal "Relation" table with attributes "LinkedFromTable", "LinkedFromID" and "LinkedToTable" and "LinkedToID". The "LinkedFromTable " and "LinkedToTable" attributes must hold unique identifiers for the tables you refer to (maybe just the table names), and the LinkedFromID and LinkedToID take the primary key values of the referred data rows.

The operation "get me all the related entities" will be inexpensive due to proper indexing of your ID columns.

But beware: the drawback is that you loose referential integrity for these "manually modeled relations" completely - if you delete a data row, you have to check and update the "Relation" table manually. For a "write-once-read-many" system this may be fine, you have to decide for yourself.

  • The way I understand is that this modelling works really well when I have a primary entity from where the relationships emanate. In my current business scenario, the relationships are bidirectional. for e.g. we can have one entry for a relation between say "Training 1" and "Regulation 1". There could also be one more entry where the relationships are reversed ("Regulation 1" to "Training 1"). These two are same for the application. I can try to avoid this kind from my application code, but is there any better way to model this bidirectional relationships in database? – Sharath Chandra Jun 9 '14 at 18:32
  • @SharathChandra: there is no need for using a different model for bidirectional relations (well, maybe you should choose slightly different names for the attributes, like LinkTable1, LinkTable2, LinkID1, LinkID2, to get rid of the directional "from-to" description. You could choose the convention that LinkTable1 gets always the tablename which comes before LinkTable2 in alphabetic order, so you avoid to get 2 different records for the same relation. – Doc Brown Jun 9 '14 at 21:38
0

You can do the following:

Having a single entity table - Entity{id, type, ref_id_to_specific_entity_table}

Having a single relation table - Relation{id1, id2}

That way you can get all related entities to a given entity with a single query. Now if you also want to know the relation specifically without having to fetch the second entity, you can add an additional column as can be done with Relation{id1, relation, id2}.

This means you have to worry about consistency if you make changes, but as you mentioned, this is a read intensive, supporting system. Might not be a problem.

0

Normalization has many benefits to RDBMS, but it also has some draw-backs. Performance for multiple and complex relations can be hindered when selecting data.

It seems like the main purpose of this app is to provide reporting and not a lot of data alterations by the typical user. You can keep your current structure to handle all the data entry/setup of the various entities by some administrator of the information, but that doesn't mean those getting the help information have to query these exact same structures.

Depending on how much your help items are altered, you can very easily prebuild denormalized reporting tables. They are basically the results of your queries without running the same query over and over with each user request. Each type of request would have its own table. The table gets rebuilt/updated/refreshed when help items are changed. You'll have to code this.

There are plenty of other caching tools and some are built into different database products. This can get more costly and complicated. I would start with this. Maybe you can just pick one and see how it works. Whether your app is using the results of the actual run of the query or just a simple query of a prebuilt table, it's working with the same data, so very few changes need to be made in your app as you migrate more and more of these to some prebuilt table.

  • Thanks for the inputs. What you are suggesting is wither to build de-normalized data or build cached views of all relationships and keep them ready for the user query. Caching only relationships is good however needs to be done per user (which should be ok in terms of memory but needs to be thought through in terms of cache coherence across WFE). – Sharath Chandra Jun 9 '14 at 18:45

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.