si-blog

Blog database

Posted May 29, 2004 in Databases.

As far as a future si-blog is concerned, here is my current thinking:

create table cats (
    id int(6) not null primary key auto_increment,
    name varchar(50) not null,
    short_desc varchar(255)
    );

create table entries (
    id int(6) not null primary key auto_increment,
    timestamp timestamp not null,
    title varchar(100) not null,
    abstract varchar(255) not null,
    comments enum('open','closed') not null default 'open',
    body text not null
    );

create table entries_cats (
    entry_id int(6) not null,
    cat_id int(6) not null
    );

create table links (
    id int(6) not null primary key auto_increment,
    link_text varchar(255) not null,
    url varchar(255) not null,
    title varchar(255) not null,
    rel varchar(255) not null
    );

create table links_cats (
    link_id int(6) not null,
    cat_id int(6) not null
    );

create table users (
    id int(6) not null primary key auto_increment,
    user_name varchar(50),
    user_pass varchar(20),
    user_email varchar(100),
    user_url varchar(100)
    );

create table comments (
    id int(6) not null primary key auto_increment,
    entry_id int(6) not null,
    user_id int(6) not null,
    timestamp timestamp not null,
    user_ip varchar(15) not null,
    body text not null
    );

create table refer (
    id int(6) not null primary key auto_increment,
    timestamp timestamp not null,
    referer varchar(255) not null,
    agent varchar(255) not null,
    ip varchar(15) not null,
    target varchar(100) not null
    );

create table banned (
    id int(6) not null primary key auto_increment,
    ip varchar(15),
    url varchar(255),
    email varchar(255)
    );

create table images (
    id int(6) not null primary key auto_increment,
    timestamp timestamp not null,
    title varchar(100) not null,
    taken date not null default '0000-00-00',
    alt_text varchar(100) not null,
    path varchar(100) not null,
    orientation  enum('landscape','portrait') not null default 'landscape',
    body text not null
    );

create table images_cats (
    image_id int(6) not null,
    cat_id int(6) not null
    );

Comments

  1. Gravatar

    Looks good. I thoroughly recommend adding a 'slug' field to both the entries and the categories though as that will let you have nicer URLs that don't include a numerical ID.

    Posted by Simon Willison on May 29, 2004.

  2. Gravatar

    I am not familiar with that term. Are you suggesting that mod_rewrite (of which I know next to nothing) can be used to create nice URLs on the fly as each entry is created, from a field in the database?

    Posted by Simon Jessey on May 29, 2004.

  3. Gravatar

    No he means the last part of the URI. On my latest entry, iirc, that is 'mozilla-css3-selectors', where the title is something different.

    In .htaccess I use a rewrite rule which makes from that URI a GET request, like on your weblog and the PHP script will look for $_GET['slug'] (and some other things, like month, year) in the database.

    Posted by Anne on May 30, 2004.

  4. Gravatar

    I still don't quite understand this process. Are you saying that a combination of yyyy/mm/dd and the "slug" field can be passed to mod_rewrite, which converts it into a querystring?

    Posted by Simon Jessey on May 30, 2004.

  5. Gravatar

    Yes, although that will never be showed to the user of your site. The querystring will exist server-side, though, which is nice. (That way you can use it in PHP easily.)

    Posted by Anne on May 30, 2004.