BeWelcome Database Global model

Here is a paste of the tables and columns definition

The columns/Table with no comment are the PT one, it is a very important todo to comment them

Nota :

• don't be afraid of some redudancies in tables, especially in geo data this is going to change.

• all these tables are not in the same database for future database splitting

• this is not exactly uptodate and teh folowing can be generated with an automatic tool

hcvoltest Schema

addresses

Addresses which belong to one member

id int '

IdMember? int Member who own this address

HouseNumber? int house member of the member

StreetName? int Name of the street where the address is, this is a crypted field which the member can choose to hide

Zip int Zip of the address, this is a crypted field which the member can choose to hide

IdCity? int City where the address is

Explanation int Explanation about the current address (useful for secundary addresses) this is a translatable field

Rank tinyint rank of the address (0 being the main address of the member)

updated timestamp '

created timestamp '

IdGettingThere? int This is and extra field which allow the member how to get to this address, this field can be translated

blog

InnoDB free: 12288 kB

blog_id int '

flags blob '

blog_created datetime '

user_id_foreign int '

country_id_foreign int '

trip_id_foreign int '

blog_categories

InnoDB free: 12288 kB

blog_category_id int '

name varchar '

user_id_foreign int '

blog_categories_seq

'

id int '

blog_comments

InnoDB free: 12288 kB

id int '

blog_id_foreign int '

user_id_foreign int '

created datetime '

title varchar '

text mediumtext '

blog_comments_seq

'

id int '

blog_data

'

blog_id int '

edited datetime '

blog_title varchar '

blog_text longtext '

blog_start datetime when this subtrip starts

blog_end datetime when this subtrip ends

blog_latitude float '

blog_longitude float '

blog_geonameid int '

blog_display_order int TODO: only used if start and end are unset and user wants to reorder

blog_seq

'

id int '

blog_tags

InnoDB free: 12288 kB

blog_tag_id int '

name varchar '

blog_tags_seq

'

id int '

blog_to_category

InnoDB free: 12288 kB

created datetime '

blog_category_id_foreign int '

blog_id_foreign int '

blog_to_tag

InnoDB free: 12288 kB

blog_id_foreign int '

blog_tag_id_foreign int '

broadcast

This table is for massmail used by adminmassmails

id int primary key

IdCreator? int Id of the member who created the massmail

Name text Name of the mass mail

created timestamp creation date

Status enum '

Type enum '

broadcastmessages

This is the table with the list of members to broad cast

IdBroadcast? int id of the broadcast entry

IdReceiver? int Id of the receiver

IdEnqueuer? int Id of the volunteer who enqueue the message

Status enum Status of the message

updated timestamp last update

cal_eventdata

'

event_id int '

title varchar '

link text '

description text '

cal_events

InnoDB free: 12288 kB

event_id int '

user_id_foreign int Owner of event

from datetime '

to datetime '

type int visibility or other options

created datetime '

cal_event_to_tag

InnoDB free: 12288 kB

tag_id int '

event_id int '

cal_tags

InnoDB free: 12288 kB

id int '

tag varchar '

cities

This is the table with all cities (from geonames)

id int '

NbMembers? int Numbers of active members in this city (this is a redudancy)

Name varchar '

ansiname varchar '

OtherNames? varchar '

latitude double '

longitude double '

feature_class char '

feature_code varchar '

country_code char '

cc2 varchar '

admin1_code varchar '

population int '

elevation int '

gtopo30 int '

timezone varchar '

modification_date date '

IdRegion? int '

ActiveCity? enum '

IdCountry? int '

comments

Comments between members

IdFromMember? int '

IdToMember? int '

Lenght set '

Quality enum '

TextFree? text '

TextWhere? text '

updated timestamp '

id int '

created timestamp '

AdminAction? enum '

copy_of_members

Members data table

id int IdMember?

Username varchar This is the username of the member its a unique field (shared with the user table)

ChangedId? int Used in case the member had a Username renamed (CurrentId? is then the id of the members entry to use)

Email int this is the main (verified at signup) mail of the member it is Crypted

HomePhoneNumber? int This is the phone number of the member its a field the member can choose to display or not , it is Crypted

CellPhoneNumber? int This is the cell phone number of the member its a field the member can choose to display or not , it is Crypted

