Over the years, I have noticed a trend in building databases. A developer will get on a certain path when it comes to what I define as Lookup tables. There are many names or labels the community has placed on this Type/Code/Category of tables, but I want to give 3 examples of the types we ran into in the latest creation of a database.
The below list and table structure was standardized once so everybody (developer, DBA, BI, etc.) would be in agreement. After the Create Table below, please read for an explanation of the columns and types.
1. Drop-down list - this table is really just a pick list for a transaction or category type.
Example
BillingClass: B - Billable, NB - NonBillable, etc.
RequstType: P - Phone, L - Letter, etc.
2. Developer Control - developers usually like to have some tables that cannot be change by end user because it would change the flow of the application code. This group of developers wanted to enumerate the IDs in .Net code. Too bad we (DBAs) can use enumeration in a Query window
Example
WorkFlow - I - Initial, U - Updated, P - Processed & C - Close
3. End User Control - end users want to be able to add to the list for use in the application, but unlike the drop-down list, there are other properties to the Item.
Example
RevenueCode - 435 - Inventory stream, 321 - visit cost, etc.
The first 2 types have a consistent structure:
CREATE TABLE [Billing].[lkClass](
[Code] [varchar](25) NOT NULL,
[ID] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[Description] [varchar](1000) NULL,
[DisplayOrder] [int] NOT NULL,
[RowState] [int] NOT NULL,
[TimeStamp] [timestamp] NULL,
CONSTRAINT [PK_lkClass] PRIMARY KEY CLUSTERED
([Code] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [uc_lkClass_ID] UNIQUE NONCLUSTERED
( [ID] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [DB_INDEX],
CONSTRAINT [uc_lkClass_Name] UNIQUE NONCLUSTERED
( [Name] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [DB_INDEX]
) ON [PRIMARY]
The code is 25 characters because we voted to have a varchar () that would satisfy all possibilities. Why still use Code when there is a unique ID field? End users still like to be able to type known code/type values into an interface for fast data entry. Even with the intelliSence possibilities, I personally do not see codes going away with end users. Also, reports need abbreviated versions of lookup to help compact display. This is the foreign key to a related table, not the ID.
The ID is used for .Net objects that want to reference a list with an integer, rather than using a Search/Find method to locate the entry in a list/collection. The Name is really the long description, while the Description is what you might call a memo field (notice MS Access reference).
DisplayOrder is self-explanatory and RowState has values like 0-active, 1-inactive, and other numbers reserved for future use. This allows the row to be ‘deleted’ – inactive, but not removed from the table. Timestamp is really the RowVersion data type from SQL Server data types. We have not changed this to RowVersion, yet.
Even though State Abbreviation is known as only 2 characters and you need only 2 columns in the table, we still use this structure because the Object Oriented interface/object code using the same structure is global throughout the project. The end user control type table will include the additional columns on the same table. We do not create a one-to-one relationship to another table just for more attibutes, in most cases.
Primary key is in the Primary file group because it is the clustered index, but the other Unique Constraints are in the INDEX File Group in order to place the Clustered Index (table) on a different file group than the Indexes. The Unique constraints are used to make sure duplicate IDs and/or Names are never possible even though the developers promise that will prevent that in the Code.
Friday, July 16, 2010
Thursday, July 8, 2010
Database Standards Part VI: code and design
Database Standards Part VI: code and design
This is Part 6 of 6 blogs about database standards. This last post is about coding standards that should be followed.
Design
A data dictionary and diagram should be maintained. The diagram can be a bird's eye view. The dictionary contains tables with description and columns with data type, size, default, constraints and descriptions. The foriegn keys would be listed along with primary keys.
Coding
- Avoid using SELECT *.
- Use CTEs instead of Cursors
- Dynamic SQL is difficult to read, confuses security and not very maintainable.
- NOLOCK and READUNCOMITTED can only be used when absolutely necessary.
- INNER JOIN not JOIN
- prefix objects with schema, even dbo.
- Use TRY/CATCH for error trapping
- only use parentheses for AND\OR expressions
- spaces between expressions and variables
Type = 'S' NOT type='S'
- BEGIN/END should only be used with there is more than one execution line after condition
Formatting
Bad:
Select Sample.*, Analysis.*,
Sample.DueDate, Sample.EnteredBy
FROM Sample JOIN
Analysis on Sample.SampleID = Analysis.SampleID
Better:
SELECT s.SampleID, s.Description AS SampDesc,
a.AnlCode AS AnalysisCode
FROM Sample s
INNER JOIN Analysis a
ON a.SampleID = s.SampleID
WHERE s.SampleID = @SampleID
Next set of Blogs will be a diary of the transition from production senior DBA to BI Data Integration Lead Developer.
Thomas LeBlanc
Outoging Production Senior DBA
This is Part 6 of 6 blogs about database standards. This last post is about coding standards that should be followed.
Design
A data dictionary and diagram should be maintained. The diagram can be a bird's eye view. The dictionary contains tables with description and columns with data type, size, default, constraints and descriptions. The foriegn keys would be listed along with primary keys.
Coding
- Avoid using SELECT *.
- Use CTEs instead of Cursors
- Dynamic SQL is difficult to read, confuses security and not very maintainable.
- NOLOCK and READUNCOMITTED can only be used when absolutely necessary.
- INNER JOIN not JOIN
- prefix objects with schema, even dbo.
- Use TRY/CATCH for error trapping
- only use parentheses for AND\OR expressions
- spaces between expressions and variables
Type = 'S' NOT type='S'
- BEGIN/END should only be used with there is more than one execution line after condition
Formatting
Bad:
Select Sample.*, Analysis.*,
Sample.DueDate, Sample.EnteredBy
FROM Sample JOIN
Analysis on Sample.SampleID = Analysis.SampleID
Better:
SELECT s.SampleID, s.Description AS SampDesc,
a.AnlCode AS AnalysisCode
FROM Sample s
INNER JOIN Analysis a
ON a.SampleID = s.SampleID
WHERE s.SampleID = @SampleID
Next set of Blogs will be a diary of the transition from production senior DBA to BI Data Integration Lead Developer.
Thomas LeBlanc
Outoging Production Senior DBA
Thursday, June 17, 2010
Database Standards Part V: Triggers and User-defined Functions
Database Standards Part V: Triggers and User-defined Functions
This is Part 5 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.
Triggers
Naming convention for triggers are 'it' for insert, 'ut' for update and dt for delete (itPatient). Trigger can slow CRUD statements so we warn developers to be careful when creating triggers. It also can 'hide' logic from development. Over and over, we get confused people trying to find how data gets added to a table because of a trigger on another table. Verify during a review that the trigger can handle multi-row updates when joining to the inserted or deleted recordsets. Most triggers I see are related to auditing Ins/Upd/Del statements.
User-Defined Functions
User-defined functions are prefixed with udf. They can be helpful with string manipulation commonly used in stored procedures or SQL statements. UDFs do add processing time to the execution and can cause scans when used on fields in a WHERE clause. Looking at an execution plan might exclude the code from the UDF. I see table-valued UDFs used like DMFs from dynamic management objects from Microsoft, mostly on small data sets.
Also, when profiling, you get a statement for each call to the udf for each row in the SELECT statement if used in a query, so you might want to exclude the data in the profiled output.
Repeating again, and again…
Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. If a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about coding and deisgn standards.
Thomas LeBlanc
This is Part 5 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.
Triggers
Naming convention for triggers are 'it' for insert, 'ut' for update and dt for delete (itPatient). Trigger can slow CRUD statements so we warn developers to be careful when creating triggers. It also can 'hide' logic from development. Over and over, we get confused people trying to find how data gets added to a table because of a trigger on another table. Verify during a review that the trigger can handle multi-row updates when joining to the inserted or deleted recordsets. Most triggers I see are related to auditing Ins/Upd/Del statements.
User-Defined Functions
User-defined functions are prefixed with udf. They can be helpful with string manipulation commonly used in stored procedures or SQL statements. UDFs do add processing time to the execution and can cause scans when used on fields in a WHERE clause. Looking at an execution plan might exclude the code from the UDF. I see table-valued UDFs used like DMFs from dynamic management objects from Microsoft, mostly on small data sets.
Also, when profiling, you get a statement for each call to the udf for each row in the SELECT statement if used in a query, so you might want to exclude the data in the profiled output.
Repeating again, and again…
Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. If a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about coding and deisgn standards.
Thomas LeBlanc
Friday, June 4, 2010
Database Standards Part IV: Stored Procedures
This is Part 4 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.
Stored Procedure
Stored procedures should be required for all create, read, update and delete (CRUD) DML (Data Manipulation Language) statements. Using stored procedures benefits security, consistence, performance and manageability on the DBA side of applications.
Security can be managed by giving GRANT EXECUTE to logins requiring access to DML statements, or in 2005 and greater EXECUTE can be granted to a login on the entire database. Performance gains are realized in compiled query plans and SQL Server’s ability to re-compile only the statement in the SP that needs a new/better plan.
Naming conventions:
Read - uspPatientGet or GetPatient
Delete - uspPatientDel or DelPatient
Report – rptSecurityAudit
Process – prcProcessPatient
The use of Schemas can better organize the SPs. Do not use prefixes sp, sp_ or xp_. These are used by the SQL Server system or custom extended procedures.
Precompiled SQL can use less memory in the Procedure Cache and require less look up in Proc Cache for existing plans.
Remember not to repeat the schema name in the SP – Patient.GetDetails instead of Patient.GetPatientDetails.
SET ANSI_NULLS ON can be a requirement because it will be depreciated in future SQL Server versions.
SET NOCOUNT ON can be prevent round trips to the client - http://msdn.microsoft.com/en-us/library/ms189837.aspx
Using a Source Control application can reduce comments in a SP. The versioning in TFS or Source Safe gives the developer the ability to associate comments with the check in. Our SPs begin with the following structure
IF NOT EXISTS (SELECT * FROM Sys.Objects
WHERE Object_Id = OBJECT_ID(N’[Ptient].[rptPatientReleased] ’)
AND type in (N’P’, N’PC’))
BEGIN
CREATE PROCEDURE [Ptient].[rptPatientReleased]
AS RETURN
GO
END
ALTER PROCEDURE [Aptient].[rptPatientReleased] AS
SELECT TOP 10 * FROM NewProcedureView
GO
Always end the SP in GO when creating or altering. The Alter is preferred because it retains permissions on SP. If DROP/CREATE is used, remember to apply the permissions. Return should not be used to return data to the calling application. Return should be reserved for returning the status of the SP. Use INPUT and OUTPUT parameters in the SP.
Repeating again, and again…
Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about Triggers and User-Defined Functions.
Thomas LeBlanc
This is Part 4 of 6 blogs about database standards. Again, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.
Stored Procedure
Stored procedures should be required for all create, read, update and delete (CRUD) DML (Data Manipulation Language) statements. Using stored procedures benefits security, consistence, performance and manageability on the DBA side of applications.
Security can be managed by giving GRANT EXECUTE to logins requiring access to DML statements, or in 2005 and greater EXECUTE can be granted to a login on the entire database. Performance gains are realized in compiled query plans and SQL Server’s ability to re-compile only the statement in the SP that needs a new/better plan.
Naming conventions:
Read - uspPatientGet or GetPatient
Delete - uspPatientDel or DelPatient
Report – rptSecurityAudit
Process – prcProcessPatient
The use of Schemas can better organize the SPs. Do not use prefixes sp, sp_ or xp_. These are used by the SQL Server system or custom extended procedures.
Precompiled SQL can use less memory in the Procedure Cache and require less look up in Proc Cache for existing plans.
Remember not to repeat the schema name in the SP – Patient.GetDetails instead of Patient.GetPatientDetails.
SET ANSI_NULLS ON can be a requirement because it will be depreciated in future SQL Server versions.
SET NOCOUNT ON can be prevent round trips to the client - http://msdn.microsoft.com/en-us/library/ms189837.aspx
Using a Source Control application can reduce comments in a SP. The versioning in TFS or Source Safe gives the developer the ability to associate comments with the check in. Our SPs begin with the following structure
IF NOT EXISTS (SELECT * FROM Sys.Objects
WHERE Object_Id = OBJECT_ID(N’[Ptient].[rptPatientReleased] ’)
AND type in (N’P’, N’PC’))
BEGIN
CREATE PROCEDURE [Ptient].[rptPatientReleased]
AS RETURN
GO
END
ALTER PROCEDURE [Aptient].[rptPatientReleased] AS
SELECT TOP 10 * FROM NewProcedureView
GO
Always end the SP in GO when creating or altering. The Alter is preferred because it retains permissions on SP. If DROP/CREATE is used, remember to apply the permissions. Return should not be used to return data to the calling application. Return should be reserved for returning the status of the SP. Use INPUT and OUTPUT parameters in the SP.
Repeating again, and again…
Though my opinion is not the same as some of these standards, I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about Triggers and User-Defined Functions.
Thomas LeBlanc
Tuesday, May 25, 2010
Database Standards Part III: Indexes, Constraints & Primary/Foreign Keys
This is Part 3 of 6 blogs about database standards we use at my current employer. Repeating myself, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is to establish a set of standards and stick to them.
Indexes
The naming of indexes is not unanimous. The standard is for a prefix of idx plus the table name then the field names with an underscore between the table and field name – idxPatient_LocationCode. This will create some very long names. I prefer abbreviation of the table and fields names, or like another DBA a reference name of where the index will be used.
A reminder to developers and contractors is that a primary key clustered index is an index. We have seen duplicate indexes created because of this. I kid not. Also, clustered indexes are included in non-clustered indexes, so a multi-column clustered index has to be seriously reviewed before implemented.
Understanding covering indexes and how they are used is important to reduce the columns used in some indexes. Fields that are regular updated probably should not be in a clustered index.
A reindex maintenance plan needs to be implemented. I will try to cover the custom one we use in a later blog or webcast.
Constraints
A unique constraint has its own index, so do not create an index on a unique constraint.
Use prefix chk (chkDigits) for check constraint and uc (ucPatientNumber) for unique constraint naming.
Default definitions should be in line with the create table statement and default objects have been deprecated, so do not use. Do not use data type conversion in constraint definitions.
Primary Key and Foreign keys
All tables have to have a primary key. When an identity field is used, a unique constraint must be created from a separate field(s) to uniquely identify a row. Every table needs to be able to be queried based on the contents, not an identity field. The normalization talks I have been privileged to give, explains more about this with the 1st normal form portion.
All foreign keys have to be indexes and should be the primary or unique constraint key from the parent table.
Naming convention is prefixed with pk for primary key (pkPatientNumber). Foreign Key naming is prefix fk then parent table name underscore column(s) underscore child table name and last underscore column(s) – fkPatient_ID_Admit_PatientID
Repeating Again, and again…
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about Stored Procedures.
Thomas LeBlanc
Indexes
The naming of indexes is not unanimous. The standard is for a prefix of idx plus the table name then the field names with an underscore between the table and field name – idxPatient_LocationCode. This will create some very long names. I prefer abbreviation of the table and fields names, or like another DBA a reference name of where the index will be used.
A reminder to developers and contractors is that a primary key clustered index is an index. We have seen duplicate indexes created because of this. I kid not. Also, clustered indexes are included in non-clustered indexes, so a multi-column clustered index has to be seriously reviewed before implemented.
Understanding covering indexes and how they are used is important to reduce the columns used in some indexes. Fields that are regular updated probably should not be in a clustered index.
A reindex maintenance plan needs to be implemented. I will try to cover the custom one we use in a later blog or webcast.
Constraints
A unique constraint has its own index, so do not create an index on a unique constraint.
Use prefix chk (chkDigits) for check constraint and uc (ucPatientNumber) for unique constraint naming.
Default definitions should be in line with the create table statement and default objects have been deprecated, so do not use. Do not use data type conversion in constraint definitions.
Primary Key and Foreign keys
All tables have to have a primary key. When an identity field is used, a unique constraint must be created from a separate field(s) to uniquely identify a row. Every table needs to be able to be queried based on the contents, not an identity field. The normalization talks I have been privileged to give, explains more about this with the 1st normal form portion.
All foreign keys have to be indexes and should be the primary or unique constraint key from the parent table.
Naming convention is prefixed with pk for primary key (pkPatientNumber). Foreign Key naming is prefix fk then parent table name underscore column(s) underscore child table name and last underscore column(s) – fkPatient_ID_Admit_PatientID
Repeating Again, and again…
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about Stored Procedures.
Thomas LeBlanc
Thursday, May 13, 2010
Database Standards Part II: Schemas, Tables & View and Columns
This is Part 2 of 6 blogs about database standards we use at my current employer. Repeating myself, though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is establish a set of standards and stick to them.
Schema
The seperation of schmea name and logins was a great addition in SQL Server 2005. Schema names should be used in place of dbo. This makes viewing in SSMS great with filters. It also categorizes the objects so a new DBA or developer can get a good view of the organization in a database. We suggest a Common schema for objects that do not fit in a group, then names that are specific to functionality - Payroll, HR, Billing, etc.
Tables
Tables names should reflect its functionality and they cannot start with a number, contain spaces, underscores or be reserved words. ANSI_NULL (OFF is deprecated in a future version) should be SET ON. Pascal Casing should be used like ClientBill and singular like Admit not Admits.
There are 3 different kinds of Lookup tables we see - developer populated (workflow), small lists (state) and known list that are updattable (RevenueCode). Small lookup tables can use a character primary key. If the developer insists on ID (identity), then a character code/description column created with a unique contraint must be included. The code should be used in the transaction table. (I will clarify this is a seperate blog). Lookup tables use camel case - lkState.
The transaction table(s) should use a ID (indentity or sequential int) as the primary, but should have a unique contraint for uniquely identifying a row. Third Key Normal Form and higher is required. The ID field will be the foreign key in the child table, and namedID (PatientID in an Episode table).
Views
Camel casing should be used in naming views - vwPatientEpisode. Future thinking should be used if the view will eventually become an indexed view, then schema binging and ansi settings need to be looked at.
Columns
Column names should describe the data placed in them, using Pascal Casing - LastName and not start with a number/special character, no spaces, dashes or underscores. Beware of reserve words or key words as object names. Do not repeat table name - use LastName not PatientLastName unless a foreign key to another table - PatientID. No data type in the name - intPatientCount, but Date is acceptable in a date name - AdmitDate.
Be aware of new features in 2008 like a seperate DATE and TIME data types. Before, you had to use midnight as the time in a date column type in order to not specify a time.
Repeating
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about Indexes, constraints and Primary/Foreigh Keys.
Thomas LeBlanc
Schema
The seperation of schmea name and logins was a great addition in SQL Server 2005. Schema names should be used in place of dbo. This makes viewing in SSMS great with filters. It also categorizes the objects so a new DBA or developer can get a good view of the organization in a database. We suggest a Common schema for objects that do not fit in a group, then names that are specific to functionality - Payroll, HR, Billing, etc.
Tables
Tables names should reflect its functionality and they cannot start with a number, contain spaces, underscores or be reserved words. ANSI_NULL (OFF is deprecated in a future version) should be SET ON. Pascal Casing should be used like ClientBill and singular like Admit not Admits.
There are 3 different kinds of Lookup tables we see - developer populated (workflow), small lists (state) and known list that are updattable (RevenueCode). Small lookup tables can use a character primary key. If the developer insists on ID (identity), then a character code/description column created with a unique contraint must be included. The code should be used in the transaction table. (I will clarify this is a seperate blog). Lookup tables use camel case - lkState.
The transaction table(s) should use a ID (indentity or sequential int) as the primary, but should have a unique contraint for uniquely identifying a row. Third Key Normal Form and higher is required. The ID field will be the foreign key in the child table, and named
Views
Camel casing should be used in naming views - vwPatientEpisode. Future thinking should be used if the view will eventually become an indexed view, then schema binging and ansi settings need to be looked at.
Columns
Column names should describe the data placed in them, using Pascal Casing - LastName and not start with a number/special character, no spaces, dashes or underscores. Beware of reserve words or key words as object names. Do not repeat table name - use LastName not PatientLastName unless a foreign key to another table - PatientID. No data type in the name - intPatientCount, but Date is acceptable in a date name - AdmitDate.
Be aware of new features in 2008 like a seperate DATE and TIME data types. Before, you had to use midnight as the time in a date column type in order to not specify a time.
Repeating
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. IF a deviation is needed, a group discussion should be voted on to change.
Next week I will talk about Indexes, constraints and Primary/Foreigh Keys.
Thomas LeBlanc
Thursday, May 6, 2010
Database Standards Part I: Defs, Abbreviation & Data Types
This is Part 1 or 6 blogs about database standards we use at my current employer. Though we do not enforce these with an iron fist, they are advised for clarity and consistency. The important point, in my opinion, is establish a set of standards and stick to them.
Definitions
We list some definitions are the beginning of our document. Included are Camel casing (vwVisitOrders) and Pascal casing (MedicalData)
Abbreviations
Though most developers like to spell everything out because they can use IntelliSense, abbreviations are ok for some common and industry standard abbreviations that can be used. Examples are Id (identification), SSN, DOB or Rpt (Report).
Data Types
Now, the standards for these are good because of the consistency factor, comparisons (WHERE clause) and SQL Server features. Some features of SQL Server get deprecated, and others are improvements. A good example is using varchar (max) instead of text data type.
Yes/No: Use bit data type. We also suggest naming the field like a question: Isdeleted
Flag/Status: use tinyint (1-Active, 0-Inactive, other numbers can be used for future status)
Use Unicode only if necessary: nvarchar(100)
Integers: Be sure to properly size up front, if not sure use int or bigint
Fixed field character lengths: State - char(2)
Variable character length: LastName varchar(25)
Money/Decimal – use over float/real. Price decimal(10,2)
Null/Not Null – won’t even discuss
Max – use with varchar, nvarchar, vbinary instead of text and binary.
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. It a deviation is needed, a group discussion should be voted on to change.
God Bless,
Thomas LeBlanc
Definitions
We list some definitions are the beginning of our document. Included are Camel casing (vwVisitOrders) and Pascal casing (MedicalData)
Abbreviations
Though most developers like to spell everything out because they can use IntelliSense, abbreviations are ok for some common and industry standard abbreviations that can be used. Examples are Id (identification), SSN, DOB or Rpt (Report).
Data Types
Now, the standards for these are good because of the consistency factor, comparisons (WHERE clause) and SQL Server features. Some features of SQL Server get deprecated, and others are improvements. A good example is using varchar (max) instead of text data type.
Yes/No: Use bit data type. We also suggest naming the field like a question: Isdeleted
Flag/Status: use tinyint (1-Active, 0-Inactive, other numbers can be used for future status)
Use Unicode only if necessary: nvarchar(100)
Integers: Be sure to properly size up front, if not sure use int or bigint
Fixed field character lengths: State - char(2)
Variable character length: LastName varchar(25)
Money/Decimal – use over float/real. Price decimal(10,2)
Null/Not Null – won’t even discuss
Max – use with varchar, nvarchar, vbinary instead of text and binary.
Though my opinion is not the same as some of these standards (I say you can abbrev. anything), I do believe standards should be discussed and established, then followed. It a deviation is needed, a group discussion should be voted on to change.
God Bless,
Thomas LeBlanc
Subscribe to:
Posts (Atom)