events sql schema

The provided SQL script creates a schema for managing events, their categories, tags, and associated media. Let's go through each table's purpose and its columns:

1. EventCategories table:

   • Purpose: This table is used to store different categories to which events can be assigned.
   • Columns:
     • Id: Unique identifier for each category (auto-incremented).
     • Title: The title or name of the category (nvarchar(max) data type).
     • Order: An integer field representing the order of the category (default value: 0).
     • CreatedAt: The date and time when the category was created (default value: current date and time).
     • UpdatedAt: The date and time when the category was last updated (default value: current date and time).
     • Published: A bit field to indicate if the category is published (default value: 0).
     • Deleted: A bit field to indicate if the category is deleted (default value: 0).
     • PK_EventCategories_Id: Primary key constraint for the Id column.

2. Tags table:

   • Purpose: This table is used to store tags that can be associated with events.
   • Columns:
     • Id: Unique identifier for each tag (auto-incremented).
     • Order: An integer field representing the order of the tag (default value: 0).
     • CreatedAt: The date and time when the tag was created (default value: current date and time).
     • UpdatedAt: The date and time when the tag was last updated (default value: current date and time).
     • Published: A bit field to indicate if the tag is published (default value: 0).
     • Deleted: A bit field to indicate if the tag is deleted (default value: 0).
     • Title: The title or name of the tag (nvarchar(max) data type).
     • PK_Tags_Id: Primary key constraint for the Id column.

3. Events table:

   • Purpose: This table is used to store information about events.
   • Columns:
     • Id: Unique identifier for each event (auto-incremented).
     • EventCategoryId: The foreign key referencing the Id column of the EventCategories table, representing the category to which the event belongs.
     • Title: The title or name of the event (nvarchar(max) data type).
     • Description: A description of the event (nvarchar(max) data type).
     • StartDate: The start date and time of the event (default value: current date and time).
     • EndDate: The end date and time of the event (default value: current date and time).
     • Order: An integer field representing the order of the event (default value: 0).
     • CreatedAt: The date and time when the event was created (default value: current date and time).
     • UpdatedAt: The date and time when the event was last updated (default value: current date and time).
     • Published: A bit field to indicate if the event is published (default value: 0).
     • Deleted: A bit field to indicate if the event is deleted (default value: 0).
     • PK_Events_Id: Primary key constraint for the Id column.
     • FK_Events_EventCategoryId: Foreign key constraint referencing the Id column of the EventCategories table.

4. EventTags table:

   • Purpose: This table is used to associate events with multiple tags through a many-to-many relationship.
   • Columns:
     • EventId: The foreign key referencing the Id column of the Events table, representing the event to which the tag is associated.
     • TagId: The foreign key referencing the Id column of the Tags table, representing the tag associated with the event.
     • Order: An integer field representing the order of the tag for the specific event (default value: 0).
     • CreatedAt: The date and time when the association was created (default value: current date and time).
     • UpdatedAt: The date and time when the association was last updated (default value: current date and time).
     • PK_EventTags: Primary key constraint for the combination of EventId and TagId.
     • FK_EventTags_EventId: Foreign key constraint referencing the Id column of the Events table.
     • FK_EventTags_TagId: Foreign key constraint referencing the Id column of the Tags table.

5. EventMedia table:

   • Purpose: This table is used to store media (e.g., images, videos) associated with events.
   • Columns:
     • Id: Unique identifier for each media item (auto-incremented).
     • EventId: The foreign key referencing the Id column of the Events table, representing the event to which the media belongs.
     • FilePath: The file path of the media item (nvarchar(max) data type).
     • Order: An integer field representing the order of the media item (default value: 0).
     • CreatedAt: The date and time when the media item was created (default value: current date and time).
     • UpdatedAt: The date and time when the media item was last updated (default value: current date and time).
     • Published: A bit field to indicate if the media item is published (default value: 0).
     • Deleted: A bit field to indicate if the media item is deleted (default value: 0).
     • PK_EventMedia_Id: Primary key constraint for the Id column.
     • FK_EventMedia_EventId: Foreign key constraint referencing the Id column of the Events table.

Overall, this schema provides a structure for managing events, categorizing them, associating them with tags, and including media content related to each event. The foreign key constraints ensure data integrity and maintain referential integrity between the related tables.

              
                /* Event schema */


CREATE TABLE EventCategories (
    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_EventCategories_Id PRIMARY KEY CLUSTERED (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 Events (
    Id int IDENTITY(1,1) not null,
    EventCategoryId int not null,
    Title nvarchar(max) NOT NULL,
    Description nvarchar(max) NOT NULL,
    StartDate datetime not null DEFAULT GETDATE(),
    EndDate datetime not null DEFAULT GETDATE(),
    [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_Events_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Events_EventCategoryId FOREIGN KEY (EventCategoryId) REFERENCES  EventCategories(Id)
);

CREATE TABLE EventTags (
    EventId int,
    TagId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_EventTags PRIMARY KEY NONCLUSTERED ([EventId],[TagId]),
    CONSTRAINT FK_EventTags_EventId FOREIGN KEY (EventId) REFERENCES  Events(Id),
    CONSTRAINT FK_EventTags_TagId FOREIGN KEY (TagId) REFERENCES  Tags(Id),
);

CREATE TABLE EventMedia (
    Id int IDENTITY(1,1) not null,
    EventId 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_EventMedia_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_EventMedia_EventId FOREIGN KEY (EventId) REFERENCES  Events(Id)
);


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