0
\$\begingroup\$

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
    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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.