How to avoid polymorphism
50 views
Skip to first unread message
Ruari Mactaggart
Nov 3, 2016, 11:30:10 AM11/3/16
to redbeanphp
I do not want to build polymorphic relationships into my db schema.
Where in the past I would have had a column called entity_id and another called entity_type.
Between the two of them, I could look up a related record. This could be from tables village, town or country
Redbean even kindly helps me achieve this, with the poly() method.
However, I understand this is an anti-pattern and I want to avoid it.
Instead, I introduce another table in between the two - let's call it place
In my parent table, I now have a single column, place_id, with a foreign key to place.
Each of the other tables has a 1:! relationship with place, via foreign-keyed place_id.
As far as I can see, this protects the integrity of my db, all foreign key columns reference a single other table.
My problem now, is how do I retrieve the village, town or country with Redbean?
Right now, I'm iterating over these three known tables and looking for an ID matching place_id. Pretty expensive and brittle way to do it though I think.
So I considered adding a qualifier into the place table, of entity_type. But then it feels like I'm back where I started, I have to use poly() again...
Where in the past I would have had a column called entity_id and another called entity_type.
Between the two of them, I could look up a related record. This could be from tables village, town or country
Redbean even kindly helps me achieve this, with the poly() method.
However, I understand this is an anti-pattern and I want to avoid it.
Instead, I introduce another table in between the two - let's call it place
In my parent table, I now have a single column, place_id, with a foreign key to place.
Each of the other tables has a 1:! relationship with place, via foreign-keyed place_id.
As far as I can see, this protects the integrity of my db, all foreign key columns reference a single other table.
My problem now, is how do I retrieve the village, town or country with Redbean?
Right now, I'm iterating over these three known tables and looking for an ID matching place_id. Pretty expensive and brittle way to do it though I think.
So I considered adding a qualifier into the place table, of entity_type. But then it feels like I'm back where I started, I have to use poly() again...
gabor
Nov 4, 2016, 4:16:36 PM11/4/16
to redbeanphp
Hi there Ruari,
Thanks for your question.
Why not simply use a table like:
village_id, town_id, city_id ?
if city_id has a non-null value the others will be null, this might seem strange but it's fast and you benefit from relational integrity.
It's the DB way ;)
cheers
Gabor
Ruari Mactaggart
Nov 7, 2016, 6:28:28 AM11/7/16
to redbeanphp
Hi Gabor
Not sure this really works very nicely.
I can't constrain the table to only store a single place value this way - I would have to unset previous values in code.
It also means when retrieving records, I have to check each column in turn in code until I find a child - if I add a new place type, I have to change all my place finding code.
What I'm trying to achieve is table inheritance, and I think I have to include type in the place table to be able to fetch the child quickly. Which means I will end up using ->poly()...
Not sure this really works very nicely.
I can't constrain the table to only store a single place value this way - I would have to unset previous values in code.
It also means when retrieving records, I have to check each column in turn in code until I find a child - if I add a new place type, I have to change all my place finding code.
What I'm trying to achieve is table inheritance, and I think I have to include type in the place table to be able to fetch the child quickly. Which means I will end up using ->poly()...
Reply all
Reply to author
Forward
0 new messages