Introduction to the database
Naming convention
This is (for now) defined by JeanYves who is has made most of the table creation definition since now + consider several of the Andreas comments.
The TB tables don't follow this rules, and some change are also to be made on some BW tables. Let say that news tables must follow these rules
comments
Always add a comment about the role of a new created table
Always add a comment about the role of a column
table names
A good table name use _ to separate the words defining the role of the table
example : forums_posts
default columns
The default columns name will help to provide future standards administration tools
The following field must be defined in each new table :
id : primary key, unique auto_increment created : a timestamp, when the record was created updated : a timestamp, when the record was created, this must be set to auto
foreign key
Foreign key must start by Id
example : IdMember, IdWriter
It is good to make a good choice with the adjective after the !Id.
Example: message.IdMember is unclear, messages.IdReceiver and messages.IdSender are better since it clarify the role of the column
other columns
Just use CamelCase notation, without underscore and don't forget to had a specific comment. Make it clear when a column is a redudancy and when it is not the source of the data.
The PARAM table
The PARAM table should in the long run contain all the parameter of the environment.
It contains only one record which is reloaded at each page refresh (even each Ajax refresh (this is a todo)).
It will be used to control dynamically the behavior of the BeWelcome platform
Tables splitted in different mysql databases
The database is organized in a way that it can be splitted in several sub databases where are gathered groups of tables.
Tis make the backup, the rights and the future server replication easier to use.
Typically, in production we have :
- BW_MAIN : the database with the main tables (this one is implicit)
- BW_ARCH : the database with the archives and the logs table
- BW_CRYPTED : the database with the encrypted data
- BW_GEONAME : the (future) database with the geoname cache
- BW_FORUM : the (future) database with the forum data
This is achieved using a dedicated $_SYSHCVOL[] global variable ( values of $_SYSHCVOL[ARCH_DB / CRYPT_DB] are setup in bw\inc\config.inc.php )
In test environment, since all these values are left empty, all the tables are in the same database.
To query some data in one of these database which are not by default in BW_MAIN
