To schema or not to schema - That is the question;
Whether it is easier to find a table with a Schema.TableName ;
Or to simply build one with a strong naming convention,
and, by opposing schemas, force other developers to learn (and re-learn)
No more, with schemas ;
It's an interesting dilemma - especially when designing a database for an organization who changes standards with almost every new development project.
One recent project implemented schemas along with a naming convention so much that the schema name was repeated in the name of the table:
SCHEMA.SCH_ClientName
Definitely overkill. But then if I want to be able to separate out lookup tables from my other tables, having a LOOKUPS schema would appear to work, except that it creates the "extra typing" scenario as every table has to include the schema.
There are a few alternatives:
a) prefix tables with naming conventions that make their purpose obvious. luStates = a lookup table of states and tblStates for a table where the business purpose might be states.
b) ignoring all and keeping it related to the business purpose.
As with most development decisions, a lot of this can be subjective. Some developers even like to make it a challenge. But what every designer should keep in the back of their mind is this:
Is this readable?
Many developers I've spoken to come back to an older application and wonder what wonderful medications they were taking when they wrote a certain line of code (or they forget to remember it was their code to begin with). So the readability and maintainability of the code and by extension, the design becomes super important.
In a recent project, I've opted for a hybrid: the use of schemas for business purposes and the use of naming conventions for lookup tables, only. It makes it easier to identify the lookup tables in code but the schemas make the graphical depiction of the database cleaner.
What approach do you prefer when building an application?
Whether it is easier to find a table with a Schema.TableName ;
Or to simply build one with a strong naming convention,
and, by opposing schemas, force other developers to learn (and re-learn)
No more, with schemas ;
It's an interesting dilemma - especially when designing a database for an organization who changes standards with almost every new development project.
One recent project implemented schemas along with a naming convention so much that the schema name was repeated in the name of the table:
SCHEMA.SCH_ClientName
Definitely overkill. But then if I want to be able to separate out lookup tables from my other tables, having a LOOKUPS schema would appear to work, except that it creates the "extra typing" scenario as every table has to include the schema.
There are a few alternatives:
a) prefix tables with naming conventions that make their purpose obvious. luStates = a lookup table of states and tblStates for a table where the business purpose might be states.
b) ignoring all and keeping it related to the business purpose.
As with most development decisions, a lot of this can be subjective. Some developers even like to make it a challenge. But what every designer should keep in the back of their mind is this:
Is this readable?
Many developers I've spoken to come back to an older application and wonder what wonderful medications they were taking when they wrote a certain line of code (or they forget to remember it was their code to begin with). So the readability and maintainability of the code and by extension, the design becomes super important.
In a recent project, I've opted for a hybrid: the use of schemas for business purposes and the use of naming conventions for lookup tables, only. It makes it easier to identify the lookup tables in code but the schemas make the graphical depiction of the database cleaner.
What approach do you prefer when building an application?
Comments