Database schema for an upcoming comment hosting system
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,...
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/');
14
votes