Bearing in mind that in order to append, the backup jobs must write to the same media set, you can create different media sets and create partitions in a library, and separate jobs for each.
Since you have a library, you could, for example, create a job called archive, defined as "Append, Else Overwrite", and set the media set to "Append - Infinite" and OPP to "None - Never Overwrite" (Remember that over time tapes do decay, so just because you backed up data last year doesn't mean that it can be restored next month)
If you will use a different tape for user data each night, you would need 5 tapes for this media set. The set should be Overwrite - 6 days, Append - None
For the Prod datbase Backup, I'd keep at least three copies (as I said, tapes do go bad, and if the backup chokes for whatever reason, the data that was on the tape is no longer retreivable) So Overwrite Protection Period would be 2 days. Append none
Dev Databases, Two sets of tape should be sufficient, OPP of 27 days
If that's as clear as mud, let me know, and I'll try again.