Database documentation
The project is mainly based on three topics, users and their informations, events and their informations, (and obviously the link between users and events) but also geographical informations. You can visualize the database on the picture below, and then details will follow.
This table represents all the countries existing in the world and contains two fields:
- TEXT NOT NULL PRIMARY KEY
country_name: each country has a unique name, none of them can have the same and there isn't any country without name. - TEXT NOT NULL UNIQUE
country_index: to each country refers an unique two letters long ID. There isn't any null ID
This table gives geographical boundaries for a location according. Contains 4 fields referring to the cardinal points.
- INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
id: ID of the specific bound. - TEXT NOT NULL
south: boundary value of the south cardinal point. Can't be null. - TEXT NOT NULL
north: boundary value of the north cardinal point. Can't be null. - TEXT NOT NULL
east: boundary value of the east cardinal point. Can't be null. - TEXT NOT NULL
west: boundary value of the west cardinal point. Can't be null.
- INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
id: ID of the location, unique, auto incremented and cannot be nul. This is the reference to the location. - TEXT NOT NULL
latitude: each location must have a latitude, so it cannot be null. - TEXT NOT NULL
longitude: each location must have a longitude, so it cannot be null. - INTEGER NOT NULL
bound_id: location boundaries ID referring to boundaries of the tablebounds. Cannot be null. - TEXT
street_number: a location may have an address and this is the field for the street number. - TEXT
street_name: a location may have an address and this is the field for the street name. - TEXT
city_district: a location may have an address and this is the field for the city district. - TEXT NOT NULL
city: a proper location has at least a city name. Cannot be null. - TEXT NOT NULL
zipcode: Zipcode is mandatory too. - TEXT
county: a location may have an address and this is the field for the county mame. - TEXT
county_code: a location may have an address and this is the field for the county code. - TEXT
region: a location may have an address and this is the field for the region name. - TEXT
region_code: a location may have an address and this is the field for the region code. - TEXT NOT NULL REFERENCES countries (country_name)
country: a proper location contains a country name. This cannot be null and must refer to the 'countries' table. - TEXT NOT NULL
country_index: each field is useless as it already exists in the 'countries' table -> has to be deleted from the database.
This table represents all the nationalities and contains only one field. It is currently used to defined the nationality of users.
- TEXT NOT NULL PRIMARY KEY
nationality_name: each nationality has a unique name, and it does not make sense to have a nationality without name.
Obviously, this table represents all the languages existing in world and contains one field. It is currently used to defined which languages are spoken in a specific country or at a particular event.
- TEXT NOT NULL PRIMARY KEY
language_name: name of each known language.
This table helps us to map languages to countries. As some countries recognize several languages as their official languages and as a same language can be recognized as official language of different countries we have here a many to many relationship.
- TEXT NOT NULL REFERENCES languages (language_name)
language_name: name of the language referring to an existing language_name of the table "languages". Cannot be null. - TEXT NOT NULL REFERENCES countries (country_name)
country_name: name of the country referring to an existing language_name of the table "languages". Cannot be null.
Due to many to many relationship on different items, all the event informations cannot be regrouped in only one table. Here we have 5 tables:
- INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
event_id: ID of the event, unique, auto incremented and cannot be null. This is the reference to the event. - TEXT NOT NULL
event_name: each event has a name which cannot be null.Several events can have the same name, they ll be differentiated by the fieldevent_id. - TEXT NOT NULL REFERENCES event_types (event_type_name)
event_type: events are registered in categories which are listed in the "event_types" table. This is the reason we have here a foreign key which reference the "event_types". It is not possible for an event to not be registered as it is not possible for the moment to be registered in several categories at the same time. - INTEGER NOT NULL
event_max_nb_of_participants: each event has a maximum number of participants which will be able to join the event. Has to be filled and cannot be null. - TEXT NOT NULL
event_starting_date: each event has a check in date which cannot be null. Text format representing a date. - TEXT NOT NULL
event_ending_date:each event has a check out date which cannot be null. Text format representing a date. - TEXT
event_description: Description of the event, allows to give more details about the event. Can be null. - INTEGER
event_location_id: each event must have a location which is referred in the 'locations' table. This field refers to the location_id in this table.
This table represents languages spoken in each events creating a link between these two. Several languages can be spoken in a same event, as well as several events can share the same spoken languages.
- INTEGER NOT NULL REFERENCES events (event_id)
event_id: ID of the event which refers to an existing event in the "events" table. - TEXT NOT NULL REFERENCES languages (language_name)
language_name: name of the language spoken at the event and refers to the table of existing 'languages'.
Several users will join different events. Here is the table linking these two.
- INTEGER NOT NULL REFERENCES events (event_id)
event_id: ID of the event which refers to an existing event in the "events" table - INTEGER REFERENCES users (user_id)
user_id: ID of the user which refers to an existing user in the "users" table. Can be null, because at the beginning, it will certainly have no participant at this event.
Several users can be the administrator of one or several events. Here is the table linking these admin to events.
- INTEGER NOT NULL REFERENCES events (event_id)
event_id: ID of the event which refers to an existing event in the "events" table - INTEGER NOT NULL REFERENCES users (user_id)
user_id: ID of the user which refers to an existing user in the "users" table. Can't be null, an event has at least one admin.
This table lists all the events categories.
- TEXT NOT NULL PRIMARY KEY
event_type_name: each category has a name which is unique and cannot be null
This table regroups some information related to a user. It contains 7 fields:
- INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
user_id: ID of the user, unique, auto incremented and cannot be nul. This is the user link reference . - TEXT NOT NULL
user_name: each user has an username which cannot be null. Several users can have the same user_name, the user being related by an ID, it is not much of a problem. - TEXT NOT NULL UNIQUE
user_email: each user has a valid email which is unique to him and cannot be null. This can also be a reference to a user. - TEXT NOT NULL
user_password_hash: each user has a password. This field contains the hashed password. Cannot be null - TEXT
user_birthday: User birthday date as a string format. Can be null until the user set it up. - TEXT REFERENCES Nationalities (nationality_name)
user_nationality: each user has to declare one nationality (if he has more than one, then he will have to choose one one of them). This refers to the "nationalities" table, as the nationality must be a real one. - TEXT
user_lastname: User last name. Null until user set ip up. - TEXT
user_firstname: User first name. Null until user set ip up.