Database
1. Table Structure
- acl[link4]
- auth_token[link5]
- cache[link6]
- category[link7]
- category_assignment[link8]
- config[link9]
- external_link[link10]
- file[link11]
- file_link[link12]
- log[link13]
- menu[link14]
- page[link15]
- page_link[link16]
- referrer[link17]
- revision[link18]
- user[link19]
- user_setting[link20]
- usergroup[link21]
- usergroup_member[link22]
- watch[link23]
1.1. Access MySQL via command line
mysql -h 127.0.0.1 -P 3306 -u root -p <database>
mysql> DESCRIBE table_name;
1.2. Database Diagram
3 of 3 Files accessible from this page Dev / Release / R 6.1 / Database[link24]:
a_schematic_of_wackowiki_r6.1_database_structure.png[link25] | table structure of WackoWiki R6.1 database | 88.0 KiB | 17.08.2023 09:44 | |
a_schematic_of_wackowiki_r6.1_database_structure.svg[link26] | table structure of WackoWiki R6.1 database | 623.2 KiB | 17.08.2023 09:42 | |
wackowiki_6.1.mwb[link27] | MySQL Workbench 8.0 file for WackoWiki R6.1 | 27.3 KiB | 17.08.2023 09:42 |
-> MySQL Workbench[link28]
1.3. Database Script
1.4. Performance
Indexes- Ensure indexes on columns used in WHERE, ON, GROUP BY clauses
- Always ensure JOIN conditions are indexed (and have identical data types)
- Be careful of the column order
- If you don't pick a primary key (bad idea!), one will be created for you -> And, you have no control over the key
1.5. Naming
Use singular for table names, e.g. “page” not “pages”A table structure describes a specific entity
1.6. Proposed Database Schema Changes
- [link1] https://wackowiki.org/doc/Dev/Database
- [link2] https://wackowiki.org/doc/Dev/Database/Layout
- [link3] https://wackowiki.org/doc/Dev/Database/Normalization
- [link4] https://wackowiki.org/doc/Dev/Database/Layout/acl
- [link5] https://wackowiki.org/doc/Dev/Database/Layout/authtoken
- [link6] https://wackowiki.org/doc/Dev/Database/Layout/cache
- [link7] https://wackowiki.org/doc/Dev/Database/Layout/category
- [link8] https://wackowiki.org/doc/Dev/Database/Layout/categoryassignment
- [link9] https://wackowiki.org/doc/Dev/Database/Layout/config
- [link10] https://wackowiki.org/doc/Dev/Database/Layout/externallink
- [link11] https://wackowiki.org/doc/Dev/Database/Layout/files
- [link12] https://wackowiki.org/doc/Dev/Database/Layout/filelink
- [link13] https://wackowiki.org/doc/Dev/Database/Layout/log
- [link14] https://wackowiki.org/doc/Dev/Database/Layout/menu
- [link15] https://wackowiki.org/doc/Dev/Database/Layout/page
- [link16] https://wackowiki.org/doc/Dev/Database/Layout/pagelink
- [link17] https://wackowiki.org/doc/Dev/Database/Layout/referrer
- [link18] https://wackowiki.org/doc/Dev/Database/Layout/revision
- [link19] https://wackowiki.org/doc/Dev/Database/Layout/user
- [link20] https://wackowiki.org/doc/Dev/Database/Layout/usersetting
- [link21] https://wackowiki.org/doc/Dev/Database/Layout/usergroup
- [link22] https://wackowiki.org/doc/Dev/Database/Layout/usergroupmember
- [link23] https://wackowiki.org/doc/Dev/Database/Layout/watch
- [link24] https://wackowiki.org/doc/Dev/Release/R6.1/Database
- [link25] https://wackowiki.org/doc/Dev/Release/R6.1/Database/file?get=a_schematic_of_wackowiki_r6.1_database_structure.png
- [link26] https://wackowiki.org/doc/Dev/Release/R6.1/Database/file?get=a_schematic_of_wackowiki_r6.1_database_structure.svg
- [link27] https://wackowiki.org/doc/Dev/Release/R6.1/Database/file?get=wackowiki_6.1.mwb
- [link28] http://dev.mysql.com/workbench/
- [link29] https://wackowiki.org/doc/Dev/Release/R5.5/Database
- [link30] https://wackowiki.org/doc/Dev/Release/R6.0/Database