How to implement options with multiple return types

Question

I have an application where a multiple shops will share the data. There is an Options table that defines various program options. I have a varchar column that defines the value type, int, bool, Guid, string, etc. There are two tables that define the option values, one for system wide options and one for local options. The base Options table defines if the local option can be selected before the system wide option. The Shop and Global option tables are basically the same structure except the ShopOption table has a ShopId FK for the shop the record belongs to. Each of these tables store the option values as varchar although the string may represent an integer, Guid, bool, or may actually be a string. I need to show a form with a tab for the local option, a tab the global option, and a tab to specify if the shop can overide the global. What I am not doing right is to get an option entity and get the value as the type it should be.

For instance:
GetOption(SessionTimeout) should return an Option entity and the value should be an integer type.
GetOption(DefaultCustomer) should return an Option entity and the value should be a Guid type.

I am searching for an answer using design patterns, and think a factory pattern may be what I want, but I am just not getting it.

Answer 1

Something I've seen quite often in similar situations is providing a typed default :

OptionEntity<T> GetOptions<T>(string OptionName, T defaultvalue);

Answer 2

What you're asking for is late-binding, ie the ability to assign the variable type at runtime instead of compile-time. The immediate answer is that C# doesn't currently support that, and when it is supported it still won't fully solve your issue.

Your best bet is to use generics, which will increase type-safety but still won't prevent stupid errors. Creating a method like:

public T GetOption<T>(string key)
{
    // Retrieve the option type and value
    // Check that the option type and return type (T) are compatible
    // cast the option value to T
    // return the value
}

will let you attempt to cast the database result to the return type of T, but it will generate an exception if the cast fails (ie: you try requesting a GUID option as an int).

Answer 3

The underlying issue is that you are suffering from the inner platform effect, whereby you are trying to make a database within a database by storing as varchar what should be distinct, typed columns.

You have given yourself the ability to add options at runtime. However, they won't mean anything if the application doesn't understand them, and you can't add that understanding at runtime. The set of options must be known at design-time, which means the schema can be known at design-time, which means you have no need to abstract the structure into varchar values.

Make a table with columns representing each of your options, and use normal ORM practices to declare a data type to which it maps.

The abstraction isn't actually buying you anything.

Edit in response to comment from OP:

To implement cascading settings, you could make an OptionSet table with a column per option. There would only be one row, representing the global set. For each option which can be overridden by a manager, add a nullable column to the Store table.

You can then have a method which asks a Store to coalesce the effective options:

public class Store
{
    public virtual bool? AllowSavePasswords { get; set; }

    public virtual OptionSet GetEffectiveOptions(OptionSet globalOptions)
    {
        return new OptionSet
        {
            AllowSavePasswords = this.AllowSavePasswords ?? globalOptions.AllowSavePasswords,
            LoginTimeout = globalOptions.LoginTimeout

            // Repeat pattern for all options
        }
    }
}

As you can see, this allows everything to remain strongly-typed, while addressing the issue of options which can't be overridden. It also expresses the intent of which options can be overridden by specifying all of them on the Store table (reflecting their scope) and making them nullable (reflecting their optional nature).

The nice part is that there are no new techniques to learn or "magic" to implement (unless you hadn't seen the ?? operator, which is the equivalent of T-SQL's COALESCE function).

Answer 4

Have a look at Martin Fowlers book Analysis Patterns. Fowler calls this "Quantity", look at the begin of chapter 3. That is essentially the same, just replace "Quantity" by "Option". Of course, thats just the database part of the problem. For the application part, I suggest using Yooder's solution.

Answer 5

There are two solutions, each having its merit and its drawbacks :

Option 1 : Genericity

a system wide option table, defined like this :

Create table tbGlobalOptions
(
OptionName Varchar(255) Identity,
OptionValue Varchar(255),
OptionType varchar(255)
isLocked bit --this indicated the value cannot be overridden by the user. 
)

And an user options table :

Create table tbUserOptions
(
OptionName varchar(255)
UserID bigint,
OptionValue varchar(255),
Active bit
)
-- extra fields for logging omitted 
-- keys omitted 

The code contains an enum matching the OptionName column, so parsing Options from the code is trivial.

Cons :

• type safety can only be implemented using constraints or triggers (which is clearly harder to maintains than column types)
• it's harder to use the stored options directly from the database (as the parsing logic lives in the application code)
• retrieving all the options for a specific user is more costly (you cannot just select the user row)

Option 2 : Specialization (and strong typing)

A strongly typed option table containing one column per option

Create table tbOptions
(
UserId bigint, -- 0 for global defaults
Option1 int,
Option2 varchar(max)
Option3 int,
...
Option426 bit
)

Type safety is clearly a good thing, but here it has a huge cost :

• adding a new option requires a schema change
• the stored procedures used to update the table will contain a lot of duplicated code, as the logic (eg the isLocked mechanism, or some extra logging you might want to add) must be repeated over and over for each field. This is how you end up with stored procedures containing 1500 arguments.
• This solution doesn't scale well, as a table cannot have an unlimited number of columns (see max values for SQL Server 2008 here for example).

---

If you have 5 options and if this number is likely to stay the same over time, the second solution has its merits.

If on the other hand you plan to end up with thousands of options, this sound like a no-brainer for me : go for genericity !

---

In your application code, your problem is quite easily solved using a generic method :

OptionEntity<T> GetOptions<T>(string OptionName, T defaultvalue);

---

Edit to answer Bryan's comment below :

And yes, if there are 10000 values to store, there will be 10000 columns. That is true for every single table you will ever write. There is nothing special about an option table. Nothing.

This all depends on the level of abstraction we chose. How would you store a chess board position for example? You can clearly use a 64 columns table (64 values -> 64 columns) or you can use a design with 4 columns only (game id, x, y, contents). Don't you think both can be adequate depending on the situation?

In this specific case, if options can be created on the fly, or if their numbers is expected to grow exponentially, those options are, to a certain extent, just another type of data. And you don't want to store data in your schema, do you?