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?