finitud/00-tech-blog

Learning SQL

I have been checking out this book to teach myself some SQL lately. I'm finding SQL fun so far, that said I haven't really started doing a lot with it...

My toy project for this is an online forum. Initially I need three tables:

posts
users
topics

with the obvious meanings ;-)

A user is pretty simple to implement:

CREATE TABLE users (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT PRIMARY KEY (user_id)
);

We don't want the username to be null, since that's how we are going to identify our users. One could possibly use the name field as primary key, since we don't want duplicate usernames, but for now I'll leave it like this.

Next, we will create a table to store the topic names (the different sections of the forum):

CREATE TABLE topics (
  topic_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  topic_name TEXT,
  CONSTRAINT PRIMARY KEY (topic_id)
);

Actually, we don't want a null topic either (otherwise users of the forum will be pretty confused). Let's fix it:

ALTER TABLE topics MODIFY topic_name TEXT NOT NULL;

Now everything looks ok:

mysql> desc topics;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| topic_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| topic_name | text             | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Finally, let's create the posts table. Here's where I had to think a bit on how to design the database structure. I went for something like this:

CREATE TABLE posts (
  post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(140),
  body TEXT,
  previous_post INT UNSIGNED,
  topic_id INT UNSIGNED,
  last_modified TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  created TIMESTAMP,
  CONSTRAINT PRIMARY KEY (post_id),
  CONSTRAINT FOREIGN KEY (user_id) REFERENCES users (user_id),
  CONSTRAINT FOREIGN KEY (previous_post) REFERENCES posts (post_id)
);

The idea behind the previous_post field is to keep track of the replies in a thread.

Learning SQL (Part 2): Populating and querying the database

So, after the basic database structure from last post it's time to put some stuff on the database. First, create a few users:

INSERT INTO users (user_id, name) VALUES (null, 'admin');
INSERT INTO users (user_id, name) VALUES (null, 'ruben');
INSERT INTO users (user_id, name) VALUES (null, 'demouser');

And a couple of topics:

INSERT INTO topics (topic_id, topic_name) VALUES (null, 'Welcome board');
INSERT INTO topics (topic_id, topic_name) VALUES (null, 'Random board');

Say we know want to insert a few posts by various users:

INSERT INTO posts (post_id, user_id, title, body, topic_id)
VALUES (null, 2, 'Hello World', 'Hello there users!', 1),
       (null, 2, 'Random post', 'Random text', 2),
       (null, 3, 'Hi there', 'Hello I'm demouser', 1);

Finally, we can query a list of posts by user:

SELECT u.name, p.title FROM users u JOIN posts p ON u.user_id = p.user_id;

Hopefully we get something like:

+----------+-------------+
| name     | title       |
+----------+-------------+
| ruben    | Hello World |
| ruben    | Random post |
| demouser | Hi there    |
+----------+-------------+
3 rows in set (0.00 sec)

(reconstructed by hand since my db has lots of crap). What the query does is, essentially, take the cartesian product of the two tables users and posts, and then take those rows in which the user_id field match.

That's it for today!