HANA dat modeling - View for Querying Records that have tags across multiple categories

[former_member515688]

I have a table that stores different software services a company offer. The services are tagged by the Industry it serves, the LoB it belong to, and the technology involved in the service . The service can have multiple tags on each of Industry,LOB and Technology. For eg: Following could be the master data:

And a transaction data could look like this :

I need to create a view that can be used to query data by Industry/LoB and Technology tags. For time being I've Left outer joined all tagtoService relation tables(service-technology, service-LoB,Service-Industry tables) to the services transaction table. But this goes for a huge number of records as it is possible to typically have one service tagged to upto 10-15 industries and technologies.

Just wanted to know what is the optimal way to model this data so that i have provision to query for a service by all of the three tags right from within one view.

I use HANA HDI container in a CAP application and this view is to be exposed by an odata service to use in UI5 where the tag filtered will be applied and passed for query.

[lbreddemann]

And "tags are back" ... at least it looks the way.

From this first answer, you can probably guess, that you are not the first one to model "tags".

Using your favorite search engine there are plenty articles etc. to be found.

Here are two that I think will give you some ideas:

http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/: this is a comparison of different approaches with links to those approaches. As you seem to use n:m-joining tables, your approach would fall under "toxi"-solution.

https://stackoverflow.com/questions/48475/database-design-for-tagging: a good discussion including a link to what "relational division" is and how it relates to this problem.

Now having a quick look at your approach, I wonder why there are three different tables for tags and an m:n-join table for each of those.
If you keep all tags in just a single table, there is only a single intermediate join necessary.

And this will have a big impact on query performance, once the queries start to ask for data that has either this tag or these five but not these two or ... (you see where this is going...)

For your data model, it shouldn't matter whether you are using HDI and UI5 - solve the problem on DB level and the rest can build on that.