database schema
From SkyPHP
Contents |
Mandatory Fields
Every table must have the following fields:
- id (serial auto-increment)
- active (smallint)
- insert_time (timestamp)
- update_time (timestamp)
- mod__person_id (integer)
Table Naming Conventions
- related tables share a common prefix
- singular names
- all lowercase
- underscore to separate words
Field Naming Conventions
- all lowercase
- underscore to separate words
- foreign key references
- example:
- where person.id = person_address.person_id
- multiple foreign key references use double underscore
- examples:
- where person.id = auction.buyer__person_id
- where person.id = auction.seller__person_id
- status
- A = Active
- D = Draft
- P = Pending
- X = Trash
Glossary
- Keytable or Extended table (one-to-one)
- a person can be in a keytable only one time
- one-to-one table of foreign keys (where active = 1). stores the id values of a subset of records from another table
- Attachment table (one-to-many)
- a person can have multiple addresses
- multiple addresses are "attached" to a person
- Linking table (many-to-many)
- links a record from each of two tables using a third linking table
- a person can be "linked" to more than one client/company account
- client_person links client_id and person_id
