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.