SQL Tables - Pattern for either/or data

Question

I have a quick question - is there a best practice in SQL Table design for storing "either/or" data?

I have the following problem - I need to store template data (defining folder structure) in a SQL table. Any given folder might have a static name (for example "Emails") or it might be dynamically generated for each instance, depending on the objects associated with it (eg. Company Name).

When a business object is instantiated the template data will be used to create the actual folder structure.

I am thinking of storing the data like this:

CREATE TABLE folder
(
    ID INT IDENTITY PRIMARY KEY,
    FolderName NVARCHAR(50),
    IsDynamic BIT NOT NULL DEFAULT 0,
    DynamicFieldID INT FOREIGN KEY REFERENCES dynamicField,
    ParentID INT FOREIGN KEY REFERENCES folder
)

So if the IsDynamic field is set to true I know that there will be a rule (defined on the foreign key), but if not I will use the value stored in the folder name.

However this seems a little messy for me - is there a "best-practice" pattern for this kind of scenario?

Answer 1

This doesn't look too bad to me.

You may want to consider not bothering with the "IsDynamic" field as this can be derived from the fact that the DynamicFieldID is null. Then in your SQL you could LEFT JOIN and COALESCE the fields from the Dynamic tables.

But I don't think the above model is all that messy.