Home > Software design >  one to one polymorphic table structure in laravel with sql
one to one polymorphic table structure in laravel with sql

Time:03-30

I'm using Laravel 8 trying to create a database design with something that turns out to be one to one polymorphic relationship (never use it before). But seeing Laravel's documentation here, I can't help to wonder why not making the tables with one to one polymorphic relationship like this:

images
    id - integer
    url - string
    imageable_type - string

posts
    image_id - integer
    name - string
 
users
    image_id - integer
    name - string

with image_id in posts and users set as foreign-primary key which refers to images's id. The idea behind this approach is because the relation between these tables is one to one, thus posts and users don't really need their own id.

Also, this approach will allow me to add constrain to image_id with

$table->foreignId('image_id')->constrained();
$table->unique(['image_id']);

I believe that this is doable. But, what's your insight about this? Is there something I miss by using this approach? Do you think this is future proof, like if a new child model is added in the future? Which one would you choose between this approach and the one Laravel wrote in its documentation?

Edit

Reading @AlexMax's comment, I now realize that my concept IS flipped! Laravel's example considers the images as the child; while in my paradigm, posts and users are considered as the children. Maybe I should change the example case to this:

documents
    id - integer
    type- string
    document_type - string

document_type_a
    document_id - integer
    name - string
 
document_type_b
    document_id - integer
    name - string

with a side note: every time new documents are created, it will then create a new record for one of its children (handled by the app). In this case, the children are document_type_a and document_type_b. Which one is used will be determined based on the type of documents.

So, back to the question: what do you think?

Plus: is this still considered a polymorphic relationship?

CodePudding user response:

You can take this approach, however I believe in future proofing. By creating primary uuid would potentially give you flexibility in the future. You're also creating a inverse dependency saying that if a user exists it must have an image_id. Whether or not that is the intention it will most likely be read that way by design. What I mean by future proofing is that you would want to be closed for modification in your db and open for extension. For example if you just have a polymorphic table and the create primary keys you won't have to modify pre existing code but rather create a new class, table (for that class), and a relationship without changing a line of code in the image class they way you are currently doing it image needs to be aware. So you would be force to have a trait for every class that has an image associated with it. You also lose the history of images. For instance a user changes their profile picture you only have a reference to the last image vs a history of images.

  • Related