Hackpads are smart collaborative documents. .

Saurabh Dhoble

1091 days ago
Unfiled. Edited by Saurabh Dhoble 1091 days ago
Saurabh D use testdb
go
 
 
/**************** all drops ***********************/
if object_id('dbo.ftTran') is not null
        drop table dbo.ftTran
 
if object_id('dbo.dmTime') is not null
        drop table dbo.dmTime
 
if object_id('dbo.dmAccount') is not null
        drop table dbo.dmAccount
 
if object_id('dbo.dmAccountType') is not null
        drop table dbo.dmAccountType
 
if object_id('dbo.dmLocation') is not null
        drop table dbo.dmLocation
 
if object_id('dbo.dmTranType') is not null
        drop table dbo.dmTranType
 
/*********************************************************/
 
 
if object_id('dbo.dmAccountType') is not null
        drop table dbo.dmAccountType
 
create table dbo.dmAccountType
(
        AccountTypeId int PRIMARY KEY IDENTITY(1,1)
        ,AccountType nvarchar(30)
        ,AccountSubType nvarchar(30)
        ,CHECK (AccountType in ('Corporate','Financial Institution','Foundations / Endowments','Government Institution','GP','Healthcare','IndUstry Fund','LP','Nonprofit','Pension Fund','Portfolio Company','Private Wealth'))
        ,CHECK (AccountSubType in ('Accountant','Administrator','Advisor','Asset Manager','Attorney','BAAM Group','Bank','Broker','Consultant','Corporate','Corporate (ERISA)','Corporate (ERISA) Pension Fund','Corporate Health Care','Corporate Pension Fund','Endowment','Family Office','Financial Institution','Foundation'))
)
 
create unique nonclustered index idx_accounttype
        on dbo.dmAccountType(AccountType, AccountSubType)
 
if object_id('dbo.dmLocation') is not null
        drop table dbo.dmLocation
 
create table dbo.dmLocation
(
        LocationId int PRIMARY KEY IDENTITY(1,1)
        ,City nvarchar(30)
        ,[State] nvarchar(30)
        ,Country nvarchar(30)
        ,Region nvarchar(30)
)
 
create unique nonclustered index idx_city
        on dbo.dmLocation(City, [State], Country, Region)
 
if object_id('dbo.dmAccount') is not null
        drop table dbo.dmAccount
 
create table dbo.dmAccount
(
        AccountId int PRIMARY KEY IDENTITY(1,1)
        ,AccountName nvarchar(200)
        ,AccountTypeId int
        ,HQLocationId int
        ,ParentId        int NULL
        ,FOREIGN KEY(AccountTypeId) REFERENCES dbo.dmAccountType(AccountTypeId)
        ,FOREIGN KEY(HQLocationId) REFERENCES dbo.dmLocation(LocationId)
        ,FOREIGN KEY(ParentId) REFERENCES dbo.dmAccount(AccountId)
)
 
if object_id('dbo.dmTranType') is not null
        drop table dbo.dmTranType
 
create table dbo.dmTranType
(
        TransactionTypeId int PRIMARY KEY IDENTITY(1,1)
        ,TransactionType nvarchar(30) NOT NULL
        ,TransactionCategory nvarchar(30) NOT NULL
        ,CHECK(TransactionType IN ('Debit', 'Credit', 'Loan'))
        ,CHECK(TransactionCategory IN ('General','Travel','Repayment','Transfer','Legal'))
)
 
 
if object_id('dbo.dmTime') is not null
        drop table dbo.dmTime
 
create table dbo.dmTime
(
        TimeId int PRIMARY KEY IDENTITY(1,1)
        ,[Date] Date UNIQUE
        ,[IsHoliday] bit
        ,[Day] as Day([Date])
        ,[Month] as Month([Date])
        ,[Year] as Year([Date])
)
 
create unique nonclustered index idx_trantype_trancategory
        on dbo.dmTranType(TransactionType, TransactionCategory)
 
...

Contact Support



Please check out our How-to Guide and FAQ first to see if your question is already answered! :)

If you have a feature request, please add it to this pad. Thanks!


Log in