Guidelines for data modelling

Guidelines for data modelling

Don't follow these if you think you know better. These are purely based on what I am learning based on my [possible] mistakes.

why? This will allow me to do a quick text/grep search in my solution space to see where I have used it and having a distinct prefix will help me in this.

Same argument. Although I am suspicious to spoil the name space. But again this may be a necessary evil for better maintenance. 


f_dateCreated
f_dateCreatedBy
f_dateUpdated
f_dateUpdatedBy
f_ownerUserId

make last updated by automatic from the database


t_tableName
tbl_tableName //may be

f_fieldName
v_viewName

A document on template servers, how to codegen sql server artifacts

for now I have kept the code generation notes for SAT application here.

Note: Move this later to a folder by iteself, perhaps still a private one.

There is a differing opinion on prefixing table names with t_ etc.


//usual tables that change frequently
//normal tables
t_table_name

//Look up/Reference tables
t_salutation_ref (Mr, Ms, Mrs etc)

//master tables
//These are tables that don't change often
tm_table_name

Beware AnsiSQL has table name and field name length limitations

Search for: Beware AnsiSQL has table name and field name length limitations

VSCode has a plugin for SQL Server stored proces etc. It is here

1. is the name correct?

2. what does it do?

3. Does it have an identity column?

4. is there a primary key?

5. are there unique constraints

6. are there foreign keys?

7. Are there refs and are they documented at the top?

8. Are foreign keys documented at the top?

Code sets that are small: for example "solar vs wind" as opposed "1 vs 2". Because it is easy to make mistakes or xref them with joins.

Generating plant names: for example "plant1 and plant2" vs "10, 15". Because it is hard to keep the "10 and 15" consistent as you update the database with new plants.