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 |
|
---|