I have a basic 1:Many table relationship for products and the categories they are in. The tables are set up as
This has been in place for years, works great, yadda yadda. Now we have a new requirement where we need to specify a single relationship (inside of CxP) as the "master category". Currently when we want to know about the product, we select the categories and only use the first returned category. This obviously doesn't always work well or even consistently since there is nothing to sort CxP on.
Without a complete overhaul of the current structure, what is the best way to accomplish this? I would like to be able to enforce the 1 master per product at the database level, but the only structure I can think of that allows that is a column that is "is_master" where no is NULL and yes is 1.... but that's a nasty hack