I do understand that an Index on this theoretical "Bitmask" column would be expensive to maintain, but how is Indexing a Bitmask column any different than say a "LastName" column? Is SQL any smarter when indexing millions of Patients' last names? In other words does SQL interpret your query and go 'Oh, you're looking for smith, ok, find all names that start with "S" and then break that down into "mith"'?
Basically, yes. The problem with a Bitmask in general, and with indexing a bitmask is that you rarely want the whole bitmask, you want to know whether some bit is set, but that isn't how indexing works. (Now, if there was a way to mark a bitmask as such and SQL would then index it differently that would change things).
Mathematical calculations have always been superb in SQL, so I can't see how "Select ID From TestAnswers Where Anwser & Power(2, 2 - 1) = Power(2, 2 - 1)" (2 = correct answer) is any different than when we are doing summations on Monetary columns?
It is different in the same way that using SUBSTRING is different. It is easier for the engine to find all words that start with "A" than to find all words that have "A" as the 3rd letter. In the same way it is easier for the engine to find all numbers greater than 50 than to find all odd numbers.
Also, as far as reporting goes, yes, you do have to build views to give a better representation about the data in an EAV. However, that IS the main point of an EAV! EAV's give you the capability to report on the same data in a myriad of ways without the need for extra columns, changes in schema, or redesigning an application.
Er.. No. The point of an EAV is to allow the customer/user to define their own data structure. You can then build a cumbersome reporting assembly over top of this to allow for ad-hoc reporting if you desire.
You can always report on the same data in many ways using a database, that is the point of the database. Data Structure helps you to determine whether the reporting you want actually makes sense with the data you have.
--
JimFive