Merge branch '3-remove-unused-fields-from-member-records' into 'master'
[memberdb.git] / install.sql
1 CREATE TABLE IF NOT EXISTS `members` (
2   `id`               INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3   `number`           INT(10) UNSIGNED NOT NULL,
4   `firstname`        VARCHAR(255)     NOT NULL,
5   `lastname`         VARCHAR(255)     NOT NULL,
6   `nickname`         VARCHAR(255)     NOT NULL,
7   `accountholder`    BLOB             NOT NULL,
8   `accountnumber`    BLOB             NOT NULL,
9   `bankcode`         BLOB             NOT NULL,
10   `bankname`         BLOB             NOT NULL,
11   `directdebit`      TINYINT(1)       NOT NULL,
12   `ddmandatesigdate` DATETIME         NOT NULL,
13   `street`           VARCHAR(255)     NOT NULL,
14   `housenumber`      VARCHAR(255)     NOT NULL,
15   `postcode`         VARCHAR(255)     NOT NULL,
16   `city`             VARCHAR(255)     NOT NULL,
17   `country`          VARCHAR(255)     NOT NULL,
18   `email`            VARCHAR(255)     NOT NULL,
19   `created_at`       DATETIME         NOT NULL,
20   `modified_at`      DATETIME         NOT NULL,
21   PRIMARY KEY (`id`),
22   UNIQUE KEY `number` (`number`)
23 )
24   ENGINE = InnoDB
25   DEFAULT CHARSET = utf8;
26
27 CREATE TABLE IF NOT EXISTS `events` (
28   `id`               INT(10) UNSIGNED                                      NOT NULL AUTO_INCREMENT,
29   `event_date`       DATE                                                  NOT NULL,
30   `event_type`       ENUM ('joined', 'changed', 'leaved')                  NOT NULL,
31   `member_id`        INT(10) UNSIGNED                                      NOT NULL,
32   `member_type`      ENUM ('regular', 'patron', 'honorary')                NULL,
33   `fee`              DECIMAL(10, 2)                                        NULL,
34   `payment_interval` ENUM ('monthly', 'quarterly', 'halfyearly', 'yearly') NULL,
35   `created_at`       DATETIME                                              NOT NULL,
36   `modified_at`      DATETIME                                              NOT NULL,
37   PRIMARY KEY (`id`),
38   UNIQUE KEY `member_id_event_date` (`member_id`, `event_date`)
39 )
40   ENGINE = InnoDB
41   DEFAULT CHARSET = utf8;
42
43 CREATE TABLE IF NOT EXISTS `cryptography` (
44   `description` VARCHAR(255) NOT NULL,
45   `masterkey`   BLOB         NOT NULL,
46   `salt`        BLOB         NOT NULL,
47   `control`     BLOB         NOT NULL,
48   `created_at`  DATETIME     NOT NULL,
49   `modified_at` DATETIME     NOT NULL
50 )
51   ENGINE = InnoDB
52   DEFAULT CHARSET = utf8;
53
54 CREATE TABLE IF NOT EXISTS `earnings` (
55   `id`          INT(10) UNSIGNED                                                                        NOT NULL AUTO_INCREMENT,
56   `date`        DATE                                                                                    NOT NULL,
57   `type`        ENUM ('donation', 'fee', 'old_fee', 'interest', 'account_transfer', 'room', 'catering') NULL,
58   `status`      ENUM ('open', 'paid')                                                                   NOT NULL,
59   `account`     ENUM ('bank', 'cash')                                                                   NOT NULL,
60   `value`       DECIMAL(10, 2)                                                                          NULL,
61   `member_id`   INT(10) UNSIGNED                                                                        NOT NULL,
62   `description` TEXT                                                                                    NOT NULL,
63   `created_at`  DATETIME                                                                                NOT NULL,
64   `modified_at` DATETIME                                                                                NOT NULL,
65   PRIMARY KEY (`id`),
66   INDEX `idx_member_id` (`member_id`),
67   INDEX `idx_date` (`date`)
68 )
69   ENGINE = InnoDB
70   DEFAULT CHARSET = utf8;
71
72 CREATE TABLE IF NOT EXISTS `expenses` (
73   `id`          INT(10) UNSIGNED                                                                           NOT NULL AUTO_INCREMENT,
74   `date`        DATE                                                                                       NOT NULL,
75   `type`        ENUM ('invoice', 'gains_tax', 'bankcharges', 'account_transfer', 'member_fee', 'catering') NULL,
76   `status`      ENUM ('open', 'paid')                                                                      NOT NULL,
77   `account`     ENUM ('bank', 'cash')                                                                      NOT NULL,
78   `value`       DECIMAL(10, 2)                                                                             NULL,
79   `member_id`   INT(10) UNSIGNED                                                                           NOT NULL,
80   `description` TEXT                                                                                       NOT NULL,
81   `created_at`  DATETIME                                                                                   NOT NULL,
82   `modified_at` DATETIME                                                                                   NOT NULL,
83   PRIMARY KEY (`id`),
84   INDEX `idx_member_id` (`member_id`),
85   INDEX `idx_date` (`date`)
86 )
87   ENGINE = InnoDB
88   DEFAULT CHARSET = utf8;