1

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?

  • 1
    Even with two tables you need to limit the version table to one row but that can be done. You could have a trigger on InUse to not allow a second Y. msdn.microsoft.com/en-us/library/… – paparazzo Jun 23 '16 at 13:34
  • Thanks @Paparazzi - a trigger on InUse which doesn't allow a row to be set to 'N', but changes all other 'Y's to 'N's when one is set to 'Y' would work, I think. There must be at least one set of tree data enabled. Thanks, – Michael Vincent Jun 23 '16 at 13:48
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)
      ) ;
    
  • Thank you, I'm going to go for the option where I store the InUSe flag in the version table. Regards, – Michael Vincent Jun 27 '16 at 9:02
1

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

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

  • Thank you. I've decided to go with the one table. I'll look into the trigger way of doing things. Regards, – Michael Vincent Jun 27 '16 at 9:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.