Database Design (RPG Item Enhance)

Question

I'm trying to figure out how to make my database tables in order to have enhance items (+0, +1, ...).

What I have: StatType (hp, str, ...) -> ItemStat (ItemId, StatId, Value) <- Item (Id, name, ...)

The thing is, I want to make items enhanced, so I tought:

1 - on the ItemStat(ItemId, StatId, EnhanceLevel, Value) Problem: How can I ensure that each enhance level gives a value to each stat of the item

2 - A way to solve the problem would be having a new table: ItemBaseStats (ItemId, StatId) so there would be all the base stats of the item and than in the ItemStat(ItemId, StatId, EnhanceLevel, Value) before the insert I would check the ItemBaseStats.

The thing is, is this the right/a good aproach? What am I missing? Should the check be on database side (so inserts would be via StoredProcedure), should it be in code? maybe both?

The thing is I want to avoid errors and each enhance have to specify a value for each base stat (that's a rule).

And btw, I'm using C# with EF Core (SQL Server) so everything will be done via code, but I need to figure this out because it will change the way the model will be built and written).

Answer 1

First of all, by taking a peek at your ItemStat table, you are using a design called EAV, Entity-Attribute-Value. The first post I've found about pros and cons of this is this one and, long story short, it can be useful when the attributes of an entity are dynamic, but relational databases (which sql server is) are not meant to do this. And indeed, I think a relational model instead would suit you fine.

Also keep in mind that I have no knowledge of ef-core at all, so this is all from the database perspective.

In order to figure out an item's stats, you need to know a) the item and b) its enhancement level. Thus, I propose:

1) The Item table as you already have it

2) The ItemStat table which will have ItemId referencing Item.Id, the EnhanceLevel, and, instead of the columns you mentioned, has one column for each possible stat value. The Primary Key of that table will be (ItemID,EnhanceLevel).

Even if there are many fields without value (eg maybe the column "defense" has null value for all weapons), you will hardly create enough items x enhancementlevels to make a table that will be too large for the database to handle.

The example in your comment would be like that:

[Table Item]
Id, name

Item1, 'Sword of StackOverflow'

,

[Table ItemStat]
ItemID, EnhanceLevel, stat1, stat2

Item1, 0, 2, 0
Item1, 1,10, 0
Item1, 2,15, 5

---

Considering the comments, the EAV solution I would propose is:

Do exactly what you describe in your "2 -" point (ie: add EnhanceLevel column in ItemStat), but there is no need for an ItemBaseStats table. You will just insert all base stats in ItemStats where EnhanceLevel=0.

I have no knowlege of nosql. However, as you yourself have stated in the comment, I would suggest re-considering my first answer. The size of a table with let's say 300 items * 100 enhance levels =30000 rows * 200 columns will be maybe unbelievably low for what you may have in mind (I expect a few MBs). And the reads on them will be using a clustered index seek, as each time you certainly know both the item and its enhancement. The CPU saved by not having to join to ALL the atributes needed at each read would be the preferrable resource to avoid, in my opinion.