I'm looking for a pattern that allows a user to turn on or off a field. If I have a CAR table, I would have a many-to-many link table called CAR_OPTION that links options (CD player, leather seats, etc.) to a car.
I would like users to be able to select which of these potential options they would like for a car and then store that data, such that the user can see which of the potential options they have turned on/off. I've gone through a bunch of this in my head and all of my solutions feel like they are reinventing the wheel. The only half-decent solution I have involves creating another many-to-many table that also has an id field to bind back to that user ORDER, something like CAR_OPTION_ORDER that I would then overlay for the delta on CAR_OPTION in the UI code
Is that the best option or are there some other common practices or patterns for this scenario? My solution just feels like a lot of the same/similar data stored multiple times.
You have the best option in my opinion. It's called an associative entity. I read once long ago to add an identity primary key / clustered index rather than using the two foreign keys as a primary key. That's been my SOP and joins perform very well.
On the reporting / historical analysis side I might use a different design if the load was heavy.