Guidelines for data modelling
satya - Monday, January 09, 2012 9:46:11 AM
These are just my initial thoughts...
Don't follow these if you think you know better. These are purely based on what I am learning based on my [possible] mistakes.
satya - Monday, January 09, 2012 9:47:29 AM
Name your columns with a prefix like f_colum1
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.
satya - Monday, January 09, 2012 9:56:14 AM
Name your tables with a prefix like t_table1
Same argument. Although I am suspicious to spoil the name space. But again this may be a necessary evil for better maintenance.
satya - Monday, January 09, 2012 9:58:07 AM
all tables have the following
f_dateCreated
f_dateCreatedBy
f_dateUpdated
f_dateUpdatedBy
f_ownerUserId
satya - Saturday, February 18, 2012 11:48:00 AM
make last updated by automatic from the database
make last updated by automatic from the database
satya - 12/23/2019, 5:02:17 PM
So ...
t_tableName
tbl_tableName //may be
f_fieldName
v_viewName
satya - 12/23/2019, 5:40:06 PM
A document on template servers, how to codegen sql server artifacts
A document on template servers, how to codegen sql server artifacts
satya - 12/23/2019, 5:41:31 PM
For specific example also see the private folder "Questions"
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.
satya - 12/28/2019, 1:16:50 PM
There is a differing opinion on prefixing table names with t_ etc.
There is a differing opinion on prefixing table names with t_ etc.
satya - 12/28/2019, 1:18:46 PM
If you were to prefix them....
//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
satya - 12/29/2019, 12:18:58 PM
Beware AnsiSQL has table name and field name length limitations
Beware AnsiSQL has table name and field name length limitations
Search for: Beware AnsiSQL has table name and field name length limitations
satya - 12/29/2019, 12:19:27 PM
VSCode has a plugin for SQL Server stored proces etc. It is here
VSCode has a plugin for SQL Server stored proces etc. It is here
satya - 12/31/2019, 1:39:46 PM
What questions to answer when creating a table
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?
satya - 2/28/2020, 11:29:33 AM
It is better to have short string keys for natural keys as opposed to numbers
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.