1 | CREATE TABLE AuditlyUser (
|
---|
2 | Id INT PRIMARY KEY,
|
---|
3 | FirstName VARCHAR(50) NOT NULL,
|
---|
4 | LastName VARCHAR(50) NOT NULL,
|
---|
5 | EmailAddress VARCHAR(255) NOT NULL,
|
---|
6 | UserDomainName VARCHAR(255)
|
---|
7 | );
|
---|
8 |
|
---|
9 | CREATE TABLE Product (
|
---|
10 | DomainName VARCHAR(255) PRIMARY KEY
|
---|
11 | );
|
---|
12 |
|
---|
13 | CREATE TABLE ProductIndustry (
|
---|
14 | ProductDomainName VARCHAR(255),
|
---|
15 | Industry VARCHAR(255) NOT NULL,
|
---|
16 | PRIMARY KEY (ProductDomainName),
|
---|
17 | FOREIGN KEY (ProductDomainName) REFERENCES Product(DomainName)
|
---|
18 | );
|
---|
19 |
|
---|
20 | CREATE TABLE Reviewer (
|
---|
21 | Id INT PRIMARY KEY,
|
---|
22 | FirstName VARCHAR(255) NOT NULL,
|
---|
23 | LastName VARCHAR(255) NOT NULL,
|
---|
24 | Position VARCHAR(255),
|
---|
25 | CompanyName VARCHAR(255),
|
---|
26 | EmailAddress VARCHAR(255),
|
---|
27 | LinkedInUserProfileUrl VARCHAR(255)
|
---|
28 | );
|
---|
29 |
|
---|
30 | CREATE TABLE Message (
|
---|
31 | Id INT PRIMARY KEY,
|
---|
32 | UserId INT,
|
---|
33 | MessageType VARCHAR(255) CHECK (MessageType IN ('email', 'linkedin')),
|
---|
34 | EmailSubject VARCHAR(255),
|
---|
35 | EmailText TEXT,
|
---|
36 | LinkedInText TEXT,
|
---|
37 | FOREIGN KEY (UserId) REFERENCES User(Id),
|
---|
38 | CHECK ((MessageType = 'email' AND EmailSubject IS NOT NULL AND EmailText IS NOT NULL) OR (MessageType = 'linkedin' AND LinkedInText IS NOT NULL))
|
---|
39 | );
|
---|
40 |
|
---|
41 | CREATE TABLE Review (
|
---|
42 | Id INT PRIMARY KEY,
|
---|
43 | ReviewerId INT,
|
---|
44 | ProductId INT,
|
---|
45 | ReviewSite VARCHAR(255) NOT NULL,
|
---|
46 | Sentiment VARCHAR(255),
|
---|
47 | ReviewerScore NULL,
|
---|
48 | ReviewText TEXT,
|
---|
49 | DateOfReview DATE NOT NULL,
|
---|
50 | FOREIGN KEY (ReviewerId) REFERENCES Reviewer(Id),
|
---|
51 | FOREIGN KEY (ProductId) REFERENCES Product(DomainName)
|
---|
52 | CHECK (Sentiment = 'positive' OR Sentiment = 'negative' OR Sentiment = 'neutral')
|
---|
53 | CHECK(Sentiment NOT NULL OR ReviewerScore NOT NULL)
|
---|
54 | CHECK (ReviewerScore >= 0);
|
---|
55 | );
|
---|
56 |
|
---|
57 | CREATE TABLE ReviewerSocialMediaLinks (
|
---|
58 | SocialMediaLink VARCHAR(255) PRIMARY KEY,
|
---|
59 | ReviewerId INT,
|
---|
60 | SocialMediaType VARCHAR(255),
|
---|
61 | FOREIGN KEY (ReviewerId) REFERENCES Reviewer(Id)
|
---|
62 | );
|
---|