WorkPhoneNumber? int This is the work phone number of the member its a field the member can choose to display or not , it is Crypted

SecEmail? int This is an eventual second email for contacting the member it is crypted

FirstName? int This is the first name of the member, the member can choose to hide it or not, it is crypted

SecondName? int This is the first name of the member, the member can choose to hide it or not, it is crypted

LastName? int This is the lastname (family name) of the member, the member can choose to hide it or not, it is crypted

Accomodation enum Here the member can state if he can offer accomodation and which kind

AdditionalAccomodationInfo? int Here the member can enter as text more precision about his accomodation possibilities;, this is can be translated

ILiveWith int Here the member can describe has text the people who he live with, this can be translated

IdentityCheckLevel? tinyint Futur Use, will define how far the identity of the member can be certified

InformationToGuest? int Here the member can enter specific information that may be of general interest for his guest, this can be translated

TypicOffer? set Other typical offer that a member can choose to propose

Offer int Here the member can describe additional things he can offer, this can be translated

MaxGuest? int '

MaxLenghtOfStay? int Max lenght of stay, this is a text field that can be translated

Organizations int Here the member can list in text the organization he belongs to, this can be translated

Restrictions set Standard usual restriction, the member can choose several of them

OtherRestrictions? int Additional non usual restriction that the member can enter as text, this can be translated

bday int day of birth for the member (not mandatory)

bmonth int lonth of birth for the member (not mandatory)

byear int year of birth for the member (not mandatory)

updated timestamp last time the profile was updated

created timestamp '

LastLogin? timestamp last time the member login

SecurityFlag? int not yet use

Quality set Specific quality flags (several of them possible at the same time) which will be used to compute a "member quality" (big discussion in pespective !)

ProfileSummary? int Very important field, he the member can fill as text a summary description of her/himself. This information will be used for short summary of profile on search page results for example. This will be translatable

Occupation int Occupation of the member , free text the member can enter, it will be translatable

CounterGuests? int Counter of guest (for performance when displaying profile)

CounterHosts? int Counter of hosts (for performance when displaying profile)

CounterTrusts? int Counter of trusts(for performance when displaying profile)

PassWord? varchar The password of the member (encrypted of course), this is typically a field of the users table

Status enum Status of the member (if just subscribed, mail confimed or not, accepted, etc) the usual being "Active")

Gender enum The gender of the member

HideGender? enum '

GenderOfGuest? enum The prfered gender for guest

MotivationForHospitality? int Allow member to describe why he has subscribe to this web site(can be translated)

HideBirthDate? enum wether the member want's to hide his birthdate or not

BirthDate? date birthdate of the member

AdressHidden? enum Wether the first address of a member is hidden

WebSite? tinytext optional website of the member

chat_SKYPE tinytext Optionnal SKYPE address

chat_ICQ tinytext Optionnal ICQ address

chat_AOL tinytext Optionnal AOL address

chat_MSN tinytext Optionnal MSN address

chat_YAHOO tinytext Optionnal YAHOO address

chat_Others tinytext Optionnal others Chat address

IdCity? int temporary field for geonames conversion

FutureTrips? int This is a translatable word for when a member want to describe his furture trips

OldTrips? int This is a translatable sentence where a member can describe his past trips

LogCount? int This is the counter of number of time a member logged in

Hobbies int '

Books int '

Music int '

PastTrips? int '

PlannedTrips? int '

PleaseBring? int '

OfferGuests? int '

OfferHosts? int '

PublicTransport? int '

Movies int '

chat_GOOGLE int '

countries

built from geonames countryInfo.txt

isoalpha2 varchar '

isoalpha3 varchar '

isonumeric int '

fipscode varchar '

Name varchar '

capital varchar '

areaInSqKm int '

population int '

continent varchar '

languages varchar '

id int '

regionopen tinyint '

countadmin1 int '

NbMembers? int Number of active members in this country

donations

This is the table wher e the history of donation is kept

id int '

IdMember? int Id of the member (if any)

Email tinytext email used by the member if any

StatusPrivate? enum '

created timestamp when the donation occurs

Amount decimal amount of money

Money varchar $ euros ...

IdCountry? int country where the member was at donation time

namegiven text name given by the user (if any)

