Back to DeveloperDocs
Database Naming Conventions
This is a proposal for naming database information for all Campware Products.
References:
- http://justinsomnia.org/writings/naming_conventions.html
- http://www.ss64.com/orasyntax/naming.html
Table names
- Use lowercase letters only, some databases do not support case sensitivity
- Use underscores between words
- Use the singular when naming tables (plural names of some words are hard to spell, e.g. referrers, indicies)
- Dont abbreviate the words
- Junction tables: concatenate the two table names together, with underscore in between
Field Names
- Lowercase letters only, some databases do not support case sensitivity
- Use underscores between words
- Any value that refers to a key: append "_id"
- Foriegn keys: prefix with "fk_"
- Date fields: prefix with "date_", or "time_"; or "seconds_", "minutes_" if you know the type of data
- Boolean: prefix with "is_", or "has_"
Functionality Conventions
- Each table should have a "last_modified" TIMESTAMP column as long as it makes sense.
- If you need a row creation time, the column name should be named "time_created"
- The unique ID column for the table should be called "id"
Example
(note: the "Article Index" table below is not the current article index table – it is a split of the current article table into two tables. By the time we implement this, the current ArticleIndex will no longer exist).
article_index Table
| Old Name | New Name |
| *** N/A *** | fk_article_id |
| IdPublication | fk_publication_id |
| NrIssue | fk_issue_id |
| NrSection | fk_section_id |
| Number | article_group_id |
| IdLanguage | fk_language_id |
article Table
| Old Name | New Name |
| *** N/A *** | id |
| Name | name |
| Type | type |
| IdUser | fk_user_created_by |
| OnFrontPage | is_on_front_page |
| OnSection | is_on_section_page |
| Published | is_published |
| PublishedDate | date_published |
| UploadDate | date_upload |
| Keywords | keywords |
| Public | is_public |
| IsIndexed | is_indexed |
| LockUser | fk_user_locked_by |
| LockTime | date_locked |
| ShortName | short_url |
| ArticleOrder | order |
| *** N/A *** | last_modified |
| *** N/A *** | time_created |
publication Table
| Old Name | New Name |
| Id | id |
| Name | name |
| IdDefaultLanguage | fk_language_id_default |
| PayTime | time_pay_interval |
| TimeUnit | time_unit |
| UnitCost | unit_cost |
| Currency | currency |
| TrialTime | time_trial |
| PaidTime | time_paid |
| IdDefaultAlias | fk_alias_default |
| IdURLType | fk_urltype |
| *** N/A *** | last_modified |
| *** N/A *** | time_created |
Sample Queries
Get the publication info with every article:
SELECT * FROM article_index, article, publication
WHERE article_index.article_id = article.id
AND article_index.fk_publication_id = publication.id
