Ytdyklly

This is a mysql database that is created trhough 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 a mysql.

I am designing a database design 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

    is_admin = db.Column(db.String(30), default=False, nullable=False)



    #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)



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



    #twitter only allows unique twitter handles, so I made it like

    #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 

    #traditional password and email. 

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



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

    # is somewhat useful

    last_login = db.Column(db.DateTime(), nullable=False)



    #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

    #twitter

    text = db.Column(db.String(100))

    date_posted = db.Column(db.DateTime(), default=datetime.utcnow(), nullable=False)



    username = db.Column(db.String(30), 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)



    username = db.Column(db.String(30), 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)



    username = db.Column(db.String(30), 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.