As an instance I’ve a really giant scholar record wish to filter if every of those college students is satisfying some particular classroom college and classroom limitations. So, in my information mannequin, I’ve Faculty and Classroom entities. Limitations (let’s name them filters) are like

• Age is bigger than 9
• The grade is equals to 2
• Needs to make use of the varsity bus

School rooms and the varsity have the identical filters however classroom filter values have priority over the varsity’s filters. If there isn’t a particular filter outlined for the classroom, the faculties one shall be used.
For instance, the varsity’s filter definition is like this:

• Age is bigger than 7
• Should wish to use the varsity bus

However the classroom’s definition is:

• Age is bigger than 10

On this instance, if a scholar is older than 10 and desires to make use of the varsity bus, I can assign him/her to the classroom.

That is my database design:

college
----------
id
title
...

classroom
----------
id
title
school_id
...

filter
----------
id: primary_key
title: string

filter_data
----------
id: primary_key
filter_id - FK to filter.id
owner_id - Polymorphic affiliation to `Faculty` and `Classroom` (the primary level of my query)
filter_type - ENUM('CLASSROOM', 'SCHOOL')
operator - must be a from an outlined record of comparability operators equivalent to: >, <, =, !=, and so forth...
filter_value - The worth to filter towards.

Some instance information:

college
------
ID   |  title
-----+------
1    |  Faculty #1
2    |  Faculty #2

classroom
------
ID   |  title          | school_id
-----+----------------|----------
1    |  Classroom #1  | 1
2    |  Classroom #2  | 1

filter
------
ID   |  title
-----+------
1    |  Age
2    |  Grade
3    |  Faculty bus

filter_data
------------
ID   | filter_id  | owner_id       | filter_type | operator | filter_value
-----+------------+----------------+-------------+----------+-------------
1    | 1          | 1              | SCHOOL      | >        | 7
2    | 1          | 1              | CLASSROOM   | >        | 10
3    | 3          | 1              | SCHOOL      | =        | 1

With this construction, I can simply question and perceive that, to assign a scholar to Classroom #1, scholar’s age should be higher than 10 and should wish to use the varsity bus. One thing like:

SELECT * FROM filter_data
WHERE (filter_type='SCHOOL' AND owner_id=1) OR (filter_type='CLASSROOM' AND owner_id=1)
GROUP BY filter_id HAVING filter_type='CLASSROOM'

(replace: the question above did not work as I anticipate 🙂 )

And now the primary query: As you see, there’s a polymorphic affiliation for the filter_data.proprietor.id, it could actually check with Faculty or Classroom and I am shedding essential options of international key utilization, like information integrity. An in search of some concepts to have a greater, extendable design which ensures information integrity.

Planning to make use of PostgreSQL if helps.