About DB Structure

TrinityCore Development TrinityCore Code Discussion
1

By having a look at how the DB is structured, I think it could benefit from a more detailed structure; in particular I think that some data management that is done within the core should be done by the Database Management System (in this case MySQL); for example I think two things should be enhanced:

  1. Data integrity: for example all characters tables should be linked by “ID”, so that deletion and update to characters are applied in cascade to all related tables.

  2. Views: I think that both the core and DB users may benefit from some basic views, which would serve as a sort of “APIs” for third party scripts and programs, like for example Web platform to manage characters and so on.

With the developing of the new features of the latest expansion I think that giving a more consistent structure would definitely be a great benefit.

What are opinions on these topics?

2

I’m not sure if we need a new layer between the db and core however we definitely need to do something regarding data integrity.

Adding foreign keys has been partially discussed in http://www.trinitycore.org/f/topic/10491-why-doesnt-the-db-use-triggers-for-cascaded-deletes

3

Well let’s hope you have more luck with this than the last few times it has been proposed…

4

I’m sorry, I did search previous posts but I couldn’t find this one. However I don’t get the discussion about the performance of MyISAM since, as Wikipedia states, it is no longer under active development, so maybe alternatives should be found if effectively InnoDB is much less performance oriented. By the way, as you also said, it is not a particularly important feature for world database while I think it is crucial for characters and auth.

5

Not sure where you read that MyISAM is no longer developed (can’t find that statement in http://en.wikipedia.org/wiki/MyISAM). Anyway, in MySQL and in my computer, MyISAM seems to be way faster than InnoDB when loading the world db (since it’s mostly reads). I should give a try to MariaDB soon.

Even if we added FKs to the world db, there’s one ‘little’ problem: we would have to change lots of '0’s to null (http://stackoverflow.com/questions/9759413/foreign-key-that-can-also-be-zero) and that would mean changing wdb/sniff data.

6

That’s where I read it, but maybe it’s just one of Wikipedia’s inconsistencies… Wikipedia

And from the statements in this page, MyISAM appears to have much more platform-related performances than InnoDB.

I can’t imagine where could it happen, could you show any example of this situation? Maybe in that cases foreign key should be avoided, or maybe there shouldn’t be at all.

7

Example: creature_template table has a field named questItem1 that is used to tell the client that this creature drops an item X.

If we used foreign keys, we would link questItem1 with item_template.id.

questItem1 with the value 0 (meaning no item drop) would error since there’s no itemId 0.

8

Ok I can’t find a proper solution for that cases, but there are some things that could be done:

  1. Adding dummy rows with value 0 in referenced tables (not sure if it can be done in all the cases)[/li] [li]Just ignore these situations and start defining foreign keys for tables where this problem can’t occur by design (like almost every table in character DB etc…)