Skip to content

Instantly share code, notes, and snippets.

@jdecode

jdecode/geartable-db-design.md

Last active September 26, 2020 22:53
Show Gist options
  • Save jdecode/e17653e47a56c537afac612765287f91 to your computer and use it in GitHub Desktop.
Save jdecode/e17653e47a56c537afac612765287f91 to your computer and use it in GitHub Desktop.
Download ZIP
Geartable DB design
Raw
geartable-db-design.md

This gist is to jump to the DB designing (without explaining the "Why/How DynamoDB" part).

  1. ERD - Entity Relation Diagram
  2. Define the Access Patterns
  3. Design PK/SK
@jdecode
Copy link
Author

jdecode commented Sep 26, 2020 •
edited

ERD

  1. Users
  2. Sheets
  3. APIs

geartable-erd

https://dbdiagram.io/d/5f6f2ad47da1ea736e2f6fcd

@jdecode
Copy link
Author

jdecode commented Sep 26, 2020 •
edited

Define/identify access patterns (not historically planned... not with a lot of thought at least), so this is the new/fresh version:

  1. Get user profile from email (using OAuth 2 social login, no "user-ID")
  2. Get all sheets for a user
  3. Get a sheet by ID for a user
  4. Get all APIs for a sheet for a user
  5. Get an APIs by sheet ID and API ID for a user

@jdecode
Copy link
Author

jdecode commented Sep 26, 2020 •
edited

Going back to "Entities" (from ERD above) and noting their respective PK/SK value formats

Entity PK SK
Users USER#<email> PROFILE#<email>
Sheets USER#<email> SHEET#<sheet-id>
APIs USER#<email> #SHEET#<sheet-id>#API#<api-id>

In the "APIs" entity, the SK is having an additional "#" as a prefix, to differentiate when a "Sheet" is being requested vs "APIs for all sheets" are requested.

@jdecode
Copy link
Author

jdecode commented Sep 26, 2020

Pseudo query: (only the "where" part)

  1. Get user profile from email (using OAuth 2 social login, no "user-ID")

    PK = USER#<email> AND SK = PROFILE#<email>

  2. Get all sheets for a user

    PK = USER#<email> AND SK "begins-with" SHEET#
    Notice that there is no "sheet-id" in the query

  3. Get a sheet by ID for a user

    PK = USER#<email> AND SK "begins-with"SHEET#<sheet-id>

  4. Get all APIs for a sheet for a user

    PK = USER#<email> AND SK "begins-with"#SHEET#<sheet-id>
    Notice that there is an additional "#" before "SHEET#"

  5. Get an APIs by sheet ID and API ID for a user

    PK = USER#<email> AND SK "begins-with"#SHEET#<sheet-id>#API#<api-id>

@jdecode
Copy link
Author

jdecode commented Sep 26, 2020 •
edited

NoSQL Workbench screenshot of the table

Screenshot from 2020-09-27 04-05-14

Other columns except PK/SK are set for Users only, for now.

Meta info of Sheets and APIs would be saved during run-time

@jdecode
Copy link
Author

jdecode commented Sep 26, 2020

I believe this is where I must stop from DB POV.

Moving on the the UI aspect in another gist (Tailwind + CakePHP).

For UX, Vue will be brought in, soon.

@jdecode
Copy link
Author

jdecode commented Sep 26, 2020 •
edited

Exported data model (as JSON) from NoSQL Workbench for DynamoDB

https://gist.githubusercontent.com/jdecode/d8249ec9516b9c7f7080c333ebcd3a21/raw/3b8e2d1c667971547de1fe48db07c32808157985/geartable-db-model-nosql-workbench.json

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment