CRM.sql

-- Entidades

CREATE TABLE Contacts
(
    ContactID SERIAL PRIMARY KEY
);

CREATE TABLE Deals
(
    DealID SERIAL PRIMARY KEY
);

CREATE TABLE Accounts
(
    AccountID SERIAL PRIMARY KEY,
    ContactID INT REFERENCES Contacts (ContactID),
    DealID    INT REFERENCES Deals (DealID),
    Type      VARCHAR(255)
);

CREATE TABLE Campaigns
(
    CampaignID SERIAL PRIMARY KEY
);

CREATE TABLE Leads
(
    LeadID SERIAL PRIMARY KEY
);

CREATE TABLE Pipelines
(
    PipelineID SERIAL PRIMARY KEY
);

CREATE TABLE Stages
(
    StageID SERIAL PRIMARY KEY
);

-- Tabelas de Referencia

CREATE TABLE ContactCampaign
(
    ContactID  INT REFERENCES Contacts (ContactID),
    CampaignID INT REFERENCES Campaigns (CampaignID),
    PRIMARY KEY (ContactID, CampaignID)
);

CREATE TABLE CampaignLead
(
    CampaignID INT REFERENCES Campaigns (CampaignID),
    LeadID     INT REFERENCES Leads (LeadID),
    PRIMARY KEY (CampaignID, LeadID)
);

CREATE TABLE CampaignStage
(
    CampaignID INT REFERENCES Campaigns (CampaignID),
    StageID    INT REFERENCES Stages (StageID),
    PRIMARY KEY (CampaignID, StageID)
);

CREATE TABLE DealContact
(
    DealID    INT REFERENCES Deals (DealID),
    ContactID INT REFERENCES Contacts (ContactID),
    Role      VARCHAR(255),
    PRIMARY KEY (DealID, ContactID)
);