model.py

Today's Goal: Complete the code version of my data model that becomes the skeleton of my database (model.py).

Detailed Action Plan:

• define all classes with the respective columns

• define the data type of each column

• specify nullability and defaults of each column

• designate appropriate foreign keys and backrefs

• establish database.sql to test the code after every class in model.py

• use INSERT INTO () VALUES() for each table/class as I go as another method of testing

Process:

After I write out one class in model.py, I CREATE TABLE in my database.sql and INSERT INTO () VALUES() at the bottom, head to the Terminal, dropdb, createdb, dump the SQL file into my database, read the error messages, Google, implement changes and repeat.

To best describe my learnings for the day, I've created a list of my errors/bugs and the respective solutions:

1. How do I specify the birthday field to have the data type DATE in SQLAlchemy?

• db.Date

1. Is there a specific data type need for the phone number field?

• Depends on the types of phone numbers that will be stored. Some phone numbers have words (1-800-CALL-MEME) and some will be international numbers that include '+' at the beginning. Some articles recommend TEXT and others VARCHAR. My app is not meant to be a storage area for phone numbers, so I stuck to VARCHAR.

1. 'ERROR: relation 'rcmdnsRelaps' does not exist' is displayed in Terminal when I'm trying to create my RecommendationRelationship table. Why?

• This error has to do with the case-sensitivity of characters in SQL. Characters within quotes are considered case-sensitive while characters not in quotes are not case-sensitive. The table name that I specified in PostgreSQL is 'rcmdnsRelatps' and in SQL under CREATE TABLE, the table name is not encapsulated in quotes so it was interpreted as 'rcmdnsrelatps'. To resolve this issue, I renamed the table to 'rcmdns_relatps'.

1. How do I insert values for a data type DATE field?

• to_date('1991-02-27', 'YYYY-MM-DD')

1. How do I insert values for a data type DATETIME field?

• to_timestamp('2002-12-27 08:32:57', 'dd/mm/yyyy hh24:mi.ss.ff')

1. How do I insert values for a data type DATETIME field?

• Use single quotes, NOT double quotes. Using double quotes will throw: "ERROR: column "Tickets to Stephen Colbert Show" does not exist".

More later!