2

My question is what would be the best way to handle storing a list of values where the next serves as a fall-back. With a completely variable number of fall-backs (specified by our user).

I'm aware storing a delimited list in one SQL field is considered an anti-pattern (such as a list of parameters), however in this case we have a folder name, and if that folder name does not exist, we will fall back to another specified folder name, so on and so forth until all given folder names have been exhausted or until the first working folder name is found.

The order of the folder names tried is also important and must be preserved. Can't have it checking 99 before 8.

1
  • You'll have to be a bit more specific about the nature of your raw data... otherwise the question isn't clear!
    – Vérace
    Oct 31 '18 at 13:53
3

The simplest solution I can think of is to simply have a table folders with two columns: folder and priority. Simply

select folder
from folders
order by priority;

Then just try them in order and use the first folder that exists as a physical directory.

If folder in your current model is an attribute, then make the folders table a child table of that table; ie, if your current model is:

x (
   id,
   col1,
   col2,
   folder
)

then you should turn that into:

x (
    id,
    col1,
    col2
 )

 folders (
     x_id references x,
     folder,
     priority
  )

and the query becomes:

 select folder
 from folders
 where x_id = <id>
 order by priority;
1

One could use a self referencing table, with a recursive CTE to provide a manageable list of folders, with unlimited fallback folders, similar to:

CREATE TABLE dbo.FallbackFolders
(
    FolderID INT NOT NULL CONSTRAINT PK_FallbackFolders PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FolderName VARCHAR(255) NOT NULL
    , ParentFolderID INT NULL CONSTRAINT FK_FallbackFolders_FolderID FOREIGN KEY REFERENCES FallbackFolders(FolderID)
);

INSERT INTO dbo.FallbackFolders (FolderName, ParentFolderID) VALUES ('C:\',NULL);
INSERT INTO dbo.FallbackFolders (FolderName, ParentFolderID) VALUES ('C:\Test\',1);
INSERT INTO dbo.FallbackFolders (FolderName, ParentFolderID) VALUES ('C:\Test\Test2',2);
INSERT INTO dbo.FallbackFolders (FolderName, ParentFolderID) VALUES ('D:\',NULL);
INSERT INTO dbo.FallbackFolders (FolderName, ParentFolderID) VALUES ('D:\Test\',4);
INSERT INTO dbo.FallbackFolders (FolderName, ParentFolderID) VALUES ('D:\Test\Test2',5);

SELECT * FROM dbo.FallbackFolders

;WITH Folders (ParentFolderID, FolderName) AS
(   SELECT F.ParentFolderID, F.FolderName  
    FROM dbo.FallbackFolders F
    UNION ALL
    SELECT F1.FolderID, F1.FolderName 
    FROM dbo.FallbackFolders F1
        INNER JOIN Folders ON F1.ParentFolderID = Folders.ParentFolderID
)
SELECT Folders.FolderName, F.FolderName AS FallbackFolderName
FROM Folders
    LEFT JOIN dbo.FallbackFolders F ON Folders.ParentFolderID = F.FolderID
WHERE (F.FolderName IS NULL OR Folders.FolderName <> F.FolderName)
ORDER BY F.FolderID DESC;

The results:

enter image description here

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