| 1 | DROP TABLE IF EXISTS ProfileMarketingRequest;
|
|---|
| 2 | DROP TABLE IF EXISTS PostMarketingRequest;
|
|---|
| 3 | DROP TABLE IF EXISTS Post;
|
|---|
| 4 | DROP TABLE IF EXISTS SocialMediaProfile;
|
|---|
| 5 | DROP TABLE IF EXISTS ServiceProvider;
|
|---|
| 6 | DROP TABLE IF EXISTS App_User;
|
|---|
| 7 | DROP TYPE IF EXISTS marketing_status;
|
|---|
| 8 |
|
|---|
| 9 |
|
|---|
| 10 | CREATE TYPE marketing_status AS ENUM ('in progress', 'completed', 'pending');
|
|---|
| 11 |
|
|---|
| 12 | CREATE TABLE App_User (
|
|---|
| 13 | UserID SERIAL PRIMARY KEY,
|
|---|
| 14 | Username VARCHAR(255) NOT NULL,
|
|---|
| 15 | Email VARCHAR(255) NOT NULL,
|
|---|
| 16 | Password VARCHAR(255) NOT NULL,
|
|---|
| 17 | Contact_Details VARCHAR(255) NOT NULL
|
|---|
| 18 | );
|
|---|
| 19 |
|
|---|
| 20 | -- Create Social Media Profile table
|
|---|
| 21 | CREATE TABLE SocialMediaProfile (
|
|---|
| 22 | ProfileID SERIAL PRIMARY KEY,
|
|---|
| 23 | Platform VARCHAR(255) NOT NULL,
|
|---|
| 24 | User_Name VARCHAR(255) NOT NULL,
|
|---|
| 25 | Account_Type VARCHAR(50) NOT NULL,
|
|---|
| 26 | Followers_Count INT NOT NULL,
|
|---|
| 27 | UserID INT NOT NULL,
|
|---|
| 28 | FOREIGN KEY (UserID) REFERENCES App_User(UserID) ON DELETE CASCADE
|
|---|
| 29 | );
|
|---|
| 30 |
|
|---|
| 31 | -- Create Post table
|
|---|
| 32 | CREATE TABLE Post (
|
|---|
| 33 | PostID SERIAL PRIMARY KEY,
|
|---|
| 34 | Content TEXT,
|
|---|
| 35 | Post_Date TIMESTAMP,
|
|---|
| 36 | URL VARCHAR(255) NOT NULL,
|
|---|
| 37 | Likes_Count INT NOT NULL,
|
|---|
| 38 | Comments_Count INT NOT NULL,
|
|---|
| 39 | ProfileID INT NOT NULL,
|
|---|
| 40 | FOREIGN KEY (ProfileID) REFERENCES SocialMediaProfile(ProfileID) ON DELETE CASCADE
|
|---|
| 41 | );
|
|---|
| 42 |
|
|---|
| 43 | -- Create Service Provider table
|
|---|
| 44 | CREATE TABLE ServiceProvider (
|
|---|
| 45 | ProviderID SERIAL PRIMARY KEY,
|
|---|
| 46 | Name VARCHAR(255) NOT NULL,
|
|---|
| 47 | Availability_Status BOOLEAN NOT NULL,
|
|---|
| 48 | Pricing FLOAT NOT NULL,
|
|---|
| 49 | Services_Offered TEXT NOT NULL,
|
|---|
| 50 | Contact_Email VARCHAR(255) NOT NULL
|
|---|
| 51 | );
|
|---|
| 52 |
|
|---|
| 53 | -- Create Profile Marketing Request table
|
|---|
| 54 | CREATE TABLE ProfileMarketingRequest (
|
|---|
| 55 | RequestID SERIAL PRIMARY KEY,
|
|---|
| 56 | Target_Followers INT NOT NULL,
|
|---|
| 57 | Timeline TIMESTAMP NOT NULL,
|
|---|
| 58 | ProfileID INT NOT NULL UNIQUE,
|
|---|
| 59 | Date_Created TIMESTAMP NOT NULL,
|
|---|
| 60 | Status marketing_status NOT NULL,
|
|---|
| 61 | UserID INT NOT NULL,
|
|---|
| 62 | ProviderID INT,
|
|---|
| 63 | FOREIGN KEY (ProfileID) REFERENCES SocialMediaProfile(ProfileID) ON DELETE CASCADE,
|
|---|
| 64 | FOREIGN KEY (UserID) REFERENCES App_User(UserID) ON DELETE CASCADE,
|
|---|
| 65 | FOREIGN KEY (ProviderID) REFERENCES ServiceProvider(ProviderID) ON DELETE SET NULL
|
|---|
| 66 | );
|
|---|
| 67 |
|
|---|
| 68 | -- Create Post Marketing Request table
|
|---|
| 69 | CREATE TABLE PostMarketingRequest (
|
|---|
| 70 | RequestID SERIAL PRIMARY KEY,
|
|---|
| 71 | Target_Likes INT NOT NULL,
|
|---|
| 72 | Target_Comments INT NOT NULL,
|
|---|
| 73 | Timeline TIMESTAMP NOT NULL,
|
|---|
| 74 | PostID INT NOT NULL UNIQUE,
|
|---|
| 75 | Date_Created TIMESTAMP NOT NULL,
|
|---|
| 76 | Status marketing_status NOT NULL,
|
|---|
| 77 | UserID INT NOT NULL,
|
|---|
| 78 | ProviderID INT,
|
|---|
| 79 | FOREIGN KEY (PostID) REFERENCES Post(PostID) ON DELETE CASCADE,
|
|---|
| 80 | FOREIGN KEY (UserID) REFERENCES App_User(UserID) ON DELETE CASCADE,
|
|---|
| 81 | FOREIGN KEY (ProviderID) REFERENCES ServiceProvider(ProviderID) ON DELETE SET NULL
|
|---|
| 82 | );
|
|---|
| 83 |
|
|---|
| 84 |
|
|---|
