PostgreSQL prototyping

postgresql.txt

PostgreSQL:
user A (non-super-user admin account with createdb and createuser)

destiny database:
# main destiny database
user X (job account with read/write)
user Y (api account with read-only)

alliance table:
# Manually created reference info for "meta-clan" groupings
id (int) *primary key*
name (text)
members (jsonb) array of uint32 Bungie clan groupIds

group table:
# Destiny clan info built from alliance.members array of groupId values for each alliance in alliance table
id (bigint) *primary* key which is a Bungie clan groupId
alliance_id (int) *references* alliances entry
updated (timestamp)
detail (jsonb) GroupV2.GetGroup.Response.detail object
founder (jsonb) GroupV2.GetGroup.Response.founder object
members (jsonb) GroupV2.GetMembersOfGroup.Response.results object

account table:
# Destiny account info built from group.members array of objects for each group in group table
membershiptype (int) *composite primary key1* Destiny membershipType from group.members.[index].destinyUserInfo.membershipType value
membershipid (bigint) *composite primary key2* Destiny membershipId from group.members.[index].destinyUserInfo.membershipId value
group_id (bigint) *references* groups entry
alliance_id (int) *references* alliances entry
displayname (text) Destiny displayName from group.members.[index].destinyUserInfo.displayName value
iconpath (text) Destiny iconPath from group.members.[index].destinyUserInfo.iconPath value
updated (timestamp)
accountstats (jsonb) Destiny2.GetHistoricalStatsForAccount.Response object
characterdata (jsonb) Destiny2.GetProfile.Response.characters.data object
characterstats (jsonb) Destiny2.GetHistoricalStats.Response object (meta object built from multiple requests)

# follow @dad2cl3 concept of manifest handling?
manifest_version table:
manifest table:
manifest_stage table:
# Remember to ask why the stage table is needed

The manifest table allows me to blast the contents of the new manifest file into a table and do large operations on the data using DML instead of row-by-row operations. After staging the entire file, a stored procedure on the database is called to execute three DML statements. The first inserts new records, the second updates existing records with changes, and the third marks records that have been deleted.

Attached is a view of the current model outlined above. The only difference is I moved the alliance_id out of the account table.

The attached screenshot is a suggestion on how to capture the character stats from GetHistoricalStatsForAccount that gives a fair amount of flexibility to aggregating data.

effective_date : Current date on which the stats were pulled. The field can be dropped if there is no desire to track changes over time. Example: 2017-10-16
destiny_character : Character to whom the stats belong represented as JSON in the database. Example:

{ "deleted": false, "destinyId": "4611686018441650450", "characterId": "2305843009269814818", "destinyMembershipType": 2, "classHash": "2271682572" }

game_play_format : Represents the game play format from which the stat was pulled. Examples: PvE, PvP, Merged
stat_id : Represents the key field in the Destiny SQLite manifest table DestinyHistoricalStatsDefinition. Example : highestLightLevel
stats : The actual stats for the individual stat per character. Example:

{ "basic": { "value": 279, "displayValue": "279" }, "statId": "highestLightLevel" }