referencepaypal text paypal reference

membercomment text comment of the member if any

SystemComment? text system comment

ewiki

'

pagename varchar '

version int '

flags int '

content mediumtext '

author varchar '

created int '

lastmodified int '

refs mediumtext '

meta mediumtext '

hits int '

faq

This is the Faq table, all composed with translatable questi

id int '

QandA varchar The question/answer (associated to a word) produced as FaqQ_xxx for question and FaqA_xxxx for answer

updated timestamp '

created timestamp '

Active enum wethere the current faq is to show to member or not

SortOrder? int The sort order of teh Faq (0 appearring first)

IdCategory? int Category the Faq refer to

PageTitle? varchar Words to be associated with the page title

faqcategories

This is the list of categories for Faq

id int '

Description varchar Description of the category (this is a word)

SortOrder? int Sort order of teh category (0 the first)

Type enum Visibility of this category for logged member or not

updated timestamp '

created timestamp '

feedbackcategories

The different available categories for members feedbacks

id int '

Name tinytext The name of the category (this is a word)

CategoryDescription? tinytext The description of the feedback category (this is a word)

created timestamp '

EmailToNotify? text The list of the email to notify in case a feedback is sent in this category, these are dor comma separated

IdVolunteer? int Default volunteer in charge of this feedback

feedbacks

feedbacks provided by the members

id int '

updated timestamp '

created timestamp '

IdMember? int '

Discussion text '

IdFeedbackCategory? int category of the feedback

IdVolunteer? int The volunteer in charge of the next action for this feedback

Status enum this is the status of the feedback

IdLanguage? int Language of the member when the feedback was entered

flags

List of possible Fags for members

id int '

created timestamp '

Name tinytext '

Description text '

flagsmembers

The different flags for different members

id int '

IdMember? int '

IdFlag? int '

Level int '

Scope tinytext '

Comment text '

updated timestamp '

created timestamp '

forums_posts

InnoDB free: 12288 kB

postid int '

threadid mediumint '

authorid int '

IdWriter? int This is the member who write the post, this is th index to use to retrieve the member data in Members table

create_time datetime '

message text '

last_edittime datetime '

last_editorid int '

edit_count tinyint '

forums_tags

InnoDB free: 12288 kB

tagid int '

tag varchar '

tag_description varchar '

tag_position tinyint '

counter int '

forums_threads

InnoDB free: 12288 kB

threadid mediumint '

title varchar '

first_postid int '

last_postid int '

replies smallint '

views mediumint '

geonameid int '

admincode char '

countrycode char '

continent enum '

tag1 int '

tag2 int '

tag3 int '

tag4 int '

tag5 int '

gallery

InnoDB free: 12288 kB

id int '

user_id_foreign int '

flags blob '

title varchar '

text mediumtext '

gallery_items

InnoDB free: 12288 kB

id int '

user_id_foreign int '

file varchar '

original varchar '

flags blob '

mimetype varchar '

width int '

height int '

title varchar '

created datetime '

gallery_items_seq

'

id int '

gallery_items_to_gallery

InnoDB free: 12288 kB

item_id_foreign int '

gallery_id_foreign int '

gallery_seq

'

id int '

geonames_admincodes

InnoDB free: 12288 kB

code char '

country_code char '

admin_code char '

name varchar '

geonames_cache

InnoDB free: 12288 kB

geonameid int '

latitude double '

longitude double '

name varchar '

population int '

fk_countrycode char '

fk_admincode char '

geonames_countries

InnoDB free: 12288 kB

iso_alpha2 varchar '

name varchar '

continent enum '

languages varchar '

groups

Available group for subscription.

id int '

HasMembers? enum Specify wether this group can have member or not

Name varchar Name of the group (this is a word)

Type enum Define if the group is and PublicGroup? or a NeedAcceptance? group

created timestamp '

NbChilds? int The number of childs this group has

Picture text this a link to a picture associated with the group

MoreInfo? text this is a link to a forum page or a wiki page associated with the group

groupshierarchy

group hierarchy, one group can be under several other groups

id int '

IdGroupParent? int Parent Group

IdGroupChild? int Child Group

updated timestamp '

created timestamp '

groupsmessages

Here are stored the messages sent to Groups

