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

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.

So that solution is:

• Creating a Bit Array in the user table to register a user to a health plan.

• Then mapping the binary digit position to the health plan table.

Benefits:

• Very little storage required, You might save $20 over the lifetime of the project.

• Faster updates/deletes albeit perhaps not measurable.

• Faster queries when not selecting by Health Plan

Problems:

• The maximum number of distinct plans has to stay small. (32 or 64 depending on field size)

• Nowhere to store plan start date, plan end date or other relevant data that you will probably need.

• You need to decode the plan every time you use or apply it using bit operations.

• There is no quick and easy way to do generic type queries that link the tables quickly.

• Slower queries for health plan driven queries (i.e. all 'H10' Plans) without special indexes and query tools.

Issues with the original premise:

-
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)

-
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)

-
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).

StackExchange Database Administrators Q#10504, answer score: 10