Database schema for an upcoming comment hosting system

Question

I'm working on a small and simple comment hosting platform in PHP (SQLITE database) to host comments for my static blog https://prahladyeri.github.io/. No login, sign-ups or third party OAuth, just plain old Wordpress.org style commenting system.

I have come up with the following DB schema so far to store the comments (comments table) and enable the administrator's dashboard authentication (users table). Can this be improved further?

-- init.sql
--
drop table if exists comments;
drop table if exists posts;
drop table if exists users;

create table comments (
    id integer primary key,
    reply_to_id integer references comments (id),
    post_id integer references posts (id),
    message text,
    name text,
    email text,
    website text,
    ip text, -- $_SERVER['REMOTE_ADDR']
    notify text default 'n',
    status text default 'Approved', -- Approved/Spam
    created_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime')),
    modified_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime'))
);

create table posts (
    id integer primary key,
    user_id integer references users (id),
    uri text -- /blog/2024/05/some-slug.html
);

create table users (
    id integer primary key,
    username text not null,
    password text not null,
    email text, -- can be null
    name text not null,
    website text, -- comments will be posted to this site
    role text not null, -- Admin/Staff
    created_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime')),
    modified_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime')),
    unique (username),
    unique (email)
);

-- create default data
-- create a default admin user who handles to dashboard
insert into users(username,password, name, role, website)
values("admin", "admin108", 'Admin', 'Admin', 'https://example.com/');

Note that the users table here is for the admin users like myself who will administer the hosting system, not the commenters on the blog.

Answer 1

deps, topo sort order

The FK DAG induces a topological order across various DB objects, such as tables.

Consider writing these in a different order:

drop ... ;
drop ... ;
drop ... ;

create table comments (
    ...
    post_id integer   references posts(id),
    ...

create table posts ( ...

It turns out that sqlite lets you get away with this forward reference, as it apparently does very late binding on FK constraints, deferring it until INSERT time.

When I write SQL-92 statements, I start out intending that they should work fine with any random database vendor, and then narrow the focus as it turns out we need some vendor-specific extension. Here, it's just weird to declare a constraint on a table which does not yet exist. Similarly, when posts forward references users. Consider reordering, to ease making this code work with MariaDB or postgres. (Well, ok, I usually start with sqlalchemy and let it sweat the per-vendor syntax details.)

spurious comment

You can just elide this one, as it's already clear.

    email text, -- can be null

I thank you for some other helpful "data format" comments, such as on uri.

    ip text, -- $_SERVER['REMOTE_ADDR']

Maybe rename to client_ip? Then it would be sooo obvious, we wouldn't even need that nice comment.

boolean

    notify text default 'n',

This worries me. In another DB backend I might expect a {'n', 'y'} enum. Wouldn't you like to make this column boolean? I think sqlite spells it CHECK (notify IN (0, 1)). Similarly, consider turning status into an approved boolean.

Also, NOT NULL please.

In general, if you can get away with NOT NULL, do so. You can always relax it later if need be. Going the other way round tends to be harder. Computers are very good at three-valued logic. Humans? Less so. Try to make things easier for them if at all possible.

OTOH, since there's no standalone comments, it looks like this one really needs NOT NULL:

    post_id integer   references posts(id),

Actually, a bunch of those comments attributes look like it's not sensible to store a NULL in them. Is it really a comment if there is no message? And given their defaults, insisting that timestamps be NOT NULL is just too easy not to do it.

UTC

    created_at datetime ... 'localtime')),

Well, you can do what you want. I always recommend that folks persist UTC seconds since epoch (1970) in the database, and then worry about local zone offsets on the way in and the way out.

My understanding is this comment system will be connected to the internet. It turns out there's internet clients located in more than one timezone.

mandatory fields

create table posts (
    id       integer  primary key,
    user_id  integer  references users (id),
    uri      text ...

A PK is always enforced to be NOT NULL, so we're good there. Beef up the remaining two columns, please.

crypto hash

create table users (
    id ...
    username ...
    password text   not null,

I hope you're not planning to store cleartext passwords in there. Users recycle passwords across sites. Compromise of your server could lead to compromise of accounts on distant servers.

You should be using argon2id, and you should be calling that column something like hash.

enum

    role text not null, -- Admin/Staff

Consider making that a mandatory FK relationship to a roles table, or at least put a CHECK constraint on it. This avoids e.g. having both "Staff" and "staff" values in there.

I do like the UNIQUE constraints on username & email, BTW.

spam

No login, sign-ups or third party OAuth

Well, it's your funeral. Hope it works out for the community!

When you're small, spammers won't notice / won't care. If you grow larger, you will attract their attention. At which point you may want to renege on a promise you made to early users who made the site successful, and impose mandatory logins so that abusive accounts can be disabled or throttled. Also, reputation systems can improve the UX but they need an "identity" foundation to build upon.

Answer 2

In table users, there is a role field which I suspect may not really be used at present. But it could make sense to add one more field field to store an E-mail address. So you can get a password reset link in case you are locked out of the system (forgot the password for example). This could be a moot point if you have physical or SSH access to the server. And if you are paranoid, you could add (2FA) two-factor authentication.

Also, you might want to receive notifications by E-mail for all kinds of events, like a new comment being posted to your site and awaiting moderation.

Table posts has only 3 fields: ID, user_id and uri. I find it odd that it doesn't have a field for some text contents. After all, that table is linked to a comments table that does have a message field.

So far you have shown us the database, but the critical part is the PHP code. This is where security vulnerabilities may lie.

---

<< Debbie Downer has entered the room >>

To be honest, I would not be writing such an app in 2024, because there is open source software that does the job the pretty well. Your app is a stripped-down version of Wordpress, but it doesn't implement anything new or unique. It uses PHP with a database. I don't think it is a "static" blog technically.

A case could be made for using your app instead of WP if you can prove that it is more secure. At this time, no such assertion can be made. And I would definitely discard any app that stores password in plain text in 2024.

Actually, there is one difference. You are using SQLite instead of a larger DBMS like Mysql or Postgress. SQLite is well, light but it is not designed for concurrency so it could potentially be a bottleneck if your website is under heavy load. On the other hand SQLite is widespread for single-tenant embedded usage etc.

I understand you may find WP bloated for your purpose, but there are mature alternatives available, that come with many plugins and themes, which allow customization. One plugin that could come handy is the antispam when the bots start flooding your site.

Plus, user management and roles are already built-in, so you don´t have to implement features like password hashing or 2FA for yourself. Doing all this from scratch takes time, even if you use a framework and there is always a risk of bugs or implementation errors.

I am not trying to discourage you, it's just that there has to be a compelling reason for reinventing the wheel (unless this is a pure learning exercise). And we developers all have to make decisions about the best way to allocate our limited time. What I see is that you have made something "different", but the interesting question would be what problem were you trying to solve.

This is not just some training lab exercise you are doing, this is an app that you are going to put on the Internet, which is a hostile environment. So it needs to be designed as such.

I am a Wordpress user too, but I have made a choice to use Docker containers. There are Docker images available so you can be up and running in minutes. At some webhosts, WP can be installed in one click. You get the point. A complete blogging solution can be deployed in very little time.

In conclusion, I would encourage you to post an update later with some PHP code, so that we have more context and can maybe assess the amount of work required to have something "good enough".

PS: it is not forbidden to look at how other pieces of software to see how they are done and take inspiration from them. I have learned a lot just by looking at other people's code and I still do :)