DBIC naming conventions
Note: there is currently no consensus in the community on DB naming convention, this is a suggestion
Please see Coding_Guidelines#Database for more information on guidelines regarding databases
There are 4 rules to DBIC naming:
- All words separated by underscores in both tables and columns
- All table names should be plural
- Primary key is 'id'
- Foreign key is named after the table pointed to, only singular.
You are also highly encouraged not to use abbreviations.
For example, if we changed the aqinvoices table so that it used the DBIC conventions, the original
aqinvoices ( invoiceid , -- ID of the invoice, primary key invoicenumber , -- Name of invoice booksellerid , -- foreign key to aqbooksellers shipmentdate , -- date of shipment billingdate , -- date of billing closedate , -- invoice close date, NULL means the invoice is open shipmentcost , -- shipment cost shipmentcost_budgetid , -- foreign key to aqbudgets, link the shipment cost to a budget )
Note that the constraint for shipmentcost_budgetid shows that it is simply a foreign key on aqbudgets:
CONSTRAINT aqinvoices_fk_shipmentcost_budgetid FOREIGN KEY (shipmentcost_budgetid) REFERENCES aqbudgets (budget_id)
Would look like this, using the DBIC naming conventions:
invoices ( id , -- Primary key name , bookseller , -- Foreign key for booksellers table shipment_date , billing_date , close_date , shipment_cost , budget -- Foreign key for budgets table )