Databases

1. Tables
   1. Organization
      1. One table similar to spreadsheet
      2. Power with many related tables
   2. Terms
      1. Columns = fields/attributes
      2. Rows = records
      3. Key = unique field per record
      4. Compound key = combination of fields guaranteed to be unique
   3. Compared to components
      1. Each table = entity or component
2. Queries
   1. SQL = structured query language
      1. SELECT fields FROM table
      2. WHERE field meets condition
      3. JOIN table ON field = field
      4. UPDATE table SET ... WHERE ...
      5. DELETE ...
      6. INSERT ...
   2. Compiled vs. run-time queries
   3. Compare vs. noSQL
      • Key/value most relevant (vs. document, graph, etc.)
3. Normal forms
   1. Formalize & remove redundant / unnecessary data
      1. Make easier to do atomic updates
      2. Make easier to extend
   2. 1st normal form
      1. Dense tables
         • Like components: add tables mapping ID to optional values
      2. No repeating columns
         • Additional mapping table rather than list
   3. 2nd normal form
      1. No column dependent on a subset of the key
      2. So if key = full name, no column for first name
   4. 3rd normal form
      1. Attributes determined by keys, not other data in table
      2. e.g. HP and isDead: isDead iff HP=0
      3. also car model and manufacturer: model implies manufacturer
   5. Boyce-Codd Normal Form (BCNF)
      1. Remove "functional dependency"
      2. If can be derived by function, don't put in the database
   6. 4th normal form
      1. Remove multivalued dependencies
      2. Split tables rather than have product of entries (e.g. size x color)
   7. 5th normal form
      1. Split tables if combinations of attributes aren't allowed
      2. Orc weapon vs. human weapon example
   8. 6th normal form
      1. Consider temporal updates
      2. Split static from update
4. Database advantages
   1. Data Persistence
      • Data is already in a file
   2. Consistency guarantees
      • Atomic transactions
   3. Scalability
      • Cloud
   4. Query optimization
      • Ability to create indices to accelerate queries
5. Database use
   1. Configuration data
      1. Easy to change/update
      2. Easy to access with external tools
   2. Internationalization
      1. String table per language, same keys, different data
      2. Language as part of key in table for all languages
      3. Maybe different asset tables too (limits on blood, guns, etc.)
   3. Game state
      1. Always saved
      2. Must put all state into database
   4. Multi-player
      1. Database holds shared state
      2. Local client (or database) for non-shared state
   5. Updates (esp. mobile games)
      1. Makes core game smaller
      2. Load just active subset of data
      3. Update on the fly (new items, new characters, new locations)
      4. Game update for database changes
      5. Game must be able to function with new unrecognized data
   6. Scalability
      1. Amazon & cloud popular
      2. Add servers as game/app popularity grows
6. From Massively Multiplayer Game Development
   1. Data driven reduces cost of maintenance & updates
   2. Import sources
      1. Text files
      2. Spreadsheet
      3. XML
      4. Scripting language generated
   3. Indexing
      1. Index columns used in conditional requests
      2. Can drastically accelerate multi-column queries
      3. "all players that have hit points > 80, belong to a guild, and are not a member of a group"
      4. Slow down updates, so use carefully
   4. Performance
      1. Avoid joins for more than 2-3 tables
      2. Queue deletes (mark or put into a delete table to batch process)
      3. Database maintenance, including deferred updates, in off hours
      4. Cache data (in server process, in client)
      5. Track transaction loads to identify needed indices, caches
      6. Pool connections, opening & closing is expensive
   5. Caching concerns
      1. Predict on client based on cached state
         • Position, direction, velocity, ...
      2. Update client to server reality
         1. Jump
         2. Pull on spring
         3. What about hit to miss or miss to hit?
      3. Update server to client reality
         1. Design uncertainty into client
         2. I see you in my crosshairs, you can't tell if you were or not
         3. Roll back events and replay according to new reality
   6. Multiple servers
      1. Login, character, object, guild, NPC, region
      2. Regions
         1. Size limit for load
         2. Limit visibility to < 1 tile
         3. Client needs to know about neighboring tiles
         4. Proxy objects in border
         5. Soft vs. hard borders
      3. Seamless
         1. Cluster for interaction
         2. Rebalance as needed
         3. Server may be more involved in updates