Skip to content

SQL Server Creation

Jump to bottom
Adam Hani Schakaki edited this page Sep 3, 2013 · 16 revisions 
CREATE TABLE [media]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [posts]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [post_likes]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [post_reports]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [post_shares]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [post_views]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [promotions]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [promotion_redemptions]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [users]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [user_searches]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venues]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_categories]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_comments]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_followers]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_loads]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_managers]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_rsvps]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_shares]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_staff]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)

CREATE TABLE [venue_views]
(
  [id]              bigint          NOT NULL  IDENTITY  PRIMARY KEY CLUSTERED
)


ALTER TABLE [media]
ADD
  [type]            nvarchar(255)   NOT NULL


ALTER TABLE [posts]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id]),
  [caption]         nvarchar(100)   NULL,
  [hidden]          tinyint         NOT NULL  DEFAULT 0


ALTER TABLE [post_likes]
ADD
  [time]            bigint          NOT NULL,
  [post_id]         bigint          NOT NULL  REFERENCES [posts]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [post_reports]
ADD
  [post_id]         bigint          NOT NULL  REFERENCES [posts]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [post_shares]
ADD
  [time]            bigint          NOT NULL,
  [media_id]        bigint          NOT NULL  REFERENCES [media]([id]),
  [post_id]         bigint          NOT NULL  REFERENCES [posts]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [post_views]
ADD
  [post_id]         bigint          NOT NULL  REFERENCES [posts]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [promotions]
ADD
  [title]           nvarchar(20)    NOT NULL,
  [description]     nvarchar(80)    NOT NULL,
  [passcode]        nvarchar(255)   NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [start]           bigint          NULL,
  [end]             bigint          NULL,
  [maximum]         int             NULL,
  [creator]         bigint          NOT NULL  REFERENCES [users]([id]),
  [level]           tinyint         NOT NULL  DEFAULT 0,
  [hidden]          tinyint         NOT NULL  DEFAULT 0


ALTER TABLE [promotion_redemptions]
ADD
  [time]            bigint          NOT NULL,
  [promotion_id]    bigint          NOT NULL  REFERENCES [promotions]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [users]
ADD
  [facebook_id]     nvarchar(255)   NULL,
  [twitter_id]      nvarchar(255)   NULL,
  [twitter_token]   nvarchar(255)   NULL,
  [twitter_secret]  nvarchar(255)   NULL,
  [facebook]        nvarchar(50)    NULL,
  [twitter]         nvarchar(140)   NULL,
  [forename]        nvarchar(255)   NOT NULL,
  [surname]         nvarchar(255)   NOT NULL,
  [age]             tinyint         NULL,
  [birth_day]       tinyint         NULL,
  [birth_month]     tinyint         NULL,
  [birth_year]      smallint        NULL,
  [gender]          nvarchar(1)     NULL,
  [employee]        tinyint         NOT NULL  DEFAULT 0,
  [joined]          bigint          NOT NULL,
  [country]         nvarchar(3)     NULL,
  [language]        nvarchar(7)     NULL,
  [email]           nvarchar(255)   NOT NULL,
  [top5]            tinyint         NOT NULL  DEFAULT 0,
  [save_locally]    tinyint         NOT NULL  DEFAULT 1,
  [last_login]      bigint          NOT NULL,
  [last_facebook]   tinyint         NOT NULL  DEFAULT 1,
  [last_twitter]    tinyint         NOT NULL  DEFAULT 1


ALTER TABLE [user_searches]
ADD
  [time]            bigint          NOT NULL,
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id]),
  [term]            nvarchar(255)   NOT NULL


ALTER TABLE [venues]
ADD
  [name]            nvarchar(255)   NOT NULL,
  [address]         text            NOT NULL,
  [country]         nvarchar(3)     NOT NULL,
  [phone]           nvarchar(255)   NULL,
  [email]           nvarchar(255)   NULL,
  [email_verified]  tinyint         NOT NULL  DEFAULT 0,
  [category_id]     bigint          NOT NULL  REFERENCES [venue_categories]([id]),
  [headline]        nvarchar(40)    NULL      DEFAULT (''),
  [tonight]         text            NULL      DEFAULT (''),
  [website]         nvarchar(255)   NULL,
  [facebook]        nvarchar(50)    NULL,
  [twitter]         nvarchar(140)   NULL,
  [facebook_id]     nvarchar(255)   NULL,
  [twitter_id]      nvarchar(255)   NULL,
  [twitter_token]   nvarchar(255)   NULL,
  [twitter_secret]  nvarchar(255)   NULL,
  [lat]             decimal(8, 6)   NULL,
  [lon]             decimal(9, 6)   NULL,
  [official]        tinyint         NOT NULL  DEFAULT 0,
  [verified]        tinyint         NOT NULL  DEFAULT 0,
  [customer_spend]  decimal(10, 2)  NOT NULL  DEFAULT 0.00,
  [authenticated]   bigint          NULL,
  [creator]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [venue_categories]
ADD
  [type]            nvarchar(255)   NOT NULL


ALTER TABLE [venue_comments]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id]),
  [comment]         text            NOT NULL


ALTER TABLE [venue_followers]
ADD
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [venue_loads]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [venue_managers]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [venue_rsvps]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id]),
  [maybe]           tinyint         NOT NULL  DEFAULT 0,
  [going]           tinyint         NOT NULL  DEFAULT 0,
  [checked_in]      tinyint         NOT NULL  DEFAULT 0


ALTER TABLE [venue_shares]
ADD
  [time]            bigint          NOT NULL,
  [media_id]        bigint          NOT NULL  REFERENCES [media]([id]),
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])


ALTER TABLE [venue_staff]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id]),
  [promo_perm]      tinyint         NOT NULL  DEFAULT 0


ALTER TABLE [venue_views]
ADD
  [time]            bigint          NOT NULL,
  [venue_id]        bigint          NOT NULL  REFERENCES [venues]([id]),
  [user_id]         bigint          NOT NULL  REFERENCES [users]([id])
Clone this wiki locally