id int '

created timestamp '

Title text message title

Message text message text

IdSender? int cid of the sender

IdGroup? int concerned group

guestsonline

Table of last guest online, it is purged periodically

IpGuest? int ip address of the user who is online

updated timestamp last time the member was active

appearance varchar What to show about the member this can be a html thing

lastactivity varchar Last url the member call

Status varchar a copy of the status of the member (sometime he can be ActiveHidden?)

hcvol_config

This table is the reference for configuring HCVol

key varchar This is the line which will be appended in hcvol_config.php by the AdminPanel? tool

value text This comment will be appended to php code line, it helps to understand

comment text '

updated timestamp '

intermembertranslations

Allow to define which member can translate other member prof

id int '

IdTranslator? int Id of the member who can translate

IdMember? int Id of the member who's profile can be translated

updated timestamp '

IdLanguage? int id of the language the translator can translate in

languages

This table is the list of available language

id int id of the language

EnglishName? tinytext '

Name tinytext '

ShortCode? char '

WordCode? tinytext The code which link to the words table

FlagSortCriteria? int The criteria for showing the flags first

logs

Logs of volunteers action and debugging tool

id int '

IdMember? int '

Str text '

Type tinytext '

created timestamp '

IpAddress? int ip address of the use at rhe time the log was inserted

DebugTracking? enum '

members

Members data table

id int IdMember?

Username varchar This is the username of the member its a unique field (shared with the user table)

ChangedId? int Used in case the member had a Username renamed (CurrentId? is then the id of the members entry to use)

Email int this is the main (verified at signup) mail of the member it is Crypted

HomePhoneNumber? int This is the phone number of the member its a field the member can choose to display or not , it is Crypted

CellPhoneNumber? int This is the cell phone number of the member its a field the member can choose to display or not , it is Crypted

WorkPhoneNumber? int This is the work phone number of the member its a field the member can choose to display or not , it is Crypted

SecEmail? int This is an eventual second email for contacting the member it is crypted

FirstName? int This is the first name of the member, the member can choose to hide it or not, it is crypted

SecondName? int This is the first name of the member, the member can choose to hide it or not, it is crypted

LastName? int This is the lastname (family name) of the member, the member can choose to hide it or not, it is crypted

Accomodation enum Here the member can state if he can offer accomodation and which kind

AdditionalAccomodationInfo? int Here the member can enter as text more precision about his accomodation possibilities;, this is can be translated

ILiveWith int Here the member can describe has text the people who he live with, this can be translated

IdentityCheckLevel? tinyint Futur Use, will define how far the identity of the member can be certified

InformationToGuest? int Here the member can enter specific information that may be of general interest for his guest, this can be translated

TypicOffer? set Other typical offer that a member can choose to propose

Offer int Here the member can describe additional things he can offer, this can be translated

MaxGuest? int '

MaxLenghtOfStay? int Max lenght of stay, this is a text field that can be translated

Organizations int Here the member can list in text the organization he belongs to, this can be translated

Restrictions set Standard usual restriction, the member can choose several of them

OtherRestrictions? int Additional non usual restriction that the member can enter as text, this can be translated

bday int day of birth for the member (not mandatory)

bmonth int lonth of birth for the member (not mandatory)

byear int year of birth for the member (not mandatory)

updated timestamp last time the profile was updated

created timestamp '

LastLogin? timestamp last time the member login

SecurityFlag? int not yet use

Quality set Specific quality flags (several of them possible at the same time) which will be used to compute a "member quality" (big discussion in pespective !)

ProfileSummary? int Very important field, he the member can fill as text a summary description of her/himself. This information will be used for short summary of profile on search page results for example. This will be translatable

Occupation int Occupation of the member , free text the member can enter, it will be translatable

CounterGuests? int Counter of guest (for performance when displaying profile)

CounterHosts? int Counter of hosts (for performance when displaying profile)

CounterTrusts? int Counter of trusts(for performance when displaying profile)

PassWord? varchar The password of the member (encrypted of course), this is typically a field of the users table

Status enum Status of the member (if just subscribed, mail confimed or not, accepted, etc) the usual being "Active")

Gender enum The gender of the member

HideGender? enum '

GenderOfGuest? enum The prfered gender for guest

