User:Chughakshay16/databasedetails

This page shows the details of the database structure used in ConferenceExtension. If you want to know more about how this extension works,and its architecture details then look on this page. =1. conferences=

conf_id
This is the primary key for this table.This key would be referenced by many other tables which such as events, pages and some more.

conf_title
This will define the title of the conference. This value would be set by the admin during the conference setup and this is the same title which will be used as $par in the Special Page (Special:Dashboard/). This title will also be used for marking the title on the main page of the conference.There can't be two conferences with the same title. All the pages for the conference will have conference title prefixed in their title.

conf_begin_date
This is the begin date of the conference as entered by the admin. Code will use start_date and end_date to figure out the number of days for the conference.

conf_end_date
This is the end date of the conference.

conf_descr
This is the description which will be shown on the main page of the conference. And this value is also entered by the admin during the setup.

conf_venue
This defines the venue for the conference. Besides using it on the pages with the help of magic word such as it wont be of any use.

conf_capacity
This defines the upper limit for the number of attendees for the conference. Once the limit is crossed a notice would be shown on the registration page that the registration is closed for the conference. If the admin wants he/she can later change this value depending on the situation.

Schema summary
DESCRIBE conferences; : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =2. pages= The pages table is where we store all the information about the pages created for the conference. Each entry in this table contains a link to the core page table, this way we can keep a track of all the changes made to a page. This provides an access point for extracting pages related to a particular conference.
 * Field                | Type                | Null | Key | Default | Extra          |
 * conf_id              | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * conf_title           | varbinary(255)      | NO   | UNI | NULL    |                |
 * conf_begin_date      | timestamp           | NO   |     | NULL    |                |
 * conf_end_date        | timestamp           | NO   |     | NULL    |                |
 * conf_descr           | tinyblob            | NO   |     | NULL    |                |
 * conf_venue           | varbinary(255)      | NO   |     | NULL    |                |
 * conf_capacity        | int(4) unsigned     | NO   |     | 0       |                |

page_conf_id
This is a foreign key to the conferences table. As each entry of this table would be attached to one of the conferences.This column can be used to extract all the pages for a particular conference.

page_ref_id
This is a foreign key to the core page table(page_id). This way we can associate all the other page features already present in the Mediawiki with the new conference pages created. All the restrictions for a page, and properties for a page can be modified through this field.

page_conf_type
There are various types of pages that can be created for a conference such as Welcome Page, Submission Page,. All such types will be stored within a PHP array in the code, and for each type a specific code would be present. So from this array a code would be used to fill this field.

page_conf_namespace
This value would be the one set by the admin, and this would be same to the page_namespace(page table).

page_conf_title
This value would also be set the admin, and this would be same to the page_title(page table).

Schema summary
DESCRIBE pages; : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++
 * Field                | Type                | Null | Key | Default | Extra          |
 * page_conf_id         | int(8) unsigned     | NO   | UNI | NULL    |                |
 * page_ref_id          | int(8) unsigned     | NO   | UNI | NULL    |                |
 * page_conf_type       | int(5)              | NO   |     | NULL    |                |
 * page_conf_namespace  | int(11)             | NO   |     | NULL    |                |
 * page_conf_title      | varbinary(255)      | NO   |     | NULL    |                |

=3. organizers= The organizers table consist of all the organizers who are set by the admin during the conference setup. There would be a separate page for displaying the organizers of the conference. So the list of organizers for that page are taken from this table, and then the organizers can put other necessary information about them on that page itself. There is no point in storing that other information in the database.

org_id
Primary key for the table.

org_conf_id
Reference to the conference_id of the conferences table.

org_ref_id
This points to the user_id of this organizer in the user (core) table.

org_cat
An organizer can be classified in different categories, such as in a Sponsorship Team, or the Developing team.

org_post
Every organizer can be assigned a post as well within a particular category. org_cat and org_post are just used to display the role and category of the organizer on the page. No rights or permissions are calculated on the basis of these two fields.

