blog sql schema

This SQL script defines a database schema for a blogging application. It creates several tables to store information related to blog posts, categories, tags, and media associated with the posts. Let's go through each table and its columns:

1. PostCategories:

   • Columns:
     • Id (int, Primary Key): Unique identifier for each post category (auto-incremented).
     • Title (nvarchar(max), NOT NULL): The title of the post category.
     • Order (int, NOT NULL, default 0): An integer value representing the order of the post category.
     • CreatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the category was created.
     • UpdatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the category was last updated.
     • Published (bit, NOT NULL, default 0): A flag indicating whether the category is published (1) or not (0).
     • Deleted (bit, NOT NULL, default 0): A flag indicating whether the category is deleted (1) or not (0).

2. Posts:

   • Columns:
     • Id (int, Primary Key): Unique identifier for each blog post (auto-incremented).
     • PostCategoryId (int, NOT NULL): Foreign key referencing the PostCategories table, indicating the category to which the post belongs.
     • Title (nvarchar(max), NOT NULL): The title of the blog post.
     • Description (nvarchar(max), NOT NULL): A description or content of the blog post.
     • Order (int, NOT NULL, default 0): An integer value representing the order of the blog post.
     • CreatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the post was created.
     • UpdatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the post was last updated.
     • Published (bit, NOT NULL, default 0): A flag indicating whether the post is published (1) or not (0).
     • Deleted (bit, NOT NULL, default 0): A flag indicating whether the post is deleted (1) or not (0).
     • The table also contains foreign key constraints, FK_Posts_PostCategoryId, referencing the PostCategories table.

3. Tags:

   • Columns:
     • Id (int, Primary Key): Unique identifier for each tag (auto-incremented).
     • Order (int, NOT NULL, default 0): An integer value representing the order of the tag.
     • CreatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the tag was created.
     • UpdatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the tag was last updated.
     • Published (bit, NOT NULL, default 0): A flag indicating whether the tag is published (1) or not (0).
     • Deleted (bit, NOT NULL, default 0): A flag indicating whether the tag is deleted (1) or not (0).
     • Title (nvarchar(max), NOT NULL): The title of the tag.

4. PostTags:

   • Columns:
     • PostId (int): Foreign key referencing the Posts table, indicating the blog post to which the tag is associated.
     • TagId (int): Foreign key referencing the Tags table, indicating the tag associated with the blog post.
     • Order (int, NOT NULL, default 0): An integer value representing the order of the tag in relation to the post.
     • CreatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the association between post and tag was created.
     • UpdatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the association between post and tag was last updated.
     • The table uses a composite primary key (PostId, TagId) to ensure each combination of PostId and TagId is unique.
     • The table also contains foreign key constraints, FK_PostTags_PostId and FK_PostTags_TagId, referencing the Posts and Tags tables, respectively.

5. PostMedia:

   • Columns:
     • Id (int, Primary Key): Unique identifier for each media item (auto-incremented).
     • PostId (int, NOT NULL): Foreign key referencing the Posts table, indicating the blog post to which the media item is associated.
     • FilePath (nvarchar(max), NOT NULL): The file path of the media item.
     • Order (int, NOT NULL, default 0): An integer value representing the order of the media item.
     • CreatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the media item was created.
     • UpdatedAt (datetime, NOT NULL, DEFAULT GETDATE()): Date and time when the media item was last updated.
     • Published (bit, NOT NULL, default 0): A flag indicating whether the media item is published (1) or not (0).
     • Deleted (bit, NOT NULL, default 0): A flag indicating whether the media item is deleted (1) or not (0).
     • The table also contains a foreign key constraint, FK_PostMedia_PostId, referencing the Posts table.

The schema provides a foundation for building a blog system that allows categorizing posts, adding tags to posts, and associating media with posts. Keep in mind that this is just the database schema, and the functionality of the blogging application would be implemented separately in the application code, interacting with this database.

              
                /*------------------------------------------------------------------------------------------------ */

/* Blog schemna */

CREATE TABLE PostCategories (
    Id int IDENTITY(1,1) not null,
    Title nvarchar(max) NOT NULL,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    CONSTRAINT PK_PostCategories_Id PRIMARY KEY CLUSTERED (Id)
);


CREATE TABLE Posts (
    Id int IDENTITY(1,1) not null,
    PostCategoryId int not null,
    Title nvarchar(max) NOT NULL,
    Description nvarchar(max) NOT NULL,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    CONSTRAINT PK_Posts_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Posts_PostCategoryId FOREIGN KEY (PostCategoryId) REFERENCES  PostCategories(Id)
);


CREATE TABLE Tags (
    Id int IDENTITY(1,1) not null,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    Title nvarchar(max) NOT NULL,
    CONSTRAINT PK_Tags_Id PRIMARY KEY CLUSTERED (Id)
);

CREATE TABLE PostTags (
    PostId int,
    TagId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_PostTags PRIMARY KEY NONCLUSTERED ([PostId],[TagId]),
    CONSTRAINT FK_PostTags_PostId FOREIGN KEY (PostId) REFERENCES  Posts(Id),
    CONSTRAINT FK_PostTags_TagId FOREIGN KEY (TagId) REFERENCES  Tags(Id),
);


CREATE TABLE PostMedia (
    Id int IDENTITY(1,1) not null,
    PostId int not null,
    FilePath nvarchar(max) NOT NULL,
    [Order] int not null default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    Published bit not null default 0,
    Deleted bit not null default 0,
    CONSTRAINT PK_PostMedia_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_PostMedia_PostId FOREIGN KEY (PostId) REFERENCES  Posts(Id)
);

/* -----------------------------------------------------------------------------------  */