Skip to content

Database documentation

Jump to bottom Edit New page
aldeen edited this page Jan 31, 2014 · 9 revisions

Befalol Database

Sum up

The project is mainly based on three items, 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. Database visualisation

Geographical Informations

Countries

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

Nationalities

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.

Languages

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.

Countries_languages

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.

Events

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:

events table

  • INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT event_id : ID of the event, unique, auto incremented and cannot be nul. 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 field event_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 full. Text format representing a date.
  • TEXT NOT NULL event_ending_date :each event has a check out date which cannot be full. Text format representing a date.
  • TEXT event_description :
  • TEXT NOT NULL event_address : each event has his own location and all event have to take place somewhere so the field cannot be null
  • TEXT NOT NULL event_zipcode : part of the event location, the zipcode is mandatory
  • TEXT NOT NULL event_city_name : part of the event location, the city is mandatory
  • TEXT NOT NULL REFERENCES countries (country_name) event_country_name: part of the event location, the country name is also mandatory. Foreign key on the 'countries' table as the event location must be in an existing country.

event_languages

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'.

event_participants

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.

event_holders

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.

event_types

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

users

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.
Add a custom footer
Add a custom sidebar
Clone this wiki locally