Rails/General SQL entity-attribute-value for complex schema

Question

I'm having some trouble coming up with an effective database model for a set of complex relations and wondering if my current implementation will work well.

I'm working with location-specific data, in this case city permits, storing the permit requirements, and relating them to specific locations. Because there are many cities, and the permit requirements differ from city to city, I'm struggling to find the best way to record a specific location's progress with permit requirements.

Currently, my schema looks like:

<permit>
id
type
state

<requirements>
id
key
value

<permit_requirements>
permit_id
requirement_id

This works fine for storing all of the permit requirements, and recalling them. The issue is when relating them to specific locations. Currently I have:

<location>
id
state

<location_permits>
permit_id
location_id

And this works fine for returning all permits (filtered by state) and the children requirements. But to store the status of specific requirements, I'm thinking:

<location_requirement_answers>
location_id
requirement_id
status

Does this make sense? Is this an anti-pattern? I'm having trouble wrapping my head around the best way to pull in permits as a whole, and specific statuses per location.

I'm thinking that the related permits create the base list, and then on changes I would update the location_requirements_answer specifically. But would this require another relation for location_permits, i.e. location_permit_answers that would basically duplicate the original relation except with the answered requirements?

Any help is greatly appreciated!

Answer 1

Still having trouble wrapping my head around this, but here's what seems to work for now (albeit with performance concerns with too many database queries that definitely won't scale):

1. Remove <location_permits>
2. Add permits attribute to location and filter by state, this pulls in nested requirements as well
3. Query location_requirements_answers with the returned requirement_id