How to store a potentially large amount of optional properties of objects?

Question

I'm using Entity Framework Code First with ASP.NET MVC. This handles my database design based on the domain models within my application.

As it stands, the engine I'm writing will have a website class and store class. store is a property of website linked by a ForeignKey in the database. I want to implement some options like TaxDisplayType on each of the models but am unsure on the best approach.

The issue I have is that Store could potentially have the same option as website which if it's set against the store should be used but default back to the website level if not.

These are the options I can think of to do this:

1. Add this option against each class i.e Website.TaxDisplayType and Store.TaxDisplayType - I am worried about the amount of duplicated options between 2 tables.

2. Have one table object that stores Key / Value pairs with a reference to the "scope" (i.e website or store) and I write a wrapper to drag out the options via their name. The issue I have with this approach is the use of magic strings. Unlike option 1, if a config option name is wrong in the code, it will still compile the project.

I'm also worried that going forward, if I have 500 options, this would equate to 500 database columns - This doesn't seem like a good idea.

I'm looking for some advice on the best way to handle this type of data given that:

1. I'd like to use properties of classes rather than magic strings.
2. Avoid duplication where possible.

Answer 1

As of this writing, I'm seeing two ways you can go with this, both of which are a variation of your option 2:

• Have an option table. This table would look something like this:

  • Foreign Key(you might want to add one for Store and Website)
  • Scope - enum(either website or store, so you know what kind of option you're pulling out)
  • Option - enum(taxdisplaytype etc., just modify the enum in the schema whenever you have new options to add)
  • Value

Pros: easy to query all the options for a website or a store and avoids duplication
Cons: you'd have a lot of rows if your websites and stores are using a LOT of options(shouldn't have a performance impact as long as its properly indexed), and you'd have to update your schema each time you add a new option(you need to add it to the list of allowed values of the enum so that the database doesn't throw a fit)

• Stick with 2, but instead of magic strings, use serialization instead of directly accessing the strings. This way, you actually have class properties you can access.

Pros: You don't have to change your schema if you want to add a new option or modify how it is structured
Cons: You're still storing strings in the database so you just need to be sure you serialize the stuff you pull out before using them.