This is a mysql database that is created through sqlalchemy. I really don't want to rewrite all of it in sql code, but the code should be understandable if you know a tiny bit of MySQL.

I am designing a database for a social media like site as a side project for learning how to handle larger projects alone, but I am kind of new to this and would like suggestions on how I should change the design of this to fit more of a twitter-ish kind of social media.

class User(db.Model):

#For moderation purposes

#so someone can't just enter a number and see everyone on there
#like stack overflow does
public_id = db.Column(db.String(30), unique=True, nullable=False)

#real id
id = db.Column(db.Integer(), primary_key=True, nullable=False)

#this to emulate it
handle = db.Column(db.String(30), unique=True, nullable=False)
description = db.Column(db.String(200))

#to mimic most of twitter's information they share
date_joined = db.Column(db.DateTime(), default=datetime.utcnow(), nullable=False)
birth_date = db.Column(db.DateTime(), nullable=False)

#so I know where to their photo is at
profile_img_uri = db.Column(db.String(30), nullable=False)

#I put requested email and normal for change email verification
#purposes
#kind of covering the case of when someone puts in an email into
#oblivion, and changin it on the spot would not be good
email = db.Column(db.String(30), unique=True, nullable=False)
requested_email = db.Column(db.String(30))

#kind of like the password. I am not sure whether to do something
#simialar to supercell's in-game login system or to do the
verif_code = db.Column(db.String(30), nullable=False)

#I just want to play with datetime and this is something that
# is somewhat useful

#how many people following them
follower_count = db.Column(db.Integer(),default=0, nullable=False)

#how many people they are following, I do have a seperate table
#that has. the list of people they are following.
# I noticed that twitter is a little bit slower when pulling up
#  who is followers and following, so I assume that they are most
# likely doing something like that.
following_count = db.Column(db.Integer(),default=0, nullable=False)

is_verified = db.Column(db.Boolean(), default=False, nullable=False)

# moderation purposes
is_suspended = db.Column(db.Boolean(), default=False, nullable=False)


db.String(30) is like a VarChar() and the rest kind of describe itself.

Here is the Post database model

class Post(db.Model):
#the id of the post itself
id = db.Column(db.Integer(), unique=True, primary_key=True, nullable=False)

#if they have an image, then the provid the url
image_uri = db.Column(db.String(30))

#I am keeping a character limit simialar to twitter, so I don't
#really have to provide the case of someone writting an essay on
text = db.Column(db.String(100))
date_posted = db.Column(db.DateTime(), default=datetime.utcnow(), nullable=False)

user_id = db.Column(db.String(30), nullable=False)

#I want to get something like reddit's rating system of upvote
#downvote
likes = db.Column(db.Integer(), default=0, nullable=False)
dislikes = db.Column(db.Integer(), default=0, nullable=False)


Here is the comment database design. It's something simalar.

class Comment(db.Model):
#id of the comment itself
id = db.Column(db.String(30), unique=True, primary_key=True, nullable=False)
image_uri = db.Column(db.String(30))
text = db.Column(db.String(30))
date_posted = db.Column(db.DateTime(), default=datetime.utcnow(), nullable=False)

post_id = db.Column(db.Integer(), nullable=False)
comment_id = db.Column(db.Integer(), unique=True, primary_key=True, nullable=False)

user_id = db.Column(db.String(30), nullable=False)
likes = db.Column(db.Integer(), default=0, nullable=False)
dislikes = db.Column(db.Integer(), default=0, nullable=False)


This is the reply id. I don't have any plans for replies to replies, but I probalby could run a reply like a comment to allow that. I don't think I should complicate things too much, since this is more of a learning project.

class Reply(db.Model):
id = db.Column(db.String(30), unique=True, primary_key=True, nullable=False)
image_uri = db.Column(db.String(30))
text = db.Column(db.String(30))
date_posted = db.Column(db.DateTime(), default=datetime.utcnow(), nullable=False)

comment_id = db.Column(db.Integer(), nullable=False)
post_id = db.Column(db.Integer(), nullable=False)
reply_id = db.Column(db.Integer(), unique=True, primary_key=True, nullable=False)

user_id = db.Column(db.String(30), nullable=False)
likes = db.Column(db.Integer(), default=0, nullable=False)
dislikes = db.Column(db.Integer(), default=0, nullable=False)


Here is the follower table, which is super simple.

class Follower(db.Model):
#who they are following
follwer_id = db.Column(db.Integer(), nullable=False)
#who is following them
user_id = db.Column(db.Integer())


Anyways, I would love some suggestions on the database design. I can provide more details if needed.