Schema summary
DESCRIBE organizers; : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =4. speakers=
 * Field                | Type                | Null | Key | Default | Extra          |
 * org_id               | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * org_conf_id          | int(8) unsigned     | NO   |     | NULL    |                |
 * org_ref_id           | int(11)unsigned     | NO   |     | NULL    |                |
 * org_cat              | varchar(20)         | NO   |     | NULL    |                |
 * org_post             | varchar(20)         | NO   |     | NULL    |                |

The speakers table consist of all the authors/speakers who have submitted their proposals. So the information concerning a speaker is stored in this table, whenever a speaker wishes to submit his/her proposal a Submission Form is presented to him/her. All the details in that form are loaded into this table, and a corresponding submission page is created for that speaker which only he has rights to modify.

speaker_id
Primary key for this table. Every time a submission is made a new entry is created in this table.

speaker-ref-id
Foreign key to the user table(user_id). This maintains a reference to the user row in user table, so it helps in knowing about all the other relevant information about the user(or speaker in our case). Every speaker who wishes to submit a proposal for the conference should already have an account for this wiki.(i.e only logged in users would have the rights to submit a proposal)

affiliation
This field just has a value which was submitted during the submission form.

blog_url
Personal blog url of the speaker.

country
Country of the speaker.

Schema summary
DESCRIBE page; : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =5. submissions=
 * Field                | Type                | Null | Key | Default | Extra          |
 * speaker_id           | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * speaker_ref_id       | int(10)unsigned     | NO   | UNI | NULL    |                |
 * affiliation          | varchar(100)        | NO   |     | NULL    |                |
 * blog_url             | varchar(200)        | NO   |     | NULL    |                |
 * country              | varchar(45)         | NO   |     | NULL    |                |

The submissions table holds the information of all the proposals submitted during the conference. A speaker can have more than one submissions for a particular conference. Speaker is supposed to register just once, and then he/she can submit as many proposals he/she can.

sub_id
Primary key for the table.

sub_author_id
Foreign key to the speakers table(speaker_id). More than one rows in this table can point to the same speaker_id.

sub_title
The title of the proposal as set by the speaker.

sub_type
The type of the proposal. It would be one of the following values (workshop,presentation,tutorial,panel)

abstract
The abstract for the proposal.

track
The track type it belongs to.

length
The length of the presentation ( in mins).

slides_info
Extra information about the slides.

slot_req
Some preference for the slot

sub_ref_id
References the page_id(page table) which holds the information about this submission.Its a wiki page but all its displayed information will be fetched from this table. This field cant be empty, If a submission page is deleted then its corresponding entry here should be deleted as well.

Schema summary
DESCRIBE submissions; : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =6. accounts= The accounts table stores the information of all the users who have made an account for this conference. These accounts are different from users which are present in the user table. Not every wiki account holder(in user table) is present in the accounts table but every entry in accounts table would be present in the user table.
 * Field                | Type                | Null | Key | Default | Extra          |
 * sub_id               | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * sub_author_id        | int(8) unsigned     | NO   | UNI | NULL    |                |
 * sub_title            | varbinary(255)      | NO   | UNI | NULL    |                |
 * sub_type             | varchar(15)         | NO   |     | NULL    |                |
 * sub_track            | varchar(25)         | NO   |     | NULL    |                |
 * sub_abstract         | blob                | NO   |     | NULL    |                |
 * sub_length           | int(3) unsigned     | NO   |     | 25      |                |
 * sub_slides_info      | blob                | NO   |     | NULL    |                |
 * sub_slot_req         | int(8) unsigned     | NO   |     | NULL    |                |
 * sub_ref_id           | int(8) unsigned     | NO   | UNI | NULL    |                |

account_id
Primary key for the table.

account_ref_id
Reference to the user_id in the user table.Whenever someone creates an account for the conference first of all an entry is created for that user in the user table and that user_id is passed over to this table to store a reference for it.

account_first_name
First name of the account holder.

acount_last_name
Last name of the account holder.

