Naming Conventions

Time to get closer to your scripts.

qss can offer you such a simple interface only, when it makes some assumptions. Not allowing to adapt these is one of the reasons, why qss can create such consistent scripts without a live-connection to the database.

Naming Conventions for Tables

• A primary key is named as the table with an ID suffix. So, the table CUSTOMER will have CUSTOMERID as primary key. I know about discussions about "data-redundancy" when you repeat the table name in the PK-name, but please read the next point, the foreign key definition, why I do this.
• A foreign key is named identically to the primary key. So, in the table ADDRESS, if you find a column CUSTOMERID, you know, it is a foreign key to the table CUSTOMER. This is the reason, why the PK is not named simply ID. This way, you can ensure, that the name of the FK will always match the name of PK, it points to.
• All primary keys have a sequence attached and are of type bigint
• All tables are generated, that:
  • The PK is the top-most column
  • Followed by all FKs
  • Followed by your custom data fields
  • Followed by The Five Standard Columns

Naming Conventions for Triggers

• A trigger is named TRG_<action>_<table>, where action describes the trigger event (see below) and table is the table name
• There are six possible actions:
  • BI - Before Insert
  • BU - Before Update
  • BD - Before Delete
  • AI - After Insert
  • AU - After Update
  • AD - After Delete
• So, the before-insert trigger for the CUSTOMER table is named TRG_BI_CUSTOMER

Naming Conventions for Stored Procedures - MSSQL

Imagine a table CUSTOMER with two foreign keys, one to ADDRESS and one to NOTE.\You will get these procedures:

Procedure	What it does
CustomerInsert	Create a customer
CustomerUpdate	Update a customer
CustomerDelete	Delete a customer
CustomerGetAll	Select the entire table
CustomerGetByCustomerID	Select by PK
CustomerGetByAddressID	Select all customers with a specific address
CustomerGetByNoteID	Select all customers with a specific note
CustomerGetByAddressIDNoteID	Select all customers with a specific address and note

Naming Conventions for Stored Procedures - MYSQL

Again, imagine the same table CUSTOMER with two foreign keys, one to ADDRESS and one to NOTE.\You will get these procedures:

Procedure	What it does
customer_insert	Create a customer
customer_update	Update a customer
customer_delete	Delete a customer
customer_getall	Select the entire table
customer_getby_customerid	Select by PK
customer_getby_addressid	Select all customers with a specific address
customer_getby_noteid	Select all customers with a specific note
customer_getby_addressid_noteid	Select all customers with a specific address and note

---

All these procedures are generated automatically for you. When it comes to foreign keys, qss will generate:

• One SP for each foreign key
• One SP for possible combination of foreign keys
  • with 2 foreign keys, this is 3 procedures (key1, key2, key1+2)
  • with 3 foreign keys, this is 7 procedures (key1, key2, key3, key1+2, key1+3, key2+3, key1+2+3)
  • ...and so on...

You see, it will save you a ton of time with a simple command line!