Take the 2-minute tour ×

First of all thanks for reading. I have an idea for an approach to solve a problem I am facing, or better say a requirement. What follows is an hypothetical scenario that is not real but represents the problem without giving up confidential information.

Imagine a service that has a very large database of people that live in a country, let's say, 100 millon records.

The service is used by different companies to query data about those people. Normal queries would be:

  • Show me all Catholic people that are between 25 and 30 years old and live in x city
  • Show me people that have more than two kids
  • Show me people that have an annual income between 100k and 200k
  • Show me people that have been on holiday more than 3 times in the last year etc..

There is a big table called "People" (wow.. ) that has all 100 millon records and all the attributes needed in different fields, let's say it has 40 columns.(mix of int, datetime, varchar, char, etc..) There are also a bunch of static tables (25) that the main table references to get descriptions, or to resolve more complex attributes.

The company that owns this database offers and sells the ability to query this DB through a web service. This web service has to receive the query, the client id, parameters, etc.. and return the result set "as fast as possible".

No issues so far.

The company that owns this database has an internal application managed by the commercial team where it sets different rules that define the ability for each client to access x or y data. The more data they access the more money they pay for the service.

Not all clients can see everything, some clients can only query people from a particular city, other clients are allowed to see people from certain age group, others people that are from certain religion "and" certain socio-economic level, etc...

Every day the company that owns this DB can change the above rules if it wants and allow a client to see more, or less. The owner can only change it today to be applied tomorrow, from one day to the other. Not within the same day or real time or anything like that.

So, we have this big database, only one, for all clients, all clients make all kind of queries, but the system needs to filter the data dynamically on two levels:

FIRST-Based on the business rules that define what each client can see

SECOND-Based on the parameters of the query (the WHERE clause)

My question is how to implement in an efficient way the "FIRST" dynamic filter described above.

There are a couple of options just to name a few like:

Option 1: Having one copy of the main table for each client, and once a day truncate that table and insert the records again from the main table checking the filtering rules to only insert those records that the client can see. This option is good from query performance point of view, but is bad from processing time and it scales poorly when we have more clients - there will be more tables and more inserts and more processing time. And it's ...ugly.. don't like that.. :)

Option 2: Adding the business filtering rules dynamically to the where clause on each request. Good because it doesn't need a large batch process but don't like it as the where clause could be tooooooo longgggg and that could bring problems, because there is no limit on the number of filtering rules that the owner can define for a client, and the filters can be quite complex (like: Company A can only access White people, born between 70 and 80, with blonde hair and between 1 and 2 cars in the family, "or", black people etc.. etc.. "or" mixed race that bla bla.. "or" ) So.. you get the point, don't like it.

Option 3: Is the one I was thinking is to have a table called Row-Client or something like that, that will have the RowID and the client that can access. Then we'll require a batch process that will populate that table based on the business rules. (but not a big process, because we are only inserting two values) Then, upon each query, add a join to that table to get only rows allowed for the current client who is executing the request.

It can be something like this:

Row: 1 Client: 1 Row: 1 Client: 2

Or (no ref int though)

Row: 1 Client: 1,2

Or even we could add that "Client" column directly into the Main Table.

So my question is if the option 3 could work (with 1050 clients querying 50 times a day on business working hours) or to know any other idea or maybe established method or approach or technology to achieve that in an efficient way.

Of course I am open to your questions/ideas and I appreciate your help.


What I am looking is to minimize batch processing time and query response time of course. Kind Regards

share|improve this question
add comment

1 Answer

First of all, I have no actual experience doing this, so I'm being theoretical here. I would not, under any circumstances, repeat your people table anywhere for any reason. I would keep another client limitation rules table that would be used to filter the results of their queries.

So they can send queries for whatever they want, even data they haven't paid for, but before the results are returned, their query results pass through another process that limits their columns and/or rows by what they've paid for.

OK, given your row limitations on aggregate functions, I see you'd need a 2 (or more) step process. First, limit by row criteria, then execute their sql, then limit by columns. Limiting by rows is the tricky part, if you need them to limit by anything that might get created in the future without schema changes. The easiest thing to do is limit (ha,ha) the row criteria they can limit by, then have one table with a bunch of columns named things like 'isLimitedByAge', 'isLimitedByRace.'

