0

My question is what are the best practices for database/application design for the following ?

I use Code First Entity Framework and .Net MVC 5. There are common functions I want to implement on different entities. For example consider the following simplified example:

class Person{
 int PersonId;
 string name;
}

class Member{
 int PersonId;
 int GroupId;
}

class Group{
 int GroupId;
 string name;
}

class Label{
 string name;
}

So 2 entities person and group that have a many-to-many relationship. Now I want to add Labels to each to filter the data based on business logic. There are a few ways to go about it:

  1. Separate "ObjectLabel" table that has "fake" FK references to any table with a type column. This is probably bad practice but can simplify code implementation and give greater flexibility.
  2. A string column on each entity to save e.g. comma delimenated list of labels. Benefit of simple database but will be slower when the data scales.
  3. Seperate tables namely "PersonLabel","GroupLabel" etc to preserve referential integrity for quick look ups but seems redundant and will require more work.

Now I would like some advice on which approach is best and why ?

John
  • 3,512
  • 2
  • 36
  • 53
  • 1
    I would implement option 3 most of the time, it depends on the scale of the project, as you said. Never should I use option 1. – Dylan Slabbinck Oct 22 '15 at 13:56
  • I actually ended up on Option 1, the performance costs will be minimized with propper table indexing, and I can then leverage the benefits of simpler abstracted business logic in my code. – John Oct 23 '15 at 12:47

1 Answers1

1

Depending on what the labels are, and how dynamic they will need to be, you could look into using Enum flags. That would still only require a single column per table, and would certainly scale better than having a comma separated list in a column.

You can see an example here or here of how to go about it.

Community
  • 1
  • 1
DrewJordan
  • 5,266
  • 1
  • 25
  • 39
  • thanks for the suggestion but the idea is to have many to one, and they will be completely dynamic. Imagine for example githubs labels. – John Oct 23 '15 at 06:11
  • Actually your suggestion can work now that I think about it. I can just have a seperate look up table to determine what each number means, and limit the amount of labels allowed per entity. If there are no better solutions I'll mark this as the answer later. – John Oct 23 '15 at 07:37