4 Added example
source | link

EXAMPLE Given a session that was created, then updated twice and finally completed, the following data is generated where update_id shows the order in which the changes to the event occurred:

session table

+-----------+------------+-----------+
| update_id | session_id |  status   |
+-----------+------------+-----------+
|         1 |          1 | STARTED   |
|         2 |          1 | STARTED   |
|         3 |          1 | STARTED   |
|         4 |          1 | COMPLETED |
+-----------+------------+-----------+

seesionlatest table:

+------------+-----------+--+
| session_id | update_id |  |
+------------+-----------+--+
|          1 |         4 |  |
+------------+-----------+--+

This way I know that if I lookup update_id 4 of session 1, that will be the latest (correct) view of the session. But I can still lookup the history of its changes.

Cardinalities

Cardinalities

EXAMPLE Given a session that was created, then updated twice and finally completed, the following data is generated where update_id shows the order in which the changes to the event occurred:

session table

+-----------+------------+-----------+
| update_id | session_id |  status   |
+-----------+------------+-----------+
|         1 |          1 | STARTED   |
|         2 |          1 | STARTED   |
|         3 |          1 | STARTED   |
|         4 |          1 | COMPLETED |
+-----------+------------+-----------+

seesionlatest table:

+------------+-----------+--+
| session_id | update_id |  |
+------------+-----------+--+
|          1 |         4 |  |
+------------+-----------+--+

This way I know that if I lookup update_id 4 of session 1, that will be the latest (correct) view of the session. But I can still lookup the history of its changes.

Cardinalities

3 added 13 characters in body
source | link

For some context: I am storing sessions in an append only log where each session will have an entrymultiple entries, (with all data of the session) one for each update.

For some context: I am storing sessions in an append only log where each session will have an entry (with all data of the session) for each update.

For some context: I am storing sessions in an append only log where each session will have multiple entries, (with all data of the session) one for each update.

2 Added full table creation details
source | link
CREATEcreate TABLEtable session (
  update_id                                     bigint NOT NULL                 not null
    constraint "PK_gameSession_update"
    primary key,
  event_id                                      bigint                   not null,
  event_type                                    varchar(255)             not null,
  time_in_millis                                timestamp with time zone not null,
  game_code                                     varchar(100)             not null,
  account_id                                    bigint,
  session_id                                    bigint NOT NULL                 not null,
  game_id                                       bigint                   not null,
  session_client_ref                            varchar(255)             not null,
  session_external_ref                          varchar(255)             not null,
  player_external_ref                           varchar(255),
  community_id                                  bigint                   not null,
  community_code                                varchar(255)             not null,
  device                                        text,
  type                                          varchar(50)              not null,
  country_code                                  varchar(10),
  game_after_state                              jsonb,
  accept_language                               char(5)                  not null,
  currency                                      varchar(255),
  status VARCHAR                                       varchar(50) NOT NULL            not null,
  ...provider_metadata                             jsonb,
  operator_code                                 varchar(255),
  jurisdiction                                  varchar(255),
  pending_round_id                              bigint,
  provider_managed_pending_round                boolean,
  time_started                                  timestamp with time zone not null,
  last_time_updated                             timestamp with time zone not null,
  action                                        varchar(30)              not null,
  seconds_to_idle                               bigint,
  provider_maximum_concurrent_sessions_per_game integer,
  referrer                                      text
);

create index "IDX_session_accept_language" on session (accept_language);
create index "IDX_session_account_id" on session (account_id);
create index "IDX_session_action" on session (action);
create index "IDX_session_community_code" on session (community_code);
create index "IDX_session_community_id" on session (community_id);
create index "IDX_session_country_code" on session (country_code);
create index "IDX_session_currency" on session (currency);
create index "IDX_session_game_after_state" on session (game_after_state);
create index "IDX_session_game_code" on session (game_code);
create index "IDX_session_game_id" on session (game_id);
create index "IDX_session_jurisdiction" on session (jurisdiction);
create index "IDX_session_last_time_updated" on session (last_time_updated);
create index "IDX_session_max_concurrent_sessions_per_game" on session (provider_maximum_concurrent_sessions_per_game);
create index "IDX_session_operator_code" on session (operator_code);
create index "IDX_session_pending_round_id" on session (pending_round_id);
create index "IDX_session_player_external_ref" on session (player_external_ref);
create index "IDX_session_provider_managed_pending_round" on session (provider_managed_pending_round);
create index "IDX_session_provider_metadata" on session (provider_metadata);
create index "IDX_session_referrer" on session (referrer);
create index "IDX_session_seconds_to_idle" on session (seconds_to_idle);
create index "IDX_session_session_client_ref" on session (session_client_ref);
create index "IDX_session_session_external_ref" on session (session_external_ref);
create index "IDX_session_status" on session (status);
create index "IDX_session_time_started" on session (time_started);
create index "IDX_session_type" on session (type);
create index "IDX_session_update_community_id" on session (update_id, community_id);
create index "IDX_session_update_partial_status_started" on session (update_id) where ((status) :: text = 'STARTED' :: text);
create index "IDX_session_session_id_player_external_ref"  on session (session_id, player_external_ref);
create index "IDX_session_session_id" on session (session_id);
CREATEcreate TABLEtable sessionlatest  
(
  session_id BIGINTbigint NOT NULL                 not null
    constraint "PK_session_id_latest"
    primary key,
  update_id BIGINT NOT NULL    bigint                   not null,
  event_id BIGINT NOT NULL     bigint                   not null,
  time_in_millis TIMESTAMP WITHtimestamp TIMEwith ZONEtime NOTzone NULLnot null
);

