Third Party Catalogues - cressie176/Load64 GitHub Wiki

1. Overview

┌───────────────────────────────────┐          ┌──────────────────────────────────────────────────────────────────────────┐
│                                   │         ╱│                                                                          │
│       ThirdPartyCatalogue         │┼○──────○─│                      ThirdPartyCatalogueEntry                            │
│                                   │         ╲├──────────────────────────────────────────────────────────────────────────┤
├───────────────────────────────────┤          │ id: integer [pk]                                                         │
│                                   │          │ game_id: integer [fk, unique(1)]                                         │
│ id: integer [pk]                  │          │ catalogue_id: integer [fk, unique(1)]                                    │
│ name: text [unique, required]     │          │ catalogue_entry_id: text [unique(catalogue_id), required]                │
└───────────────────────────────────┘          └──────────────────────────────────────────────────────────────────────────┘
                                                                            ╲│╱
                                                                             ○
                                                                             │
                                                                             │
                                                                             ┼
                                                             ┌───────────────────────────────┐
                                                             │                               │
                                                             │             Game              │
                                                             │                               │
                                                             └───────────────────────────────┘

A ThirdPartyCatalogue represents a named external game catalogue such as GameBase64. A ThirdPartyCatalogueEntry records the identifier by which a LoadC64 game is known in that catalogue.

A game may be linked to at most one entry per catalogue. The catalogue_entry_id is the identifier used by the third party catalogue (e.g. a GameBase64 numeric ID) and must be unique within that catalogue.

2. Tables

ThirdPartyCatalogue

Field	Type	PK	FK	Nullable	Unique	Description
id	integer	yes				Surrogate primary key.
name	text				yes	Name of the third party catalogue (e.g. `"GameBase64"`).

ThirdPartyCatalogueEntry

Field	Type	PK	FK	Unique	Description
id	integer	yes			Surrogate primary key.
game_id	integer		Game.id	1	The LoadC64 game this entry links to.
catalogue_id	integer		ThirdPartyCatalogue.id	1	The third party catalogue id.
catalogue_entry_id	text			unique(catalogue_id)	The identifier for the game in the third party catalogue.

3. Example

third_party_catalogue

id	name
1	GameBase64

third_party_catalogue_entry

id	game_id	catalogue_id	catalogue_entry_id
1	1	1	243
2	2	1	8765
3	3	1	412