Depending on your timeline, you might need to implement this in pieces, with a less sophisticated solution now, and a more dynamic one later, after you've learned more about what most clients are querying by and therefore, likely to be willing to pay for.

For a more specific example, let's say a client sends a query, 'select * from people'. the first part would be to query clientLimitRows to see what they've paid for, like people in a certain city, or people in a certain age range. That process builds the WHERE clause for the second process that actually queries the people table, performing aggregation. Then a third process checks clientLimitColumns to remove from their results any columns they haven't paid for.

Again, just my opinion, but I think you're going to have to break your client rules down. If I had to model WHERE Company A can only access White people, born between 70 and 80, with blonde hair and between 1 and 2 cars in the family, "or", black people etc.. etc.. "or" mixed race that bla bla.. "or" ) I'd have a tables with rules (one per rule), conditions (one per OR set), and clauses (one per field/operator/value tuple connected by ANDs).

So for this rule, where you're limiting by race, age, haircolor, and numCars, OR race and blah2 OR mixedRace and blah3 or blah4 you'd have 4 rows of conditions with one or more clauses.


rule = 1
    condition = 1
    clause1 = 'race = white'
    clause2 = 'age >= 70'
    clause3 = 'age <= 80'
    clause4 = 'haircolor = blonde'
    clause5 = 'numCars >= 1'
    clause6 = 'numCars <= 2'
    condition = 2
    clause1 = 'race = black'
    clause2 = 'field2 = blah2'
    condition = 3
    clause1 = 'race = mixedrace'
    clause2 = 'field3 = blah3'
    condition = 4
    clause1 = 'field4 = blah4'

the clause table has fields customerID, ruleID, conditionID, clauseID, field, operator, value

OK, I'm not sure I 100% understand what you're doing with option3, but it sounds like you're extending your people table with client markers, or introducing a rowID/clientID table with a 1-many relationship with people? Then you'd apply their paid for rules in an overnight process so the rows they can access are marked and can be limited with a join? I think it would work, but wouldn't they only get results from yesterday's queries today? If they pay for new data today, it won't be marked paid until tomorrow. I'm not sure you're missing anything, really, but if someone else has a better response, more power to them.

OK, I think I see where you're going. You want to create a table overnight that extends the people table with a clientID field, so each clientID has their own set of queryable rows? And you can't do that on the fly? When they send a query, first create their set of rows, then apply their query to that set?

share|improve this answer
Yes, I think the same, the problem is "HOW" to implement that middle step that removes the unpaid data without impacting too much on query performance? As you can see, I don't like to repeat the data as I said on Option 1. Option 2 is not practical, the only thing I have so far is option 3. Remove data after the query is executed can be impossible as some queries can have Group By or Sum() operations, and it wouldn't be possible to remove the unpaid items after is was previously summarized. Thanks for your answer –  LeoGranata Nov 16 '12 at 15:44
Thanks for your time again, you are describing the option I named "Option 2", the column part is fine and easily doable. But it's not possible to rely on a dynamic WHERE as the number of filtering conditions set by the commercial team can be up to many thousands, and the use of indexes will be badly hit I believe. (also maybe it will be problems with statement lenght as it will be huge). Thanks Anyway :) –  LeoGranata Nov 16 '12 at 16:08
Thanks, I have a table with the rules already, just don't want to apply those in the WHERE Clause, I can't reduce the number of conditions as a client will only buy what they need, and they could need a very specific set of filtered data. I wanted to know if there is something at a database level that can be done that will be kind of transparent to the query, but without replicating data.. I still think Option 3 I initially thought is the best bet at the moment. Could you please check my Option 3 and let me know if you find any issue with that or what you think about that if you can please.? –  LeoGranata Nov 16 '12 at 17:36
Yes Beth, you are understanding correctly the Option 3, and there is no problem if its done in a batch, as the information the company sells is always "yesterday" data. I think that process that assigning clientId to the rows is less expensive than any other process that comes to my mind. I am surprised no one else has anything to say on regards to my question, I really appreciate again your time trying to understand my issue. If anyone else comes to this thread and read what you said and my comments , it would have a nice picture of the scenario. Hope I get more opinions. Cheers! –  LeoGranata Nov 18 '12 at 22:21
add comment

Your Answer


