Spells Data Revisited

[John Adams]

Before we get started on rebuilding all the spells data, I wanted to get opinions on how we should approach this. Right now I see 2 choices:

• 1. Insert each new record for {Spell} {Line} {Tier} as it's own record (tables remain as they are) or
  2. Re-design Spells to be just {Spell}, add a new table for {Line} and link that to the existing {Tier} data

Option 2 will cut down on much of the main `spells` table data, but will add just as many `spell_lines` and `spell_tier` entries. The only real advantage of Option 2 is that the "text" values will not be duplicated dozens of times per spell line/tier.

I can go either way, and we could in fact revisit this at a later date. For now, I added it to ProjMan, Database, Spells, as an objective in 0.7.1 for me to deal with. Before I spend any time on it though, I need opinions.

Ready? Go.

[John Adams]

Oh, forgot one other idea I had, which could strictly be an EQ2Emu thing. Instead of hard-coding a bunch of values for what effects a spell might have, use a baseline and each tier/line would increase the effects as a variant, maybe defined in Rules.

Example, if a spell causes 10 damage at Spell_tier1, if you get Spell_tier2 you multiply 10 damage * .02 or something to give you a new possible max damage for the tier 2 version. Same with lines.

But, that's just a customization option. I doubt SOE does it that way, since database space is cheap

[Scatman]

Doh. I tried to look at one of my servers database but it had no spells. I need to refresh my memory a little on how exactly how spells are stored now and exactly how that differs from #2 before i decide

[Zcoretri]

My vote is to keep it the way is now, I don't really see the advantage to normalizing text.
Not to hard to copy and paste a description for 7 to 9 spells in a line

[John Adams]

My vote is to keep it the way is now, I don't really see the advantage to normalizing text.
Not to hard to copy and paste a description for 7 to 9 spells in a line

Okay, Z. In this, your vote is the one I rely on most since you know the spells data better than us all. For now we can proceed with what we have, though you know redundant data makes my skin itch... I reserve the right to change my mind, later

[Scatman]

I am highly against redundant data. IMO anything we can do to reduce redundant data is a good thing.

[John Adams]

Yeah, I gave this much further thought last night as I was sitting here like a zombie...

Zcoretri, I think we need to pursue another option. We're not quite ready for Spells v2.0 anyway yet, and have plenty of time to consider our options. Fact is, we already have 1 parent `spells` and multiple child `spell_tiers` records for it... there is no reason we cannot slip `spell_lines` or something to that effect in there.

Let's take our time, not rush into Spells data right now, and give us some time to come up with something solid that makes us look like we know what we're doing I don't think I can live with 40,000 records when we only need a few thousand.

[John Adams]

a_dead_topic says, "Hello, World!"

Yes, it is that time to /necro this topic. I am currently ass-deep in a spells table redesign, including (possibly) merging Traits data into the parent Spells table, but so far I am not convinced that is the best course of action. In fact, it might make more sense to ADD tables to support the other types of spells/abilities to come - spell_traditions, spell_altadv, spell_yourmom, etc. Still mulling that one over.

The only thing really relevant to me right now is to not have (example) Heal I - Heal IX: 9 separate records for a spell line called Heal. Because each one of these also has up to (7?) tiers, that's just a ton of data for World to have to manage.

My current design, which I am second guessing a little.

new_spells.jpg

`spells` - add columns to support Traits, Traditions and AA, the latter being merely flags so we can easily identify them from normal spells.

`spell_tiers` - add spell_line_level which represents the current level for a spell in a line. Spell lines will maintain their separate tiers and unique data settings this way, and it is much less data than repeating varchars 1,000's of times.

`spell_levels` - (used to be spell_classes) adding race_id which is used for race-based spells/abilities (absorbing the remaining setting from spell_traits)


Other changes to these table structures will be my first attempt to get away from using MySQL Reserved Words/Commands in our column names as well.

Please think about this, and let me know if I've forgotten anything before I start making code changes.



EDIT: I mocked up the alternate changes - creating new tables for each spell/ability "type". As you can see, it's a little more convoluted, but might be easier to manage -

new_spells2.jpg

Note: the Lines, Traditions and AA tables are placeholders - not sure what to put in them yet.


Thanks

[Zcoretri]

The only thing really relevant to me right now is to not have (example) Heal I - Heal IX: 9 separate records for a spell line called Heal. Because each one of these also has up to (7?) tiers, that's just a ton of data for World to have to manage.

How are you gonna handle displaying the spell name for Heal II, Heal III ... Heal X?

[John Adams]

Once we fetch data from the DB, we should be able to manipulate it any way we want before shoving it into a packet.

eg., if it is Heal, and we're sending spell line level 6, we concat "Heal" + " VI" in code then build the packet with the new name value.

[John Adams]

Zcoretri and I talked about this again the other day, and while I keep flip-flopping back and forth, I am really nervous about duplicating this much data in our database, and in memory to display.

Here is an example of just the string values:
http://data.soe.com/jml/get/eq2/spell/?name=Charm

"description": "Grants the enchanter control over target opponent. The opponent has a recurring chance to resist the charm throughout its duration. Higher ranks of this spell will decrease the chance at a periodic resist."

"description_pvp": "Grants the enchanter control over target opponent. The opponent has a recurring chance to resist the charm throughout its duration. Higher ranks of this spell will decrease the chance at a periodic resist."

Aside from the name - Charm, Charm II, III, ... VII, which eventually end up looking like this in our database -

spells1.jpg

You cannot really be okay with that not to forget a 2nd description for the PVP version of the spell (see above). Maybe they are not different, but SOE seems to send them both...


After staring at SOE's RESTful data feeds a little longer, I've finally decided I want to rebuild our spells table structures entirely from scratch, using their naming conventions for tables and fields.

When I get something put together, I will share it - until then, Spells can stay as they are - but I recommend we not work extensively on the Spells/Abilities lists (except ongoing Character Development stuff) because I am going to nuke the data anyway in lieu of live data from SOE (I have a plan to actually update our tables directly from SOE's public-facing feeds )