DBIC naming conventions

From Koha Wiki
Jump to navigation Jump to search

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:

  1. All words separated by underscores in both tables and columns
  2. All table names should be plural
  3. Primary key is 'id'
  4. 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