MotivationForHospitality? int Allow member to describe why he has subscribe to this web site(can be translated)

HideBirthDate? enum wether the member want's to hide his birthdate or not

BirthDate? date birthdate of the member

AdressHidden? enum Wether the first address of a member is hidden

WebSite? tinytext optional website of the member

chat_SKYPE tinytext Optionnal SKYPE address

chat_ICQ tinytext Optionnal ICQ address

chat_AOL tinytext Optionnal AOL address

chat_MSN tinytext Optionnal MSN address

chat_YAHOO tinytext Optionnal YAHOO address

chat_Others tinytext Optionnal others Chat address

IdCity? int temporary field for geonames conversion

FutureTrips? int This is a translatable word for when a member want to describe his furture trips

OldTrips? int This is a translatable sentence where a member can describe his past trips

LogCount? int This is the counter of number of time a member logged in

Hobbies int '

Books int '

Music int '

PastTrips? int '

PlannedTrips? int '

PleaseBring? int '

OfferGuests? int '

OfferHosts? int '

PublicTransport? int '

Movies int '

chat_GOOGLE int '

memberscopy

Members data table

id int IdMember?

Username varchar This is the username of the member its a unique field (shared with the user table)

ChangedId? int Used in case the member had a Username renamed (CurrentId? is then the id of the members entry to use)

Email int this is the main (verified at signup) mail of the member it is Crypted

HomePhoneNumber? int This is the phone number of the member its a field the member can choose to display or not , it is Crypted

CellPhoneNumber? int This is the cell phone number of the member its a field the member can choose to display or not , it is Crypted

WorkPhoneNumber? int This is the work phone number of the member its a field the member can choose to display or not , it is Crypted

SecEmail? int This is an eventual second email for contacting the member it is crypted

FirstName? int This is the first name of the member, the member can choose to hide it or not, it is crypted

SecondName? int This is the first name of the member, the member can choose to hide it or not, it is crypted

LastName? int This is the lastname (family name) of the member, the member can choose to hide it or not, it is crypted

Accomodation enum Here the member can state if he can offer accomodation and which kind

AdditionalAccomodationInfo? int Here the member can enter as text more precision about his accomodation possibilities;, this is can be translated

ILiveWith int Here the member can describe has text the people who he live with, this can be translated

IdentityCheckLevel? tinyint Futur Use, will define how far the identity of the member can be certified

InformationToGuest? int Here the member can enter specific information that may be of general interest for his guest, this can be translated

TypicOffer? set Other typical offer that a member can choose to propose

Offer int Here the member can describe additional things he can offer, this can be translated

MaxGuest? int '

MaxLenghtOfStay? int Max lenght of stay, this is a text field that can be translated

Organizations int Here the member can list in text the organization he belongs to, this can be translated

Restrictions set Standard usual restriction, the member can choose several of them

OtherRestrictions? int Additional non usual restriction that the member can enter as text, this can be translated

bday int day of birth for the member (not mandatory)

bmonth int lonth of birth for the member (not mandatory)

byear int year of birth for the member (not mandatory)

updated timestamp last time the profile was updated

created timestamp '

LastLogin? timestamp last time the member login

SecurityFlag? int not yet use

Quality set Specific quality flags (several of them possible at the same time) which will be used to compute a "member quality" (big discussion in pespective !)

ProfileSummary? int Very important field, he the member can fill as text a summary description of her/himself. This information will be used for short summary of profile on search page results for example. This will be translatable

Occupation int Occupation of the member , free text the member can enter, it will be translatable

CounterGuests? int Counter of guest (for performance when displaying profile)

CounterHosts? int Counter of hosts (for performance when displaying profile)

CounterTrusts? int Counter of trusts(for performance when displaying profile)

PassWord? varchar The password of the member (encrypted of course), this is typically a field of the users table

Status enum Status of the member (if just subscribed, mail confimed or not, accepted, etc) the usual being "Active")

Gender enum The gender of the member

HideGender? enum '

GenderOfGuest? enum The prfered gender for guest

MotivationForHospitality? int Allow member to describe why he has subscribe to this web site(can be translated)

HideBirthDate? enum wether the member want's to hide his birthdate or not

BirthDate? date birthdate of the member

