Appointments sql schema

The SQL script provided creates four tables: AppointmentCategories, Tags, Appointments, and AppointmentFiles. These tables are designed to manage appointments and their related information. Below is a brief explanation of each table's purpose and the columns it contains:

1. AppointmentCategories Table: This table stores different categories for appointments.

   • Id: Primary key auto-generated for each record.
   • Title: The title or name of the appointment category (nvarchar(max) is a data type for storing variable-length Unicode strings).
   • Order: An integer column used for specifying the order of the categories.
   • CreatedAt: A datetime column that stores the creation date of the category. It has a default value of the current date and time when not provided explicitly.
   • UpdatedAt: A datetime column that stores the last update date of the category. It has a default value of the current date and time when not provided explicitly.
   • Published: A bit column that indicates whether the category is published (1) or not (0). It has a default value of 0 (not published) when not provided explicitly.
   • Deleted: A bit column that indicates whether the category is deleted (1) or not (0). It has a default value of 0 (not deleted) when not provided explicitly.

2. Tags Table: This table stores tags that can be associated with appointments.

   • Id: Primary key auto-generated for each tag.
   • Order: An integer column used for specifying the order of the tags.
   • CreatedAt: A datetime column that stores the creation date of the tag. It has a default value of the current date and time when not provided explicitly.
   • UpdatedAt: A datetime column that stores the last update date of the tag. It has a default value of the current date and time when not provided explicitly.
   • Published: A bit column that indicates whether the tag is published (1) or not (0). It has a default value of 0 (not published) when not provided explicitly.
   • Deleted: A bit column that indicates whether the tag is deleted (1) or not (0). It has a default value of 0 (not deleted) when not provided explicitly.
   • Title: The title or name of the tag (nvarchar(max)).

3. Appointments Table: This table stores information about individual appointments.

   • Id: Primary key auto-generated for each appointment.
   • AppointmentCategoryId: A foreign key referencing the Id column of the AppointmentCategories table, linking each appointment to a specific category.
   • Title: The title or name of the appointment (nvarchar(max)).
   • Description: A description of the appointment (nvarchar(max)).
   • StartDate: A datetime column that stores the start date and time of the appointment. It has a default value of the current date and time when not provided explicitly.
   • Order: An integer column used for specifying the order of the appointments.
   • CreatedAt: A datetime column that stores the creation date of the appointment. It has a default value of the current date and time when not provided explicitly.
   • UpdatedAt: A datetime column that stores the last update date of the appointment. It has a default value of the current date and time when not provided explicitly.
   • Published: A bit column that indicates whether the appointment is published (1) or not (0). It has a default value of 0 (not published) when not provided explicitly.
   • Deleted: A bit column that indicates whether the appointment is deleted (1) or not (0). It has a default value of 0 (not deleted) when not provided explicitly.

4. AppointmentTags Table: This table establishes a many-to-many relationship between appointments and tags.

   • AppointmentId: A foreign key referencing the Id column of the Appointments table, linking each record to a specific appointment.
   • TagId: A foreign key referencing the Id column of the Tags table, linking each record to a specific tag.
   • Order: An integer column used for specifying the order of the tags associated with an appointment.
   • CreatedAt: A datetime column that stores the creation date of the relationship record. It has a default value of the current date and time when not provided explicitly.
   • UpdatedAt: A datetime column that stores the last update date of the relationship record. It has a default value of the current date and time when not provided explicitly.

5. AppointmentFiles Table: This table stores files related to each appointment.

   • Id: Primary key auto-generated for each file record.
   • AppointmentId: A foreign key referencing the Id column of the Appointments table, linking each file to a specific appointment.
   • FilePath: The path or location of the file (nvarchar(max)).
   • Order: An integer column used for specifying the order of the files associated with an appointment.
   • CreatedAt: A datetime column that stores the creation date of the file record. It has a default value of the current date and time when not provided explicitly.
   • UpdatedAt: A datetime column that stores the last update date of the file record. It has a default value of the current date and time when not provided explicitly.
   • Published: A bit column that indicates whether the file is published (1) or not (0). It has a default value of 0 (not published) when not provided explicitly.
   • Deleted: A bit column that indicates whether the file is deleted (1) or not (0). It has a default value of 0 (not deleted) when not provided explicitly.

Please note that the AppointmentTags table is used to create a many-to-many relationship between appointments and tags, as one appointment can have multiple tags, and one tag can be associated with multiple appointments. The primary key of this table is a combination of both AppointmentId and TagId, ensuring uniqueness of tag assignments per appointment.

              
                /* Appointment  schema */

CREATE TABLE AppointmentCategories (
    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_AppointmentCategories_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 Appointments (
    Id int IDENTITY(1,1) not null,
    AppointmentCategoryId int not null,
    Title nvarchar(max) NOT NULL,
    Description nvarchar(max) NOT NULL,
    StartDate 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_Appointments_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Appointments_AppointmentCategoryId FOREIGN KEY (AppointmentCategoryId) REFERENCES  AppointmentCategories(Id)
);

CREATE TABLE AppointmentTags (
    AppointmentId int,
    TagId int,
    [Order] int not null  default 0,
    CreatedAt datetime not null DEFAULT GETDATE(),
    UpdatedAt datetime not null DEFAULT GETDATE(),
    CONSTRAINT PK_AppointmentTags PRIMARY KEY NONCLUSTERED ([ApppointmentId],[TagId]),
    CONSTRAINT FK_AppointmentTags_AppointmentId FOREIGN KEY (AppointmentId) REFERENCES  Appointments(Id),
    CONSTRAINT FK_AppointmentTags_TagId FOREIGN KEY (TagId) REFERENCES Tags(Id),
);


CREATE TABLE AppointmentFiles (
    Id int IDENTITY(1,1) not null,
    AppointmentId 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_AppointmentFiles_Id PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_AppointmentFiles_AppointmentId FOREIGN KEY (AppointmentId) REFERENCES  Appointments(Id)
);

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