Hi,
I'm currently designing our database structure, and I'm currently struggling wether to define 1-to-many or Many-to-many.
So, here's a sample scenario:
Let's say I have the following entities:
At first glance, we know that a Hero may save many Cities, and a City can be saved by many Heroes. So we can already say there's a M:M relationship there.
But is it acceptable if I treat this M:M table into a single entity?
Here's the table definitions for each relationship:
Many-to-Many pivot table: city_hero
city_id
hero_id
saves
city_id
hero_id
saved_on
casualties
As you can see, either way, city_id and hero_id are still being referenced.
I know I can extend my model with the Illuminate\Database\Eloquent\Relations\Pivot class, and I also know that there's nothing holding me back on treating this as a standalone entity, but what would be the downside/effects if I treat it as one instead of a pivot entity?
It could depend if each hero has one city they are based in. They still can save other cities.
For example hero X is based in Columbus. Then a one to many.
But if a hero is based anywhere (unknown) go for the many to many.
Just my thoughts.
Even a complex many to many can be worked out with a chain of one to many.
I'd probably go for option two here
Can you elaborate why?
Also, would you consider it as a PivotModel or a standard Model?
Thanks!
I would go for a regular table, and not a pivot. It will have a kinds combined foreign key. If you go with a regular pivot then you would still need to create another table containing the other information, and a reference to the pivot.
I have done both.
If a name is apparent for this connection, ie "Rescues" then I would make it its own model. For instance a rescue might have assists, or wounded innocents, press coverage, and so starts to take on a life of its own. You might want to list all rescues, the city and the hero involved for instance.
This does not stop you querying City::with('rescues.hero') or $city->heros using hasManyThrough
Not having a model at all, and just using the table as a pure pivot still means you can have extra columns and use the withPivot to get those extra columns in your query. However this is the most restrictive setup, and you could outgrow it quickly,
I think I may have overcomplicated my current setup.
I've actually extended PivotModel, but I think it makes more sense if I just treat this as a regular model.
@ezmiks Surely your “saves” table would just act as a pivot table by referencing what “hero” made the save, and what “city” the save occurred?
Good talk, @ezmiks
Just to add to the fun. Can a hero save a city on multiple occasions?
Please or to participate in this conversation.