From The Mana World
(Database Schema Recommended Changes) |
|||
| Line 1: | Line 1: | ||
I think it is generally a bad idea to use the player's | 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 | * 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 | * 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 | * 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 | 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. | |||
Revision as of 15:59, 20 November 2005
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.
