Synergetic database object naming conventions

Modified on Fri, 13 Mar at 12:48 AM

Clients can create database object with a 'u' prefix.

While the use of the naming conventions is recommended we do not (cannot) mandate this for user objects.

Schemas

Schema	Description
dbo	contains the non-financial records for the school including community records for all types of people and companies (names, addresses, phone numbers etc.), applications, student, alumni, academic information, etc.
finance	contains the core financial transaction records for the school. Separating these into a separate schema allows for different security permissions to easily be granted at the database level and it also allowed for schools to have different finance schemas for the different financial entities they operate, while sharing the one set of community information.
media	the repository for multimedia information. This includes school photos, archived school reports and debtor statements as well as any digital document stored against a record. Having this as a separate schema allows for different growth and backup strategies.
sif	sql objects to support the Synergetic specific SIF functionality
siform	sql objects to support the SIF Environment provider. These objects are defined by the ORM used in the SIF Object Provider (C#) and should only be changed in conjunction with changes to the Object Provider.
xsys	contains objects to support interfaces to external systems/entities. This includes code sets used by the external systems and the mappings back to their equivalent Synergetic values.
eventbroker	sql objects to support the Event Broker functionality - a notification component for (a)synchronous loosely coupled event handling.
preserved	used as a permanent archive of data when the underlying database structure changes. In the current schema, this information is combined with temporary changed data in the scratch schema. This separates the data that currently goes in to the scratch schema from that which should be stored permanently for historical verification, from data which is a temporary backup and can be cleared after an upgrade.
scratch	a temporary repository for backups of table data during table changes (usually during an upgrade)

Stored Procs

Prefix	Description
sp	legacy/older items that didn't have the convention used eg spStudentsSet (note the 'Set' implies a select dataset purpose)
crsp	Crystal Report stored procedure
Lookup	lu table/used for lu dropdowns
spc	create (not used much)
spd	delete
spe	external/export
spg	generate
spi	insert
spq	queue (service broker related)
sps	select
spt	transfer
spu	update
spx	external/relating to interfaces outside Synergetic

Views

There are some exceptions where tables have been abstracted as views, with the table being renamed with a 't' prefix.

For example, the Config table was recently refactored as a view against a tConfig table.

Prefix	Description
pv	program view (used by our applications internally and specific to their needs)
v	view

Tables

Prefix	Description
lu	lookup table
ref	ref table (stores internal system data like SynergyMeanings and external code sets)
t	table (used when we changed some areas to have user-based security, and a view replaced the original 'non-t' table name eg GeneralLedger)
wk	work table (used as temp staging area for some program logic and transactions)
xe	external export
xi	external import
xfre	transfer external (web service outbound)
xfri	transfer internal (web service inbound)

Indices

prefix	Description
PK_<ReferencedTableName>	Primary Key
AK_<ReferencedTableName>_<Column Name,,>	Covering index

Triggers

Triggers are referenced by a suffix rather than a prefix, with action on INSERT, UPDATE and DELETE referenced in that seniority

Suffix	Description
_ITR	after INSERT
_UTR	after UPDATE
_DTR	after DELETE
_IUTR	after INSERT or UPDATE
_IUDTR	after INSERT, UPDATE or DELETE
_IDTR	after INSERT or DELETE (rarely used)
_UDTR	after UPDATE or DELETE (rarely used)
_IO_ITR	instead of INSERT
_IO_UTR	instead of UPDATE
_IO_DTR	instead of DELETE
_IO_IUTR	instead of INSERT or UPDDATE
_IO_IUDTR	instead of INSERT, UPDATE or DELETE
_IO_IDTR	instead of INSERT or DELETE (rarely used)
_IO_UDTR	instead of UPDATE or DELETE (rarely used)
_DDLTR	ddl trigger (rarely used)

Constraints

Constraints are formatted as follows (prefix format follows SSMS studio templating - eg: you can Ctrl + Shift + M in SSMS)

Prefix	Description
DF_<Tablename>_<FieldName>	Default constraint
CK_<Table Name>_<Column Name,,>	Check constraint
FK_<ForeignTableName>_<ReferencedTableName>	Foreign Key - usually i only add foreign column as needed - eg: when referenced table name is referenced more than once by Foreign Table (in the case of ModifiedByID, CreatedByID - both are referencing Community)

Functions

Prefix	Description
fn	Function