Materia with real benefits suggestion

[Seraphor]

The problem with these suggestions is they're all an illusion of choice.

Someone will inevitably determine the mathematically superior option and any other choice becomes meaningless. Look at Tenacity. It raises your defense by upwards of 5% yet how many tanks do you see running around with it melded? Virtually none. Why? The extra defense means nothing. Not even at the Ultimate level will you notice really Tenacity because it doesn't save healers any GCDs at its current level. in the case of the OP, adding materia that grants a buff that you lose for 15 minutes if killed would cause so many issues. Imagine PF where if someone gets you killed, you lose 10% of your damage with no way to recover it for the length of an Ultimate encounter. It would turn DPS checks into "wait 15 minutes so our strength comes back."

In a single player game, you have none of these issues since you aren't playing with other people. You can use whatever setup you fancy because no else is impacted. A MMO does not have that luxury. Opting for more HP when it isn't necessary just makes you wrong for no reason.

Tenacity is an odd one, and I can imagine the only reason it's still in the game is because the devs aren't even sure of its worth.
Of course, if the content can be beaten without Tenacity, then as always, extra dps is favoured, so you're right that it doesn't see any use in current endgame where damage comes in spikes, but it could still have use for casual players as it acts as a crutch.

I believe it was worked out that melding for 2500 Determination gives you 10% extra damage, while 2500 Tenacity gives you 7.5% extra damage and defence.
That's 2.5% extra damage for Det over Ten, and considering you will never have ZERO Determination, it's probably more like 1.5% extra damage in practice.
Meanwhile a conservative 5% extra defence from Tenacity... is basically an extra cooldown. Given that most of them are ~10% damage reduction with <50% uptime, a 5% constant is fairly useful for things like dungeon trash pulls.

Piety is in a similar boat, in that you either need that extra MP, or you don't.