Schema summary
DESCRIBE accounts;: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =7. registrations=
 * Field                | Type                | Null | Key | Default | Extra          |
 * account_id           | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * account_ref_id       | int(11)unsigned     | NO   |     | NULL    |                |
 * account_first_name   | varchar(20)         | NO   |     | NULL    |                |
 * account_last_name    | varchar(20)         | NO   |     | NULL    |                |

The registrations table stores all the transactions made by an account holder.One account holder can make more than one registration for a particular conference.

reg_id
Primary key for the table.

reg_account_id
References the account_id in the accounts table.

reg_type
Type of registration for the conference.(for eg . a student type registration)

reg_dietary_rest
Dietary restrictions specified by the account holder while making this registration.

reg_other_diet_opts
Other dietary options.

reg_other_opts
Extra information.

reg_badge_info
Badge information type( only first name on the badge, or just the nickname or the mediawiki username)

Schema summary
DESCRIBE registrations;: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =8. passport_info=
 * Field                | Type                | Null | Key | Default | Extra          |
 * reg_id               | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * reg_account_id       | int(11)             | NO   | MUL | NULL    |                |
 * reg_type             | varchar(10)         | NO   |     | NULL    |                |
 * reg_ dietary_rest    | tinyblob            | NO   |     | NULL    |                |
 * reg_other_diet_opts  | tinyblob            | NO   |     | NULL    |                |
 * reg_other_opts       | tinyblob            | NO   |     | NULL    |                |
 * reg_badge_info       | tinyint(1)          | NO   |     | 1       |                |

The passport_info table maintains a 1:1 relationship with the accounts table. But just to keep things clear, I have built a separate table for storing the passport information of an account holder.

p_id
Primary key for the table.

paccount_id
The id for the account holder.

p_no
The passport no.

p_valid_until
Validity date for the passport.

p_place
Place where it was issued.

p_dob
Date of birth for the account holder

Schema summary
DESCRIBE passport_info;</tt>: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =9. transactions=
 * Field                | Type                | Null | Key | Default | Extra          |
 * p_id                 | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * paccount_id          | int(8)unsigned      | NO   | UNI | NULL    |                |
 * p_no                 | int(11) unsigned    | NO   | UNI | NULL    |                |
 * p_issued_by          | varchar(45)         | NO   |     | NULL    |                |
 * p_valid_until        | timestamp           | NO   |     | NULL    |                |
 * p_place              | varchar(20)         | NO   |     | NULL    |                |
 * p_dob                | timestamp           | NO   |     | NULL    |                |
 * p_country            | varchar(3)          | NO   |     | NULL    |                |

The transactions table is responsible for maintaining a list of all the financial transactions that occurred for a conference.transactions table maintain a 1:1 relationship with the registrations table.

tr_id
Primary key for the transaction.

tr_reg_id
The registration for which this transaction happened.

tr_type
The type of transaction(debit card, paypal..)

tr_amount
The amount for the transaction.

tr_status
The status of the transaction(on failure the value would be0 else it would be 1)

Schema summary
DESCRIBE transactions;</tt>: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =10. events=
 * Field                | Type                | Null | Key | Default | Extra          |
 * tr_id                | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * tr_reg_id            | int(8)unsigned      | NO   |     | NULL    |                |
 * tr_type              | varchar(10)         | NO   |     | NULL    |                |
 * tr_amount            | int(10)             | NO   |     | NULL    |                |
 * tr_status            | tinyint(1) unsigned | NO   |     | NULL    |                |

The events table holds the information about all the events for a particular conference. The admin sets the events during the conference setup, and with each event a location is attached as well.

event_id
Primary key for this table

event_conf_id
Reference to the conference for which this event was set.

event_start_time
The starting time of the conference.

event_end_time
The ending time of the conference

event_date
The date on which this event is held.

event_location_id
The location for this conference.This field refers to a row in locations table. More than one event can point to the same location but not at the same time.

event_topic
The topic for that event. A sort of a brief description which would be displayed in the schedule of the conference.

event_group
This is the group that can attend this event.

