1

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

LoadIt
  • 137
  • 1
  • 1
  • 9
  • So, enhancement levels affect multiple stattypes, right? Is the way they affect them the same for all items, eg for all items enhance level 1 brings +50% hp, or could it be that for item 1 and item 2 the hp enhancements for the same level differ? – George Menoutis Nov 07 '18 at 11:31
  • Yes (that's why I tought of a table that contains all base stats for the item) but they differ with the enhance (is not a multiplier) so lets say, Item 1, have 2 stats so Item1 (stat1, enhancelevel0, 2) Item1 (stat1, enhancelevel1,10) Item1 (stat1, enhancelevel2, 15) and Item1 (stat2, enhancelevel0, 0) Item1 (stat2, enhancelevel1, 0) Item1 (stat2, enhancelevel3, 5) – LoadIt Nov 07 '18 at 11:38

1 Answers1

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.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • Hello, thank you for the suggestion, but I can't use this approach (I'm not developing a game, I just want to do a database for a existing game, for learning purposes and for some tooling) and this approach is a problem because there are items that have 13 stats for example (there are some unique event items that have more). I can totally see the advantage on the approach you suggested, and is a very nice idea that I can apply for some other stuff, but for this particular case is not a solution. – LoadIt Nov 07 '18 at 12:14
  • What is the problem with 13 stats? I've seen many production tables with 50 columns. If it is computing resources that set you off, have you considered the pros and cons of having one less join? – George Menoutis Nov 07 '18 at 12:18
  • The problem is, and I can give you the exact number in this case I would have 1 colum per existing stat, in total they are 120 different stats. And this list continues growing as the developer releases new content. – LoadIt Nov 07 '18 at 12:24
  • Ok, I'll add an EAV solution too. – George Menoutis Nov 07 '18 at 12:27
  • Thanks, and btw is it worth all that or just go for a NoSQL Database (but for what I've read so far NoSQL like CosmosDB are good for very high frequency of writes/reads, But in this case it will have a lot of reads, but almost non existent writes after the inicial setup. And this seems like something a relational database or maybe a mix of both?? – LoadIt Nov 07 '18 at 12:31
  • The Game have around 2500 items, so it would be 2500 items * 21 levels os enhancement * 120 columns = 6 300 000. In .json all the items have less than 5MB and for what you have been showing and this talk I think this is very suitable as a cache database (redis cache for example) The size is not very big, inserts will be hardly done and in case of a restar or a fail If I have everything in json is easy to get back up? (I don't know anything about redis (or similar) and NoSQL so sorry about that, just trying to get all solutions I can so I can dig dipper) – LoadIt Nov 07 '18 at 12:51
  • At any rate, you cannot avoid the 2500*21*120 data, in any model. The only thing that differs is that some attributes might be unneeded. So the question is: How many stats does an item have in average? If it is 10, then ok, the eav gain in space(=disk/memory) *might* be more performant. But if it is something like 40 (33%) or more, I'd go with non-EAV. – George Menoutis Nov 07 '18 at 13:00
  • On average around 3 (only like .1% of the items have like 6/7) out of the 120 available. So in this case the EAV is the way to go right? – LoadIt Nov 07 '18 at 13:05
  • I still cannot be sure. CPU is so much more worth than disk space usually (memory doesn't come into question for this kind of join operation). You got the answer for both designs, you could test them a bit with your real data if you want. – George Menoutis Nov 07 '18 at 13:08
  • I'll do it for sure, thank you very mutch for your time. – LoadIt Nov 07 '18 at 13:10
  • Sorry to re-open but this is like a Many-to-Many relationship (witch is a EAV too right?) Does it mean that I should avoid many-to-many?) Where do I cross the line between going for a EAV vs Dynamic Collumns? I completly understood that joins have a mutch higher CPU cost, and memory is not even something to talk about because it will be very small anyway – LoadIt Nov 07 '18 at 13:31
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/183241/discussion-between-george-menoutis-and-loadit). – George Menoutis Nov 07 '18 at 13:32