Database Schema

Schema for PostgresQL using Sequelize ORM

This project has 6 tables: Users, Laughs, LaughBoxes, LaughBoxLaughs, Ratings, and Reviews

Users

column name	data type	details
id	integer	not null, primary key
username	string	not null, indexed, unique
email	string	not null, indexed, unique
hashedPassword	string	not null
createdAt	datetime	not null
updatedAt	datetime	not null

• unique index on username
• unique index on email
• Sequelize hasMany Laughs association
• Sequelize hasMany LaughBoxes association
• Sequelize hasMany Ratings association
• Sequelize hasMany Reviews association

Laughs

column name	data type	details
id	integer	not null, primary key
body	string	not null, indexed
userId	integer	not null, indexed, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• userId references Users table id
• index on userId
• unique index on [body, authorUserId]
• Sequelize belongsTo Users association
• Sequelize hasMany Ratings association
• Sequelize hasMany Reviews association
• Sequelize hasMany LaughBoxes association
• Sequelize belongsToMany LaughBoxLaughs through LaughBoxLaughs as LaughsInLaughBox association

unique index on [body, userId] will only allow a laugh to be created by the same user once.

LaughBoxes

column name	data type	details
id	integer	not null, primary key
name	string	not null, indexed, unique
userId	integer	not null, indexed, foreignKey
createdAt	datetime	not null
updatedAt	datetime	not null

• unique primary key on id
• unique index on [name, userId]
• foreign key on userId connects to Users table on id
• name is the name of the LaughBox
• Sequelize hasMany LaughBoxLaughs association
• Sequelize belongsTo Users association
• Sequelize belongsToMany Laughs through LaughBoxLaughs as LaughsInLaughBox association

unique index on [name, userId] will only allow a laughbox of a particular name to be created by the same user once.

We don't need a separate index for name or userId because the index on [name, userId] adds it for us.

LaughBoxLaughs

column name	data type	details
id	integer	not null, primary key
laughId	integer	not null, indexed, foreignKey
laughBoxId	integer	not null, indexed, foreignKey
createdAt	datetime	not null
updatedAt	datetime	not null

• unique primary key on id
• foreign key on laughId connects to Laughs table on id
• foreign key on laughBoxId connects to LaughBoxes table on id
• unique index on [laughId, laughBoxId]
• Sequelize belongsTo Laughs
• Sequelize belongsTo LaughBoxes

unique index on [laughId, laughBoxId] will only allow a laugh to be added to a laughbox once.

We don't need a separate index for laughId or laughBoxId because the index on [laughId, laughBoxId] adds it for us.

Ratings consist of Bows(boolean) and Lols (integer)

column name	data type	details
id	integer	not null, primary key
bows	boolean	not null
lols	integer	not null
userId	integer	not null, indexed, foreign key
laughId	integer	not null, indexed, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• userId references Users table
• laughId references Laughs table
• unique index on [laughId, userId]
• 'lols' is 1 to 5
• Sequelize belongsTo Users association
• Sequelize belongsTo Laughs association

unique index on [laughId, userId] will only not allow a laugh to be liked by the same user once.

We don't need a separate index for laughId or userId because the index on [laughId, userId] adds it for us.

Reviews

column name	data type	details
id	integer	not null, primary key
body	string	not null
userId	integer	not null, foreignKey
laughId	integer	not null, foreignKey
createdAt	datetime	not null
updatedAt	datetime	not null

• unique primary key on id
• name is the name of the LaughBox
• unique index on [laughId, userId]
• foreign key on userId connects to Users table on id
• foreign key on laughId connects to Laughs table on id
• Sequelize belongsTo Users association
• Sequelize belongsTo Laughs association