Is it best practice to use a pivot table with many to many relationship when a model and 2 one to ma-CodePudding

This is more of a best practices question. I am making a game where factions can upgrade their technology for their faction. So I have a faction table which stores the faction name and id and a tech_upgrades table which stores the possible tech upgrades along with their costs. Then I have another table which stores the progress each faction has made towards their tech upgrades which is the faction_tech_upgrades table. So there should be a row in faction_tech_upgrades for every combination of faction and tech_upgrade. This kind of looks like a pivot table with extra data similar to https://laravel.com/docs/9.x/eloquent-relationships#retrieving-intermediate-table-columns

My dilemma involves how to set up the eloquent relationships. My gut says to use option 1 but just wanted to make sure I'm not setting these up poorly. So any advice on best practices is appreciated. Here are the 2 options I think make sense.

Option 1: Create a model for faction_tech_upgrades. Then set up a one to many relationship between factions and faction_tech_upgrades, and set up a one to many relationship between tech_upgrades and faction_tech_upgrades. The reasoning here is that the faction_tech_upgrades table is functioning as more than just a pivot table so maybe making a model for it makes sense, as it might be easier to work with in terms of updating and querying the table.

Option 2: Set faction_tech_upgrades up a as a pivot table and use the above link to get the data from that table. And use https://laravel.com/docs/9.x/eloquent-relationships#updating-a-record-on-the-intermediate-table to update the table.

factions columns

id, 
name, 
color, 
created_at, 
updated_at

tech_upgrades columns

id, 
name, 
description, 
class, 
order, 
type, 
gold_cost, 
iron_cost, 
wood_cost, 
emerald_cost, 
pink_tourmaline_cost, 
turquoise_cost, 
created_at, 
updated_at

faction_tech_upgrades columns

id, 
faction_id, 
tech_upgrade_id, 
unlocked, 
gold_contributed, 
iron_contributed, 
wood_contributed, 
emerald_contributed, 
pink_tourmaline_contributed, 
turquoise_contributed, 
created_at, 
updated_at

Any advice or other options appreciated.

CodePudding user response：

If you have more than 2-3 extra fields in a pivot table, its better to create an another model and make one to many relationship as you describe in option one. It will will be easy to insert and update data. But if you follow the Laravel pivot table naming convention you will get extra benefit for easy access of the data.