create index "IDX_sessionlatest_update_id" on sessionlatest (update_id);
CREATE TABLE session (
  update_id bigint NOT NULL,
  session_id bigint NOT NULL,
  status VARCHAR(50) NOT NULL,
  ...
);
CREATE TABLE sessionlatest (
  session_id BIGINT NOT NULL,
  update_id BIGINT NOT NULL,
  event_id BIGINT NOT NULL,
  time_in_millis TIMESTAMP WITH TIME ZONE NOT NULL
);
create table session(
  update_id                                     bigint                   not null
    constraint "PK_gameSession_update"
    primary key,
  event_id                                      bigint                   not null,
  event_type                                    varchar(255)             not null,
  time_in_millis                                timestamp with time zone not null,
  game_code                                     varchar(100)             not null,
  account_id                                    bigint,
  session_id                                    bigint                   not null,
  game_id                                       bigint                   not null,
  session_client_ref                            varchar(255)             not null,
  session_external_ref                          varchar(255)             not null,
  player_external_ref                           varchar(255),
  community_id                                  bigint                   not null,
  community_code                                varchar(255)             not null,
  device                                        text,
  type                                          varchar(50)              not null,
  country_code                                  varchar(10),
  game_after_state                              jsonb,
  accept_language                               char(5)                  not null,
  currency                                      varchar(255),
  status                                        varchar(50)              not null,
  provider_metadata                             jsonb,
  operator_code                                 varchar(255),
  jurisdiction                                  varchar(255),
  pending_round_id                              bigint,
  provider_managed_pending_round                boolean,
  time_started                                  timestamp with time zone not null,
  last_time_updated                             timestamp with time zone not null,
  action                                        varchar(30)              not null,
  seconds_to_idle                               bigint,
  provider_maximum_concurrent_sessions_per_game integer,
  referrer                                      text
);

create index "IDX_session_accept_language" on session (accept_language);
create index "IDX_session_account_id" on session (account_id);
create index "IDX_session_action" on session (action);
create index "IDX_session_community_code" on session (community_code);
create index "IDX_session_community_id" on session (community_id);
create index "IDX_session_country_code" on session (country_code);
create index "IDX_session_currency" on session (currency);
create index "IDX_session_game_after_state" on session (game_after_state);
create index "IDX_session_game_code" on session (game_code);
create index "IDX_session_game_id" on session (game_id);
create index "IDX_session_jurisdiction" on session (jurisdiction);
create index "IDX_session_last_time_updated" on session (last_time_updated);
create index "IDX_session_max_concurrent_sessions_per_game" on session (provider_maximum_concurrent_sessions_per_game);
create index "IDX_session_operator_code" on session (operator_code);
create index "IDX_session_pending_round_id" on session (pending_round_id);
create index "IDX_session_player_external_ref" on session (player_external_ref);
create index "IDX_session_provider_managed_pending_round" on session (provider_managed_pending_round);
create index "IDX_session_provider_metadata" on session (provider_metadata);
create index "IDX_session_referrer" on session (referrer);
create index "IDX_session_seconds_to_idle" on session (seconds_to_idle);
create index "IDX_session_session_client_ref" on session (session_client_ref);
create index "IDX_session_session_external_ref" on session (session_external_ref);
create index "IDX_session_status" on session (status);
create index "IDX_session_time_started" on session (time_started);
create index "IDX_session_type" on session (type);
create index "IDX_session_update_community_id" on session (update_id, community_id);
create index "IDX_session_update_partial_status_started" on session (update_id) where ((status) :: text = 'STARTED' :: text);
create index "IDX_session_session_id_player_external_ref"  on session (session_id, player_external_ref);
create index "IDX_session_session_id" on session (session_id);
create table sessionlatest 
(
  session_id bigint                   not null
    constraint "PK_session_id_latest"
    primary key,
  update_id       bigint                   not null,
  event_id        bigint                   not null,
  time_in_millis  timestamp with time zone not null
);

create index "IDX_sessionlatest_update_id" on sessionlatest (update_id);
1
source | link