SQL Database Standards

Table Basics

• Table Names - Should alway be in the same meaning with the referred object/item.

Example: Create a table that stores the basic information of a person.
So, the table name should be named as Persons.

• Field Names - Should always be in the same meaning with the referred object/item.

Example: Create a table that stores the basic information of a person.
Informations: Given Name, Family Name, Birth Date, Home Address
So, the table fields must be like this:

1. personID (Primary Key)
2. firstname (Given Name)
3. lastname (Family Name)
4. birthDate (Birth Date)
5. homeAddress (Home Address)

• CRUD Methods (Create, Read, Update, and Delete) - All SQL queries should be in the Stored Procedures. However, if the query involves two or more tables and is used not only for Reading, you can create another Stored Procedure provided it has proper naming conventions (See: Stored Procedures Section). If the query involves two or more tables and is only used for Reading (SELECT Command) it should be created as Views and should by named as format Vw_[database name or initials]_[table name].

Example: Create a Views for Persons Table in SocietyInformation database. So, the Views name would be: Vw_SocietyInformation_Persons

Stored Procedures

• Creation - there should only be one stored procedure for each table.
  

• Naming - Should always be in the same name as the table referred plus "Sp_" (Stands for Stored Procedure) and the database name or initials , as the first letters, followed by the underscore symbol. The format should be like this: Sp_[database name or initials]_[table name]

Example: Create a stored procedure for Persons Table in SocietyInformation database.
So, the Store Procedure name would be: Sp_SocietyInformation_Persons

However, if the Store Procedure involves two or more tables and is used not only for Reading, the Stored Procedure's last section name should be the name of the process made. The format should be like this: SP_[database name or initials]_[process name]

Example: Create a stored procedure for Adding, Viewing, Editing, and Deleting a Family Tree.
So, the Store Procedure name would be: Sp_SocietyInformation_FamilyTree

• Variable Names - Stored Procedure must always have @option variable, for selecting the section, and @ReturnValue, as the result return value (commonly used to get SCOPE_IDENTITY()) and will be the Reserved VariableNames. Other variables must be in the same meaning of its field name. Use synonyms for the variable whose field name is the same as the two Reserved Variable Names.
  

• Sections - Stored Procedures must be divided into sections for every CRUD method involved. Sections are selected by @option variable. These sections are as follows:
• Creating (INSERT Command) - @option = 1 to 200
• Reading (SELECT Command) - @option = 601 up
• Updating (UPDATE Command) - @option = 201 to 400
• Deleting (DELETE Command) - @option = 401 to 600

Note: Reading Section has 601 up sections because SELECT Command is the most used Command in SQL.


---------------------------------------------------------------------------------------------------

SAMPLE

---------------------------------------------------------------------------------------------------

Situation:

Create a database so that a company can see its old and new employees. The database name is CompanyInformation.

Tables:

• Persons
• personID (PK)
• firstname
• lastname
• homeAddress
• Company
• companyID (PK)
• companyName
• address
• businessType
  
• Employments
• employmentID (PK)
• employmentDate
• position
  
• companyID (FK)
  
• personID (FK)

Stored Procedures:

• Sp_CompanyInformation_Persons
• Variables
• @option
• @ReturnValue
• @personID
  
• @firstname
• @lastname
• @homeAddress
• Query

IF @option = 1
BEGIN
INSERT INTO Persons(firstname, lastname, homeAddress)
VALUES(@firstname, @lastname, homeAddress)

SET @ReturnValue = SCOPE_IDENTITY()
Return @ReturnValue
END

ELSE IF@option = 201
BEGIN
UPDATE Persons SET
firstname = @firstname,
lastname = @lastname,
homeAddress = @homeAddress
WHERE personID = @personID

SET @ReturnValue = @personID
Return @ReturnValue
END

ELSE IF @option = 401
BEGIN
DELETE Persons WHERE personID = @personID

SET @ReturnValue = @personID
Return @ReturnValue
END

ELSE IF @option = 601
BEGIN
SELECT * FROM Persons
END

• Sp_CompanyInformation_Company
• Variables
• @option
• @ReturnValue
• @companyID
  
• @companyName
• @address
• @businessType
• Query

IF @option = 1
BEGIN
INSERT INTO Company(companyName, address, businessType)
VALUES(@companyName, @address, @busineessType)

SET @ReturnValue = SCOPE_IDENTITY()
Return @ReturnValue
END

ELSE IF@option = 201
BEGIN
UPDATE Company SET
companyName = @companyName,
address = @address,
businessType = @businessType
WHERE companyID = @companyID

SET @ReturnValue = @companyID
Return @ReturnValue
END

ELSE IF @option = 401
BEGIN
DELETE Company WHERE companyID = @companyID

SET @ReturnValue = @companyID
Return @ReturnValue
END

ELSE IF @option = 601
BEGIN
SELECT * FROM Company
END

• Sp_CompanyInformation_Employment
• Variables
• @option
• @ReturnValue
• @employmentID
• @employmentDate
• @position
  
• @companyID
  
• @personID
• Query

IF @option = 1
BEGIN
INSERT INTO Employments(employmentDate, position, companyID, personID)
VALUES(@employmentDate, @position, @companyID, @personID)

SET @ReturnValue = SCOPE_IDENTITY()
Return @ReturnValue
END

ELSE IF@option = 201
BEGIN
UPDATE Employments SET
employmentDate = @employmentDate,
position = @position,
companyID = @companyID,
personID = @personID
WHERE employmentID = @employmentID

SET @ReturnValue = @employmentID
Return @ReturnValue
END

ELSE IF @option = 401
BEGIN
DELETE Employment WHERE employmentID = @employmentID

SET @ReturnValue = @employmentID
Return @ReturnValue
END

ELSE IF @option = 601
BEGIN
SELECT * FROM Employment
END