Database Schema - Sisysphus/Meadium GitHub Wiki

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
firstName	string	not null
lastName	string	not null
createdAt	datetime	not null
updatedAt	datetime	not null

• unique index on username
• unique index on email
• Sequelize hasMany Stories association
• Sequelize hasMany Cheeres association
• Sequelize hasMany Comments association
• Sequelize belongsToMany Stories through Cheers as CheeredStories association
• Sequelize belongsToMany Stories through Comments as CommentedStories association

Stories

column name	data type	details
id	integer	not null, primary key
title	string(100)	not null
body	text	not null
authorId	integer	not null, indexed, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• authorId references Users table
• index on authorId
• Sequelize belongsTo Users association
• Sequelize hasMany Cheers association
• Sequelize hasMany Comments association
• Sequelize hasMany Pins association
• Sequelize belongsToMany Users through Cheers as CheeredUsers association
• Sequelize belongsToMany Users through Comments as UserComments association
• Sequelize belongsToMany Users through Pins as UserPins association

Cheers

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

• userId references Users table
• storyId references Stories table
• unique index on [storyId, userId]
• Sequelize belongsTo Users association
• Sequelize belongsTo Stories association

unique index on [storyId, userId] will not allow a story to be liked by the same user more than once.

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

Comments

column name	data type	details
id	integer	not null, primary key
comment	text(500)	not null
commenterId	integer	not null, indexed, foreign key
commentedOnId	integer	not null, indexed, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• commenterId references Users table
• commentedOnId references Stories table
• index on commenterId and commentedOnId
• Sequelize belongsTo Users association
• Sequelize belongsTo Stories association

Hungover (Formerly Pinned)

column name	data type	details
id	integer	not null, primary key
pinnerId	integer	not null, indexed, foreign key
pinnedStoryId	integer	not null, indexed, foreign key
createdAt	datetime	not null
updatedAt	datetime	not null

• pinnerId references Users table
• pinnedStoryId references Stories table
• index on pinnerId and pinnedStoryId
• Sequelize belongsTo Users association
• Sequelize belongsTo Stories association