I have a table containing data that represents a tree structure of data, using the nested set model. I'd like to store data for more than one tree in the table and have only one of the sets of data be available. The idea is that there may be more than one version of the tree and even trees in development before going "live" with them.
My question is about where to safely store the "This is the live set" flag.
This is what I have (minimalized):
TreeDataSets id DataText ... VersionId Versions id VerName InUse Version id InUse TreeDataSets ID DataText VersionId 1 ABCD 100 2 EFGH 100 3 IJKL 100 4 1234 101 5 5678 101 6 3456 101 7 AB12 102 8 CD34 102 Versions ID Vername InUse 100 Characters Y 101 Numbers N 102 Mixed N Version ID InUse 1 100
So, I can either store the "InUse" flag against every row of the versions table, or have it in a separate table. (Both option are shown - I hope you get the idea).
Disadvantages with storing the flag in every row is that if more than one row is set to InUse, the data will be messed up. The disadvantages with storing the flag in an extra table is err, that there is an extra table and sql is a little more complex.
Is this a common data storage pattern? What is the best practice?
Is there a way of enforcing only one row is set to true within the database, if the 2 table option is preferred?