AdressHidden? enum Wether the first address of a member is hidden

WebSite? tinytext optional website of the member

chat_SKYPE tinytext Optionnal SKYPE address

chat_ICQ tinytext Optionnal ICQ address

chat_AOL tinytext Optionnal AOL address

chat_MSN tinytext Optionnal MSN address

chat_YAHOO tinytext Optionnal YAHOO address

chat_Others tinytext Optionnal others Chat address

IdCity? int temporary field for geonames conversion

FutureTrips? int This is a translatable word for when a member want to describe his furture trips

OldTrips? int This is a translatable sentence where a member can describe his past trips

LogCount? int This is the counter of number of time a member logged in

Hobbies int '

Books int '

Music int '

PastTrips? int '

PlannedTrips? int '

PleaseBring? int '

OfferGuests? int '

OfferHosts? int '

PublicTransport? int '

Movies int '

chat_GOOGLE int '

memberscounters

This is redudant table (can be rebuilt)

IdMember? int Id of the member

NbGoodComment? int Number of good comment the member has

NbComment? int Number of comments the member has

NbSentMessages? int Nb of messages the member has sent

NbSendMessageRead? int Nb of messages the member has ent and the receiver has read

NbSentSpam? int Nb of messages the member has sent and that have been marked as spam

updated timestamp When the record was updated

NbLogged? int Number of time the member has logged

membersgroups

Describe which members have subscribed to which group. The m

id int '

updated timestamp '

created timestamp '

Comment int Comment filled by the member

IdMember? int Id of the concerned member

IdGroup? int group the member belong to

Status enum status of appliance some group need an appliance

IacceptMassMailFromThisGroup? enum Wether the member of this group accept massmail from this group

CanSendGroupMessage? enum Wether this member is authorize to send message to this group

memberslanguageslevel

Level of the member in known languages

id int '

updated timestamp '

created timestamp '

IdMember? int concerned member

IdLanguage? int concerned language

Level enum level in the language

membersphotos

Photos for members

id int '

FilePath? tinytext path where the photo is stored, can be a local server path or some www place

IdMember? int '

SortOrder? tinyint Sort order of the picture (0 teh first one which will be displayed,1 the second, etc)

updated timestamp '

created timestamp '

Comment int translatable comment associated with the picture

memberspreferences

The preferences for each members

id int '

IdMember? int Member who the preference belong to

IdPreference? int preference which is concerned by ythis record

Value text Current value that the member has choose for his preference

updated timestamp '

created timestamp '

memberspublicprofiles

List all the members who have a public profile

id int '

IdMember? int '

created timestamp '

type enum Specify the type of public profile (like for example if it is a one to propose has a sample)

memberstrads

Sentences translatable for members

id int '

IdLanguage? int refer to the langauge the sentence is supposed to be translated in. Default to english

IdOwner? int id of the member who own the translation

IdTrad? int Id of the translation as it is refered in the source table (the one needed a translatable sentence)

IdTranslator? int Id of the authorized translator

updated timestamp '

created timestamp '

Type enum '

Sentence text Sentence translated

IdRecord? int Security redudancy : This is the id of the record for the corresponding TableColumn?

TableColumn? varchar Security redudancy : This define the Table and the column wich is the pointer to the current record

message

InnoDB free: 12288 kB

message_id int '

sender_id_foreign int '

recipients varchar comma separated user_ids

subject varchar '

text text '

created datetime '

refcount tinyint tracks how many users have this msg in their in/outbox, will be deleted if it reaches 0

messages

Here are stored all the messages sent to members. An archive

id int '

updated timestamp '

created timestamp When the message was first sent

DateSent? timestamp '

DeleteRequest? set Mark who has ask to delete the message (and thus will not be able to see it anymore) both values can coexist

IdParent? int specific for chained messages (when someone reply to a previous message)

IdReceiver? int Id of the receiver

IdSender? int Id of the sender

IdentityInformation? text Passport num or id document num that the sender can add with the message if he wants to reassure the guy he contact. Note that this field will need to be erased periodically (after 40 days ?)

SendConfirmation? enum wether a confirmation must be sent to the Sende when messahe is delivered

SpamInfo? set Spam information, to be filled by the automatic tools for spam filtering

Status enum Status for sending attempts