Schema summary
DESCRIBE events;</tt> : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =11. locations=
 * Field                | Type                | Null | Key | Default | Extra          |
 * event_id             | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * event_conf_id        | int(8) unsigned     | NO   |     | NULL    |                |
 * event_start_time     | datetime            | NO   |     | NULL    |                |
 * event_end_time       | datetime            | NO   |     | NULL    |                |
 * event_location_id    | int(8) unsigned     | NO   |     | NULL    |                |
 * event_topic          | varchar(45)         | NO   |     | NULL    |                |
 * event_group          | varbinary(16)       | NO   |     | NULL    |                |

The locations table would be used to store all the locations for all the conferences. Since it has got no column which refers to the conference_id of the conferences table so the entries from this table can only be fetched or updated with the help of events table. With join operations ,locations for a particular conference can be fetched.

location_id
Primary key for this table.

room_no
Room no of the location if at all specified, if not then room no wont be used on the pages.

location_descr
Description of this location, if in some cases room no is not specified then the description should be clear enough to point to a specific location.

location_image_url
Any url that needs to be displayed alongside the location description on a page.

Schema summary
DESCRIBE locations;</tt> : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =12. poll_question=
 * Field                | Type                | Null | Key | Default | Extra          |
 * location_id          | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * room_no              | int(6) unsigned     | NO   |     | 0       |                |
 * location_descr       | tinyblob            | NO   |     | NULL    |                |
 * location_image_url   | varchar(200)        | NO   |     | NULL    |                |

The poll_question table stores the information of all question boxes set on this wiki. A question cant point to two different conferences (i.e only 1:m relationship)

id
Primary key for this table.

title
Title for the question box

question
Question for this poll.

author_id
User who created this poll

start_time
Starting time for this poll

end_time
Ending time for this poll. After end_time is reached, poll would get disabled.

add_answer_access
Who can modify the answers for this poll

voting_access
Who can vote for this poll.(basically the users to whom this poll would be visible)

result_access
Who can view the results for this poll.

admin_access
Who can modify or delete this poll.(by default it will be the one who set up this poll)

conf_id
reference to the conference

Schema summary
DESCRIBE poll_question;</tt> : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =13. poll_answers=
 * Field                | Type                | Null | Key | Default | Extra          |
 * id                   | int(8)unsigned      | NO   | PRI | NULL    | auto_increment |
 * title                | varchar(200)        | NO   |     | NULL    |                |
 * question             | varchar(200)        | NO   |     | NULL    |                |
 * author_id            | int(11)unsigned     | NO   |     | NULL    |                |
 * start_time           | datetime            | NO   |     | NULL    |                |
 * end_time             | datetime            | NO   |     | NULL    |                |
 * add_answer_access    | tinyint(1)          | NO   |     | NULL    |                |
 * voting_access        | tinyint(1)          | NO   |     | NULL    |                |
 * result_access        | tinyint(1)          | NO   |     | NULL    |                |
 * admin_access         | tinyint(1)          | NO   |     | NULL    |                |
 * conf_id              | int(8) unsigned     | NO   |     | NULL    |                |

The poll_answers table stores all the answers set by the author of the poll during its setup.

id
Primary key for this table.

qid
reference to the poll_question

answer
answer in the form of text.

author_id
author who added this answer

Schema summary
DESCRIBE poll_answers;</tt> : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++ =14. poll_votes=
 * Field                | Type                | Null | Key | Default | Extra          |
 * id                   | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * qid                  | int(8) unsigned     | NO   |     | NULL    |                |
 * answer               | varchar(200)        | NO   |     | NULL    |                |
 * author_id            | int(11) unsigned    | NO   |     | NULL    |                |

The locations table

id
Primary key for this table.

user_id
references the user which voted

answer_id
references the answer which user voted for

voted_time
the time when the user voted

Schema summary
DESCRIBE poll_votes;</tt> : +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++
 * Field                | Type                | Null | Key | Default | Extra          |
 * id                   | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * user_id              | int(11) unsigned    | NO   |     | NULL    |                |
 * answer_id            | int(8) unsigned     | NO   |     | NULL    |                |
 * voted_time           | timestamp           | NO   |     | NULL    |                |