Help with a microblog (twitter clone) database structure?

Question

I'm working on an exercise that wants me to create a small twitter clone, with users, tweets and following system. Well, i came up with the following database structure:

CREATE TABLE tweets (
  tweet_id INT NOT NULL AUTO_INCREMENT,
  tweet VARCHAR(140) NOT NULL,
  PRIMARY KEY (tweet_id)
) ENGINE=INNODB;


CREATE TABLE users (
  user_id INT NOT NULL AUTO_INCREMENT,
  user VARCHAR(255) NOT NULL,
  password VARCHAR(40) NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE user_tweets (
  id INT NOT NULL AUTO_INCREMENT,
  id_user INT NOT NULL,
  id_tweet INT NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (id_tweet)
    REFERENCES tweets(tweeth_id)
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  FOREIGN KEY (id_user)
    REFERENCES users(user_id)) ENGINE=INNODB;

CREATE TABLE followers (
  id_user INT NOT NULL REFERENCES users (user_id),
  id_following INT NOT NULL REFERENCES users (user_id),
  PRIMARY KEY (id_user, id_following)
) ENGINE=INNODB;

Is it valid? Am i missing something? Also:

How do i select the tweets from a user?
How do i select the followers from a user?
How do i select the people a user is following?

I'm getting a little lost with the foreign key concept. :(

there is an open source twitter-like microbloging system, I think it was http://identi.ca — Maysam, Aug 22 '11 at 16:21
i'm not looking for something ready to use, i'm trying to make my own to improve my skills — OralB, Aug 22 '11 at 16:49

score 8 · Accepted Answer · edited May 23 '17 at 11:33

8

Building on the answer by @Karel, I'd use slightly different tables:

CREATE TABLE tweets (
  tweet_id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL
  tweet VARCHAR(140) NOT NULL,
  PRIMARY KEY (tweet_id),
  FOREIGN KEY user_id(user_id) REFERENCES users(user_id) 
  ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;


CREATE TABLE users (
  user_id INT NOT NULL AUTO_INCREMENT,
  user VARCHAR(255) NOT NULL,
  /*password VARCHAR(40) NOT NULL,*/<<--- NEVER STORE A PASSWORD IN THE CLEAR!
  passhash VARCHAR(40) NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=INNODB;


CREATE TABLE followers (
  id_user INT NOT NULL REFERENCES users (user_id),
  id_following INT NOT NULL REFERENCES users (user_id),
  PRIMARY KEY (id_user, id_following)
) ENGINE=INNODB;

How do i select the tweets from a user?

SELECT * FROM tweets WHERE user_id = 458

How do i select the followers from a user?

SELECT * FROM users 
INNER JOIN followers ON (users.users_id = followers.id_user) 
WHERE followers.id_following = 458

How do i select the people a user is following?

SELECT * FROM users
INNER JOIN followers ON (followers.id_following = users.user_id)
WHERE followers.id_user = 458

Use a SHA2 hash to compare the password.
And don't forget to add a salt to the hashing to prevent rainbow attacks.

SELECT user_id 
FROM users 
WHERE users.user = 'OralB' 
      AND users.passhash = SHA2(CONCAT(users.user,'secretToothbrush'),512)

SHA1 is no longer secure, so I'd advice using SHA2 with a 512bit hash length.

Links
MySQL tutorial: http://www.tizag.com/mysqlTutorial/
Foreign keys: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
SHA2: http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_sha2
Concat: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
Why salt: What is "salt" when relating to MYSQL sha1?

edited May 23 '17 at 11:33

Community

1
1

answered Aug 22 '11 at 16:40

Johan

74,508
24
191
319

I'm getting a syntax error on your first create table, near the foreign key statement. – OralB Aug 22 '11 at 16:59
That's because I typed it from memory, please lookup the correct syntax in the following link: http://dev.mysql.com/doc/refman/5.1/en/create-table.html – Johan Aug 22 '11 at 17:04
Well, the erro 150 i couldn't fix. Any ideas why this error happens? It just says Cant create table (errno: 150); – OralB Aug 22 '11 at 17:08
#1005 - Can't create table 'microblog.tweets' (errno: 150) – OralB Aug 22 '11 at 17:21
Anyone has any ideas? Just take the query and run it, it wont work. – OralB Aug 22 '11 at 17:49
Error #150 is a foreign key problem, this is because the table to foreign key references to does not yet exist. Drop the foreign key or create the tables in reverse order. – Johan Aug 22 '11 at 18:03
what would be query to get all your following user tweets ? – Sheitan Jun 20 '12 at 10:36
The salt needs to be random for each user. (see https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet for details). – jwilkins Jan 11 '13 at 07:20
1

How would you account for "re-tweets" with this? – Omnipresent Dec 12 '13 at 02:32
You could add a table 'retweets' linking users and tweets from other users. by their ID's. – Karel-Jan Misseghers May 19 '17 at 13:29

score 4 · Answer 2 · edited Aug 22 '11 at 16:18

4

You don't need a table 'user_tweet' because a tweet can only belong to 1 user, so it would be more logical to have a 'user_id' in you tweet-table

a crosstable like user_tweet is only useful when there is a many-to-many relationship (for example a teacher teaches multiple classgroups, classgroups get classes from multiple teachers)

if you do this you can select the tweets from a user by this sql-statement:

SELECT Tweet
FROM Tweets t (your table name)
WHERE t.User_ID == UserID_Whose_Tweets_you_want

you should now be able to get the followers with a similar query ;D

Hope you can do anything with this!

edited Aug 22 '11 at 16:18

Johan

74,508
24
191
319

answered Aug 22 '11 at 16:15

Karel-Jan Misseghers

797
8
31

But a user can have many tweets... therefore i need the user_tweet table, right? – OralB Aug 22 '11 at 16:49
2

no, you can have multiple tweets with the same user_id as foreign key, table would look like this: TweetID - UserID - Tweet * 1 - George - Hello world! * 2 - George - my second tweet lololol* 3 - Eric - This tweeting is cool!* 4 - george - @Eric, it sure is haha! :D _____(the user_id's are just names here, might as well be numbers too) – Karel-Jan Misseghers Aug 22 '11 at 20:23

Help with a microblog (twitter clone) database structure?

2 Answers2