ccni-migration.V1.000__setup.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of ccni Show documentation

SQL Model for Charity Register of Northern Ireland

There is a newer version: 1.1.0

create schema ccni;

create table ccni.charity (
    charity_number varchar not null,
    charity_name varchar not null,
    date_registered varchar not null,
    status varchar,
    financial_year_end varchar,
    total_income bigint,
    total_spending bigint,
    charitable_spending bigint,
    income_generation bigint,
    public_address varchar,
    website varchar,
    email varchar,
    telephone varchar,
    company_number varchar,
    what varchar,
    who varchar,
    how varchar,
    charitable_purposes varchar,
    other_name varchar,
    type_of_governing_document varchar,
    financial_period_start varchar,
    financial_period_end varchar,
    previous_total_income bigint,
    employed_staff int,
    volunteers int,
    donations_and_legacies_income bigint,
    charitable_activities_income bigint,
    other_trading_activities_income bigint,
    investments_income bigint,
    other_income bigint,
    total_income_and_endowments bigint,
    raising_funds_expenditure bigint,
    charitable_activities_expenditure bigint,
    governance_expenditure bigint,
    other_expenditure bigint,
    total_expenditure bigint,
    total_fixed_assets bigint,
    total_net_assets bigint,
    -- compensate for spurious comma at end of each CSV line
    dummy varchar
);

alter table ccni.charity add primary key (charity_number);

create index ix_charity_number_year_end on ccni.charity (charity_number, financial_year_end);