Archive talk:Database specifications

I think it is generally a bad idea to use the player's character name as the primary key that links together the tables for the following reasons:

• Renaming the player will be unnecessarily difficult (since the character name will have to be updated in almost every table) (yes, I realize you could setup a cascading update to update the character name foreign key in every table; however, this is completely unnecessary and a waste of resources)
• Data storage is massively redundant, which defeats the purpose of a relational database
• The currently proposed schema will require 32 bytes of storage for every record in almost every table just for the primary key

A substantially better design would be a Characters table which contains a Unique Identifier (auto-number, GUID, or other similar field type), this revised schema would only require between 4-16 bytes per record per table and allow the character name to be updated in a single place.

This same concept would apply to items, weapons, monsters, NPCs, or any other similar table.

Table Naming

I think the table names should avoid using the "tmw" prefix for the following reasons:

• There was a discussion in the forum about renaming the project from "The Mana World" to something else (which would require a substantial amount of work later on, since all database table names and queries (used by both the server application and website) would have to be updated/changed
• Since this is an open source project, other developers may want to use the database/server/client as a starting point for their own projects, in which case we are creating unecessary work for them by placing "tmw" prefix on table names
• Other projects that use TMW source/structure may generate code that could be merged back into TMW; however, if the table names had "TMW" and the other projects removed it, additional work will be necessary for the re-integration of the code