1

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.

1

Introduce a Base class. You are already describing it Owner is either a School or a Classroom.

ergo:

owner
------
ID   |  name
-----+------
1    |  School #1
2    |  School #2
3    |  Classroom #1
4    |  Classroom #2

school
------
ID   |  other school related data
-----+------
1    | ...
2    | ...

classroom
------
ID   | school_id
-----+----------
3    | 1
4    | 1

If there is no further data needed for a school just drop the table altogether.

| improve this answer | |
  • How I will manage precedence `with base tables? in other words, how I will query "select school filters but overwrite them if the same filter exists for the classroom". I am meaning the case I explain in my question like this: "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." – Eray Apr 4 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.