have-a-nice-pickem-db

Rewrite of the Have A Nice Pickem Appplication Database in PostgreSQL

Database Design and Development Approach

1. PostgreSQL was chosen to get experience with another RDBMS other than MSSQL, Oracle SQL and MySQL.

2. Built upon then existing database schema to update and recreate the entity relationship diagram, using the dbdiagram.io VSCode extension.

3. Developed the create_tables procedure with the DDL to define a table for each entity from the ERD above, including primary and foreign key constraints.

4. Developed the create_constraints procedure and copied over the foreign key constraints originally defined in create_tables.

5. Developed the drop_constraints, drop_tables and drop_db procedures to remove all constraints or tables with one procedure call. This approach was a requirement in my database management graduate courses and has just remained a habit since.

6. Developed /scripts/load/ procedures to improve efficiency of data loading compared to the single row INSERTS in loops used in the past.

7. Developed the /scripts/views procedures to instantiate common queries that were executed against the database in last year's implementation

8. Installed Postgres using homebrew

   brew install postgresql
   

9. Instantiate local development instance of the database with the command

   createdb pickem_dev
   

10. Ran the initial setup scripts with pgAdmin