Database Schema

Database Schema

Users

| column name | data type | details |-------------------------------- |id | integer | not null, primary key |user_url | string | not null, indexed |firstname| string | not null |lastname| string| not null |email |string | not null, indexed, unique |bio| text| optional |profile_img_id | integer | optional |cover_photo_id | integer | optional |friend_ids | integer| array: true, default:[] |chat_ids | integer | array: true, default: [] |password_digest | string | not null |session_token | string | not null, indexed, unique |created_at | datetime | not null |updated_at | datetime | not null

• index on user_url, unique: true
• index on session_token, unique: true

note: I think that user url should be set automatically, it is the only unique attribute (as far as I'm aware) on the user model - used for profile url.

Posts

| column name | data type | details |----------------------------------- |id | integer | not null, primary key |body |string | not null |photo_ids| integer| array: true, default: [] |author_id |integer |not null, indexed, foreign key |created_at |datetime |not null |updated_at |datetime |not null

• index on author_id

Comments

|column name |data type| details |---------------- |id |integer| not null, primary key |user_id |integer |not null, indexed, foreign key |post_id |integer |not null, indexed, foreign key |created_at |datetime |not null |updated_at |datetime |not null

• index on post_id

• Add an optional parent comment ID in the future
• Add likes in the future - should there just be likes or should there be comment and post likes as separate models?

Photos

|column name |data type| details |---------------- |id |integer| not null, primary key |owner_id |integer |not null, indexed, foreign key |url |string |not null, unique: true |created_at |datetime |not null |updated_at |datetime |not null

• index on owner_url
• index on url, unique: true

possible additions: tag_ids(users), comments, likes

Chats

| column name | data type | details |----------------------------------- |id | integer | not null, primary key |created_at |datetime |not null |updated_at |datetime |not null

Messages

|column name |data type| details |---------------- |id |integer| not null, primary key |sender_id |integer |not null, indexed, foreign key |chat_id |integer |not null, indexed, foreign key |created_at |datetime |not null |updated_at |datetime |not null

• index on chat_id
• when a user sends a message, it is associated with one chat that many users can potentially subscribe to
• the user that creates the chat can add members to the chat - the controller will modify the target users to subscribe them to the chat when it's created
• once added to a chat, any user can add new users to the conversation