m9FxBUsE

1/*
2[------------------------------------------------------------------------------------------]
3    Countries where the cities exist, in which festivals were held
4[------------------------------------------------------------------------------------------]
5*/
6CREATE TABLE IF NOT EXISTS `%PREFIX%_countries`
7(
8    `id`        int(10) unsigned    NOT NULL AUTO_INCREMENT, # ID of the country for internal use
9    `name`      varchar(255) UNIQUE NOT NULL,                # The name of the country (e.g. "Germany")
10    `latitude`  FLOAT(8, 5)         NOT NULL,                # The latitudinal position of the country
11    `longitude` FLOAT(8, 5)         NOT NULL,                # The longitudinal position of the country
12    PRIMARY KEY (`id`),
13    KEY `name` (`name`)
14) ENGINE = InnoDB
15  DEFAULT CHARSET = utf8
16  COLLATE = utf8_unicode_ci;
17
18/*
19[------------------------------------------------------------------------------------------]
20    Cities in which the festivals were held
21[------------------------------------------------------------------------------------------]
22*/
23CREATE TABLE IF NOT EXISTS `%PREFIX%_cities`
24(
25    `id`         int(10) unsigned NOT NULL AUTO_INCREMENT, # ID of the city for internal use
26    `country_id` int(10) unsigned NOT NULL,                # ID of the country in which the city exists
27    `name`       varchar(255)     NOT NULL,                # The name of the city (e.g. "Brussels")
28    `latitude`   FLOAT(8, 5)      NOT NULL,                # The latitudinal position of the city
29    `longitude`  FLOAT(8, 5)      NOT NULL,                # The longitudinal position of the city
30    PRIMARY KEY (`id`),
31    KEY `name` (`name`)
32) ENGINE = InnoDB
33  DEFAULT CHARSET = utf8
34  COLLATE = utf8_unicode_ci;
35
36/*
37[------------------------------------------------------------------------------------------]
38    Super 8 Film Festival that was held at some point in time.
39[------------------------------------------------------------------------------------------]
40*/
41CREATE TABLE IF NOT EXISTS `%PREFIX%_festivals`
42(
43    `id`         int(10) unsigned NOT NULL AUTO_INCREMENT, # ID of the city for internal use
44    `country_id` int(10) unsigned NOT NULL,                # ID of the country in which the festival exists
45    `city_id`    int(10) unsigned NOT NULL,                # ID of the city in which the festival exists
46    `name`       varchar(255)     NOT NULL,                # The name of the city (e.g. "Brussels")
47    `date`       DATE,
48    PRIMARY KEY (`id`),
49    KEY `name` (`name`),
50    KEY `date` (`date`)
51) ENGINE = InnoDB
52  DEFAULT CHARSET = utf8
53  COLLATE = utf8_unicode_ci;
54
55/*
56[------------------------------------------------------------------------------------------]
57    The people who actually created the films
58[------------------------------------------------------------------------------------------]
59*/
60CREATE TABLE IF NOT EXISTS `%PREFIX%_filmmakers`
61(
62    `id`                int(10) unsigned NOT NULL AUTO_INCREMENT, # ID of the city for internal use
63    `first_name`        varchar(255),
64    `last_name`         varchar(255),
65    `organization_name` int(10) unsigned,                         # ID of the contribution for internal use
66    primary key (`id`),
67    KEY `name` (`first_name`),
68    KEY `name` (`last_name`),
69    KEY `name` (`organization_name`)
70) ENGINE = InnoDB
71  DEFAULT CHARSET = utf8
72  COLLATE = utf8_unicode_ci;
73
74
75/*
76[------------------------------------------------------------------------------------------]
77Contributors are people who contribute any amount of information or media to the website.
78[------------------------------------------------------------------------------------------]
79*/
80CREATE TABLE IF NOT EXISTS `%PREFIX%_contributors`
81(
82    `id`                int(10) unsigned NOT NULL AUTO_INCREMENT, # ID of the contributor for internal use
83    `first_name`        varchar(255),
84    `last_name`         varchar(255),
85    `organization_name` int(10) unsigned,                         # ID of the contribution for internal use
86    primary key (`id`)
87) ENGINE = InnoDB
88  DEFAULT CHARSET = utf8
89  COLLATE = utf8_unicode_ci;
90
91
92/*
93[------------------------------------------------------------------------------------------]
94Contributors are people who contribute any amount of information or media to the website.
95[------------------------------------------------------------------------------------------]
96*/
97CREATE TABLE IF NOT EXISTS `%PREFIX%_contribution_types`
98(
99    `id`   int(10) unsigned NOT NULL AUTO_INCREMENT, # ID of the contribution type for internal use
100    `name` varchar(255),
101    primary key (`id`)
102) ENGINE = InnoDB
103  DEFAULT CHARSET = utf8
104  COLLATE = utf8_unicode_ci;
105
106INSERT INTO `%PREFIX%_contribution_types` (`name`)
107VALUES ('films');
108INSERT INTO `%PREFIX%_contribution_types` (`name`)
109VALUES ('memorabilia');
110INSERT INTO `%PREFIX%_contribution_types` (`name`)
111VALUES ('newspaper');
112INSERT INTO `%PREFIX%_contribution_types` (`name`)
113VALUES ('magazine');
114INSERT INTO `%PREFIX%_contribution_types` (`name`)
115VALUES ('poster');
116
117
118/*
119[------------------------------------------------------------------------------------------]
120Contributions made by people
121[------------------------------------------------------------------------------------------]
122*/
123CREATE TABLE IF NOT EXISTS `%PREFIX%_contributions`
124(
125    `id`             int(10) unsigned NOT NULL AUTO_INCREMENT, # ID of the contribution for internal use
126    `type`           int(10) unsigned NOT NULL,
127    `contributor_id` int(10) unsigned NOT NULL,                # ID of the contribution for internal use
128    primary key (`id`)
129) ENGINE = InnoDB
130  DEFAULT CHARSET = utf8
131  COLLATE = utf8_unicode_ci;