How to select sets of data from a table

Question

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?

Answer 1

The problem is a simpler version of: How to have a one-to-many relationship with a privileged child?
Either option is good.

• If you choose storing the InUse flag in the Versions table, you just need to add a filtered unique index:

  ADD UNIQUE INDEX Versions_InUse_FUX
    ON dbo.Versions (InUse)
    WHERE (InUse = 'Y') ;
  

• If you choose the separate table option, you'll need to restrict the table to (maximum) one row. This recent question has several ways to do that: How to limit maximum number of rows in a table to just 1. One of them:

  CREATE Table dbo.VersionInUse
    ( VersionId INT,
      InUse AS 'Y',
      CONSTRAINT Versions_VwrsionInUse_FK
        FOREIGN KEY (VersionId)
        REFERENCES dbo.Versions (Id),
      CONSTRAINT VersionInUse_IsOnlyOne_UQ 
        UNIQUE (InUse)
    ) ;
  

Answer 2

With one table you would need to limit it to a single row.

I think a trigger on InUse would be a better solution.