Message text text of the message

InFolder? enum The folder where the message is to be stored, note that Comment, Broadcast, evaluate, meeting will be sent thru notifications

WhenFirstRead? timestamp When the receiver read the message on the site for the first time

IdChecker? int Id of the eventual checker (if manual check occured)

IdTriggerer? int Id of the triggerer (may be 0 if mailbot)

JoinMemberPict? enum Wethher the picture of the send is to be joined

CheckerComment? text Comment from the checker or the autocheck tools

mod_user_apps

InnoDB free: 12288 kB

id int '

name varchar '

mod_user_apps_seq

'

id int '

mod_user_auth

InnoDB free: 12288 kB

id int '

name varchar '

mod_user_authgroups

InnoDB free: 12288 kB

id int '

name varchar '

mod_user_authrights

InnoDB free: 12288 kB

auth_id int '

right_id int '

mod_user_auth_seq

'

id int '

mod_user_groupauth

InnoDB free: 12288 kB

auth_id int '

group_id int '

mod_user_grouprights

InnoDB free: 12288 kB

group_id int '

right_id int '

mod_user_implications

InnoDB free: 12288 kB

right_id int '

implies_id int '

mod_user_rights

InnoDB free: 12288 kB

id int '

app_id int '

name varchar '

has_implied int '

level int '

mod_user_rights_seq

'

id int '

mycontacts

Contacts table filled by the member about other members

id int primary key

Idmember int member who own the contact

IdContact? int id of the contact

updated timestamp '

Category tinytext category of the contact (can be used as a sort otion)

Comment text comment of the member about contact

created timestamp '

oldvisits

This table contains the archived visits, it is built using r

id int '

IdMember? int id of the visited profile

IdVisitor? int id of the visitor

created timestamp when the visit occured

online

Table of last online members, it is purged periodically

IdMember? int member who is online

updated timestamp last time the member was active

appearance varchar What to show about the member this can be a html thing

lastactivity varchar Last url the member call

Status varchar a copy of the status of the member (sometime he can be ActiveHidden?)

params

This is the table with generic paramaters

id int '

updated timestamp '

recordonline int This parameter contains the number max of members seen online

pendingmandatory

Contain the data when a member ask to update his mandatory d

id int '

IdMember? int id of the concerned member

created timestamp '

FirstName? text new firstname of the member

SecondName? text new secondname of the member

LastName? text new lastname of the member

HouseNumber? text new housemember for the member

StreetName? text new street name for the member

Zip text new zip for the member

IdCity? int New id city for the member

Comment text A comment the member can set

Status enum Status of the request

IdAddress? int Id of the address to update (member can have several)

posts_notificationqueue

This table is to be used to tell mailbot who to notify about

id int '

Status enum wether the notification was sent or not

IdMember? int The member to notify

IdPost? int The post to notify about

updated timestamp '

created timestamp '

Type enum the type of the notification

IdSubscription? int Id of the subsrciption (if any) to allow efficient unsubscribe procedure

TableSubscription? varchar The name of the subscription table this notification is caused by

preferences

Contain the list of the available preferences with the corre

id int '

codeName varchar Word code of the preference to find a name to display for members

codeDescription varchar word code for description (translatable) of the preferences to explain memebr what it is use for

Description text Description of the preferences (to be used for volunteers)

created timestamp '

DefaultValue? tinytext '

PossibleValues? tinytext '

EvalString? text eval string which will be used to build layout choice

Status enum This is the Status/Category? of preferences

previousversion

'

id int '

IdMember? int '

TableName? tinytext '

IdInTable? int '

Type enum '

XmlOldVersion? text '

created timestamp '

profilesvisits

Table use for visits on profiles

IdMember? int id of the visited profile

IdVisitor? int id of the visitor

created timestamp date of first visit

updated timestamp date of last visit

recentvisits

This table contains the recent visits

id int '

IdMember? int id of the visited profile

IdVisitor? int id of the visitor

created timestamp when the visit occured

regions

'

id int '

Name varchar '

ansiname varchar '

OtherNames? varchar '

latitude double '

longitude double '

feature_class char '

feature_code varchar '

country_code char '

cc2 varchar '

admin1_code varchar '

population int '

elevation int '

gtopo30 int '

