Let's say I have a very large student list want to filter if each of these students is satisfying some specific classroom school and classroom limitations. So, in my data model, I have School and Classroom entities. Limitations (let's call them filters) are like
- Age is greater than 9
- The grade is equals to 2
- Wants to use the school bus
Classrooms and the school have the same filters but classroom filter values have precedence over the school's filters. If there is no specific filter defined for the classroom, the schools one will be used. For example, the school's filter definition is like this:
- Age is greater than 7
- Must want to use the school bus
But the classroom's definition is:
- Age is greater than 10
In this example, if a student is older than 10 and wants to use the school bus, I can assign him/her to the classroom.
This is my database design:
school
----------
id
name
...
classroom
----------
id
name
school_id
...
filter
----------
id: primary_key
name: string
filter_data
----------
id: primary_key
filter_id - FK to filter.id
owner_id - Polymorphic association to `School` and `Classroom` (the main point of my question)
filter_type - ENUM('CLASSROOM', 'SCHOOL')
operator - should be a from a defined list of comparison operators such as: >, <, =, !=, etc...
filter_value - The value to filter against.
Some example data:
school
------
ID | name
-----+------
1 | School #1
2 | School #2
classroom
------
ID | name | school_id
-----+----------------|----------
1 | Classroom #1 | 1
2 | Classroom #2 | 1
filter
------
ID | name
-----+------
1 | Age
2 | Grade
3 | School 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 structure, I can easily query and understand that, to assign a student to Classroom #1, student's age must be greater than 10 and must want to use the school bus. Something 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'
(update: the query above didn't work as I expect :-) )
And now the main question: As you see, there is a polymorphic association for the filter_data.owner_id, it can refer to School or Classroom and I'm losing important features of foreign key usage, like data integrity. An looking for some ideas to have a better, extendable design which guarantees data integrity.
Planning to use PostgreSQL if helps.
