DB Optimization with 2 to the power of N - Approach clarification

Question

This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know whether this a better approach for the explained scenario in the link below,

DB Optimization Blog Link

If there are another better option kindly advice.

Answer 1

So that solution is:

1. Creating a Bit Array in the user table to register a user to a health plan.
2. Then mapping the binary digit position to the health plan table.

Benefits:

1. Very little storage required, You might save $20 over the lifetime of the project.
2. Faster updates/deletes albeit perhaps not measurable.
3. Faster queries when not selecting by Health Plan

Problems:

1. The maximum number of distinct plans has to stay small. (32 or 64 depending on field size)
2. Nowhere to store plan start date, plan end date or other relevant data that you will probably need.
3. You need to decode the plan every time you use or apply it using bit operations.
4. There is no quick and easy way to do generic type queries that link the tables quickly.
5. Slower queries for health plan driven queries (i.e. all 'H10' Plans) without special indexes and query tools.

Issues with the original premise:

1. When subscribing to a new plan / leaving a plan you can just insert or delete the 'changed' records.

   Insert will add a new record ('U1',3).

   Delete will only need to delete the relevant record User_Health_Plan_Mapping(U1,10)

2. You seem to assume that db optimization is about minimizing the storage, it is more about the following.

   • Placing data so that it is easy to understand.
   • Storing it long term (think 5 generations of the code that talks to it)
   • Making the data extensible, supportable, searchable, reliable, secure, robust, backed up.
   • Optimizing the whole ecosystem (time to market, development time, operational time, hardware cost, business process, call center hours)

3. Optimizing the tables / indexes for the specific use-cases that is required of your application. Which is not about processor power, but indexes, direct access to hard disk and cache optimization (unless you have a trivial sized database that can stay in memory).

Answer 2

In addition to Andrew Russel's great answer, see also When is it better to store flags as a bitmask rather than using an associative table?.

I'm guessing a very common query would be "find all users who are subscribed to healthplan 1"; in this design, that would be a horrible query without all sorts of special indices. Maintaining those indices on insert/update would almost certainly negate the benefits of the reduced number of insert/deletes.

If you really do have a performance problem with the insert/update methods on your table, buy better hardware, look into partitioning, or see if you can archive data.

Answer 3

It's called a bit mask, and while it does save space, it is otherwise a bad idea.

Consider how you might join based on child records - a real hassle and it would perform poorly.

DON'T TRY THIS AT HOME!

Answer 4

Thanks all for providing your comments and suggestions.

The approach mentioned in http://www.nverma-tech-blog.blogspot.com/2011/11/logical-approach-to-optimize-database.html has it's own merits and demerits:

Merits

1. Reduces the number of INSERT and DELETE operations.
2. Does not take much decryption time to separate health plans for a user.

Demerits

1. 2 to power n series grows exponentially. The maximum number of plan mappings must stay small to fit the maximum size of data types.
2. Decryption of users specific to a health plan will take extra processing power during searches.

Per my requirement, I had at max 50-70 mappings per single user and did not require any search specific to the mapped items. Hence, I opted for this one.

More ideas and comments are most welcome.