timezone varchar '

modification_date date '

IdCountry? int '

citiesopen tinyint '

NbCities? tinyint '

NbMembers? int The number of members in this region (this is a redudancy)

rights

List of possible Rights

id int '

created timestamp '

Name tinytext '

Description text '

rightsvolunteers

The different rights for different members

id int '

IdMember? int '

IdRight? int '

Level int '

Scope tinytext '

Comment text '

updated timestamp '

created timestamp '

specialrelations

This table describe special relation between members if the

id int '

updated timestamp '

Type set Type of the relation

Comment int Comment about the relation this is a translatable field

created timestamp '

IdOwner? int owner of the relation

IdRelation? int other member in the relation

Confirmed enum relation will be confirmed if both member say so

sqlforvolunteers

this is a table for queries made for helping volunteers

id int '

Name text name of the query

Query text content of the query

updated timestamp '

stats

This is the daily statistics page

id int '

created timestamp date of the stat

NbActiveMembers? int Number of active members

NbMessageSent? int Nb message sent

NbMessageRead? int Nb Messages read

NbMemberWithOneTrust? int Nb Member with at least one trust

NbMemberWhoLoggedToday? int Nb members who logged today

tags

This table is the table of tag, it will be use by forum, gro

id int id of the tag

Name int Name of the tag ( this is in members trads or in words depend on the category)

Description int description of the tag purpose( this is in members trads or in words depend on the category)

Type enum Type of the tag

Position int Position of the tag

created timestamp when the tag was created

tags_threads

link between tags and threads

IdTag? int The tag

IdThread? int The thread

tantable

'

Username varchar '

OnePad? bigint '

trip

InnoDB free: 12288 kB

trip_id int '

trip_options blob '

trip_touched datetime '

user_id_foreign int '

trip_data

'

trip_id int '

edited datetime '

trip_name varchar '

trip_text mediumtext '

trip_descr longtext '

trip_seq

'

id int '

trip_to_gallery

InnoDB free: 12288 kB

trip_id_foreign int '

gallery_id_foreign int '

user

InnoDB free: 12288 kB

id int '

auth_id int '

handle varchar '

email varchar '

pw text '

active int '

lastlogin datetime '

location int '

userfrombewelcome

InnoDB free: 12288 kB

id int '

auth_id int '

handle varchar '

email varchar '

pw text '

active int '

lastlogin datetime '

location int '

user_bw_old

Users and global privileges

id int this id is the basic id of the user, it is the same as the members.id

auth_id int '

handle varchar '

email varchar This email field will be kept to blank (because it is crypted elsewhere)

pw text '

active int '

lastlogin datetime to keep updated at member login

location int It is assumed that the location is the same thisng as the members.IdCity?

File_priv enum '

Grant_priv enum '

References_priv enum '

Index_priv enum '

Alter_priv enum '

Show_db_priv enum '

Super_priv enum '

Create_tmp_table_priv enum '

Lock_tables_priv enum '

Execute_priv enum '

Repl_slave_priv enum '

Repl_client_priv enum '

Create_view_priv enum '

Show_view_priv enum '

Create_routine_priv enum '

Alter_routine_priv enum '

Create_user_priv enum '

ssl_type enum '

ssl_cipher blob '

x509_issuer blob '

x509_subject blob '

max_questions int '

max_updates int '

max_connections int '

max_user_connections int '

user_friends

InnoDB free: 12288 kB

user_id_foreign int '

user_id_foreign_friend int '

user_inbox

InnoDB free: 12288 kB

user_id_foreign int '

message_id_foreign int '

seen tinyint '

replied tinyint 1 if user sent a reply

user_outbox

InnoDB free: 12288 kB

user_id_foreign int '

message_id_foreign int '

user_seq

'

id int '

user_settings

InnoDB free: 12288 kB

user_id int '

setting varchar '

value text '

valueint int '

valuedate datetime '

words

Table of language dependent entities

id int '

code varchar '

ShortCode? char This is a redundancy , do not use

Sentence text '

updated timestamp '

donottranslate enum '

IdLanguage? int The link with the IdLanguage? table

Description text This field is used to describe the usage of the current Sentence. It is only fill by programmers and for english language

IdMember? int member who created or updated the word

created timestamp '