blog_posts_table.sql

blog_posts_table.sql

create table blog_posts
(
    post_id      bigint(32) UNSIGNED                              not null unique auto_increment,
    post_title   varchar(255)                                     not null default '',
    post_content varchar(255)                                     not null default '',
    post_tags    tinytext                                         not null default 'Blog Post',
    post_slug    varchar(255)                                     not null unique,
    post_status  enum ('Draft','Published','Scheduled','Removed') not null default 'Draft',
    category_id  bigint(32) UNSIGNED                              not null default 0,
    created_at   timestamp                                                 default NOW() not null,
    published_at timestamp                                                 default NOW() not null,
    updated_at   timestamp                                                 default ('0000-00-00 00:00:00') not null,
    created_by   bigint(32) UNSIGNED                              not null default 0,
    published_by bigint(32) UNSIGNED                              not null default 0,
    updated_by   bigint(32) UNSIGNED                              not null default 0,
    is_published enum ('Yes','No')                                not null default 'No',
    is_deleted   enum ('Yes','No')                                not null default 'No',
    is_scheduled enum ('Yes','No')                                not null default 'No',
    is_featured  enum ('Yes','No')                                not null default 'No',
    primary key (post_id),
    index (post_id),
    index (published_by, post_title, published_by),
    index (post_slug),
    index (post_status),
    index (is_published),
    index (is_deleted),
    index (is_scheduled),
    index (is_featured),
    index (category_id),
    index (published_by),
    index (updated_by) primary key (post_id),
    constraint fk_blog_posts_category_id foreign key (category_id) references blog_categories (category_id),
    constraint fk_blog_posts_created_by foreign key (created_by) references blog_users (user_id),
    constraint fk_blog_posts_published_by foreign key (published_by) references blog_users (user_id),
    constraint fk_blog_posts_updated_by foreign key (updated_by) references blog_users (user_id),
    constraint fk_blog_posts_post_status foreign key (post_status) references blog_post_statuses (post_status)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_unicode_ci
  AUTO_INCREMENT = 3 COMMENT 'Blog Posts';