0

I am currently building a game series information site and have come to a design issue. I have a table called Abilities that would have character ability information in it. Depending on the game there could be special columns added that are unique to just that game. For example one Ability in Game A requires the use of ITEM X but in 50 other games there are no abilities that require the use of items.

I was originally thinking that I would create a parent abilities table that had all the universal columns in it and a separate table for each game (currently 250+ games) for game specific abilities or I could create a single table with lots of columns that would end up with lots of null values in it.

I think the better design pattern is to split into game specific tables but since I am using Entity Framework Core I am not sure if splitting them would still be the better design pattern or not as that would make for some convoluted LINQ to Entities queries. Can anyone that has experience with this design conundrum in ASP.Net and Entity Framework chime in with some thoughts on which way to go and why?

  • 1
    That you're using ASP.NET core has very little to do with your database design. – Robert Harvey Jun 12 at 15:09
  • @RobertHarvey I know ASP.Net has little but Entity Framework might affect it since it is an ORM. I think I miss explained my question. Editing question now for better clarity to those not in my head. – Matthew Verstraete Jun 12 at 15:16
1

This might be a good use case for an EAV table.

Here are your columns:

ID          int PK
PlayerID    int
ItemID      int
Value       string (or whatever)

EAV's are typically used for sparse matrices, exactly what you describe in your question.

  • Thanks, I had never heard the terms EAV or Spare Matrices before. Am I correct in that in your example ItemID would map to a table that would have the Object names in them? – Matthew Verstraete Jun 12 at 16:31
  • Yes, that's correct. – Robert Harvey Jun 12 at 17:18
  • Thanks for the confirmation and help. – Matthew Verstraete Jun 12 at 17:33

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.