4

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.
3

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.

  • I like this idea and is very similar to an approach I was already trying out (foreign key for Store and Website in the option table). I am using Enum for fieldtype (String, int, select list etc) so having one for the actual name is a great idea. My only concern at this point is mapping a number to field name when writing raw SQL queries but this isn't a huge issue. – webnoob Oct 23 '14 at 9:58
  • this normally depends on how your DBMS handles enums, In mysql's case, you can use the word itself you defined in the enum field(the database converts it to int for you on the fly) when running queries, helps alleviate the usage of lookup tables and magic numbers – Maru Oct 23 '14 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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