How should I define different subsets of a common dataset in a web application?

Question

I am speccing out a potential project. The core of the project is a set of data that the client wants to be able to present to different third-parties. The client wants to be able to offer different subsets of the data to different third-parties.

The data will be stored in a MySQL database. A web app will connect to the database and provide the data through an API to client applications. Each client application may have access to different records in the database. Here's some example data:

fruit
id  name    color
1   apple   red
2   orange  orange
3   grape   purple
4   banana  yellow

location
id  name
1   new york
2   florida
3   california

fruit_location
fruit_id   location_id
1          1
2          2
3          3
4          2

The API for this data would have methods like get_all_fruit() and get_fruit_by_location().

By default, client applications would have access to all of the data. However, a client may only want (or be allowed) access to information about fruit from Florida. Another application may need access to all data except for the grape record. Calls to the API methods would be the same for each application, but would return different data with some results possibly omitted, based on which client application is making the request.

I am wondering what suggestions the amazing SO community members have for defining this sort of filter.

• Defined within the database
• Some type of configuration file

The two primary concerns:

• Ease of creating and modifying the specified filter, these will be managed by an end user
• Performant for a low to moderate number of concurrent users

Other notes

• The data will be read only for the client applications
• The data will change over time as new information is added or changed

I know this is a bit general, let me know if I can provide any other specifics that would help. Thanks for taking the time to make suggestions!

Answer 1

personally, i would handle this at your API level. where you store the criteria is irrelevant. your API will have to convert whatever rules you have in to SQL so you can add it as a WHERE clause in to all of your queries.