SQL Tables - Pattern for either/or data

Question

Hello All,

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.

Answer 2

CREATE TABLE folder
(
    ID INT IDENTITY PRIMARY KEY,
    ParentID INT FOREIGN KEY REFERENCES folder
)

CREATE TABLE dynamic_folder (
     ID INT FOREIGN KEY REFERENCES folder (id),
     DynamicFieldID INT FOREIGN KEY REFERENCES dynamicField
)

CREATE TABLE static_folder (
     ID INT FOREIGN KEY REFERENCES folder (id),
     FolderName NVARCHAR(50)
)

Answer 3

I am not sure I am understanding the question exactly, but I think what you are saying is that you have a field that can have either a static value or one that needs to be determined at runtime based on other values.

I would create your own "variable" scheme and store that in the database. I don't know what language you are using and so I would come up with something based on that (make it different from your language) and I would use something surrounding the value - start and end. So for example:

value: /companyA/projectA/

or

value /@companyVariable@/@projectVariable@/

then just write a routine looking for opening and closing @ signs and swap those out with the appropriate values. It is a little more work, but I think it would be the easiest to understand in the end and the most flexible. Again, @ signs is just what I thought of first, use whatever characters make the most sense to you.

Answer 4

You can just have NULL in DynamicFieldID and query like so:

SELECT COALESCE(dynamicName, folderName)
FROM folder
LEFT JOIN dynamicField ON (dynamicField.ID = folder.DynamicFieldID)