Data Model - Further Exploration
My goal for today is to complete my data model. After three thorough reviews by three different pairs of eyes, I'm at a cozy pre-final-draft. I say 'pre-final' because I'm anticipating minor tweaks as my project progresses.
Key Feedback:
The naming of certain tables and columns were confusing so they've been updated.
The nullability of a column defaults to true, meaning I only need to be explicit about columns that cannot be nullable.
There are two characters types that SQL allows for storage: character(n) and character varying(n). Should the inputted string be shorter than what is specified by n, char(n) will store the string with pad whitespace where varchar(n) will store the shorter string as is. PostgreSQL provides the dandy TEXT data type that allows for unlimited characters. According to PostgreSQL documentation, there isn't a significant difference in performance between the three types, so picking TEXT is a no-brainer as I want my users to be able to store an unlimited amount of notes.
The preferences (likes/dislikes, goals, favorites, etc.) in the relationships table needed to be normalized. For example, the user will probably want to store several goals for one of their friends. I can store each goal as a new row, a new column, or by a new table. A new table makes sense because every piece of information should live in exactly one place and relate to directly to the key. This new table, named Recommendation Relationship, represents the idea of assigning one relationship to one recommendation. More on this later.
DATE vs. DATETIME: The birthday field should be stored as DATE instead of DATETIME as the latter includes the time and timezone information, which is unnecessary for this field.
The primary key id column is verbose. Hackbright convention specifies that the key should be "tablename_id" instead of "id". We have been informed that either is acceptable, and consistency is key.
One of the first things to consider about a data model is the cardinality of each of the tables' relationships. The cardinality refers to the uniqueness of data values contained in a column. Is it a one-to-many or many-to-many relationship?
Users have many relationships. Each relationship has one user. (one-to-many)
Users have many recommendations. Each recommendation has one user. (one-to-many)
Users have many events. Each event has one user. (one-to-many)
Each relationship has many recommendations. Each recommendation has many relationships. (many-to-many)
Each relationship has many events. Each event has one relationship. (one-to-many)
Each recommendation has many events. Each event has one recommendation. (one-to-many)
The User table stores information about each user. This includes their user_id, first and last name, email (which will be their username for logging in), password and birthday.
The Event tables stores information about each individual event. It includes the user_id, date, relatp_id (which points to all the information stored about the users relationship with that contact) and description of the event.
The Relationship table stores information about all of the users contacts. It includes the following:
relatp_id
relatp_code: a users brother will have a different code than a users friend, etc.
user_id: points to which user a contact belongs
general info: separately - email, bday, phone, work, education
social media: separately - Facebook, LinkedIn, Twitter, Google+, MeetUp, Github, Pinterest, Reddit, Word Press, Yelp, YouTube, Skype, other
gift ideas
goals
notes
pets
family
hobbies
likes
dislikes
pet peeves
most recent convo: removed from my draft because this info can be found by querying the 'convo_log' field in this very table
favorites: separately - foods, drinks, restaurants, sports teams, brands, other
convo_log: notable conversations the user had with that person
traits: record personality traits to keep in mind about a person
The Recommendation table stores the information about the different tips of reaching out. It includes the following:
rcmdn_id
relatp_code: points to which type of relationship the recommendation is assigned
rcmdn_text: the recommendation itself i.e. send <contact> an email, grab a drink with <contact>, etc.
Finally, I need a table that ties together my Recommendation and Relationship table. A recommendation can have many relationships and a relationship can have many recommendations. I need a place to store that information so that I'm not storing redundant information (normalization). Association tables are used for this exact purpose. This association table will be called Recommendation Relationship, as it is the glue between those exact tables. It will include it's own id, and use foreign keys to reference the rcmdn_id and the relatp_id from their respective tables.
The army of mini tables are a place for me to store the individual items stored under each field. For example, if five gift ideas were inputted, there would be no way for me to access the second gift idea, because the field becomes a string. The only way for me to access each element is by creating a separate table. Normalization is at work again here.
