Hi, I'm here trying to get back to life :) I won't make a long intro, let's start...
Data centric applications are widely used. Maintenance can be a piece of cake or a source of pain. Some people tend to break the rules, doing a nasty design that just works for the moment but fails later. Adding more features or maintaining existing ones brings a lot of headaches like inconsistent data and broken constraints. The problem gets bigger when there is no documentation.
So, please do it right... don't depend on people who come after to review your work and correct it. Keep it clean so they can make further improvements, instead of letting them still trapping bugs.
The following tips, though basic & simple, but IMPORTANT. People doing the maintenance feels them by heart.
- Enforce Primary Key (PK) and Foreign Key (FK) Constraints:
Heck!! It's an a-b-c database tip. Are there anybody don't enforce PKs?!! Unfortunately, YES. Ignoring PKs and FKs leads to great problems later. It results in
inconsistent data. Tables can contain
duplicate records, which results in exceptions for queries expecting single value result. Some records may
reference non-existing records in other tables, which lead to unexpected results for join queries. Absence of PKs or allowing nulls for them can result in complete
records of null values too. Also, absence of FKs can lead to
referencing data in wrong tables!!!
So, Please do it right and enforce constraints..- Don't rely on users to do things right:
Users usually do mistakes. They usually don't understand that simple mistake (like a duplicate record) can make many things fail. They don't understand how will you suffer to overcome their errors, and bring data to a consistent state. That's why you must enforce constraints and data validation on every operation they do. Even you enforce validation on GUI, you should enforce the constraints on the DB objects too. This makes sure your
data is consistent and valid.
So, Please do it right and don't rely on users..
- Use the appropriate data types:
For every column in your entire database, pick the data type that best suites the values stored in it.
Avoid strings for primary keys. Picking strings for PKs leads to
performance deterioration for queries involving operations on the PK like joins and multiple match queries.
So, Please do it right and use appropriate data types..- Use stored procedures and functions when appropriate:
When there is a
common functionality shared among many queries, forms, reports...etc, make use of the stored functions. Some people copy and paste the code as many times as they need it. Any modification later in the functionality has to be applied to all these places, which result in forgetting some places and having them behave differently as they are not updated. Stored procedures and functions
saves time as you've to update one place. They also
makes it safe as all queries using this functionality will behave the same.
So, Please do it right and use stored functions when appropriate..
- Use the right trigger on the right objects:
Misplacing a trigger results into unexpected functionality as it fires on wrong actions. Also, someone who is maintaining the application would spend much time trying to figure where is this trigger and why it's firing on these actions.
So, Please do it right and use triggers correctly..When designing your database, normalize it and avoid redundant data.
Redundant data leads to inconsistency and newbies may get confused, what are the correct sources for the data?!! Replicated tables and redundant columns lead to
unexpected behavior for different views of the data. Some queries would update table1 while other queries expect to get the same data from table2!!
So, Please do it right and avoid redundant data.. Documentation saves much of time when maintaining an application. It help newbies to understand what should be done to fix problems. It helps to understand the structure and design of the application. At least explain the design of the database and describe the contents of each table. Missing the documentation leads to having developers guessing the relations and meaning of different items in the database. These guesses can be wrong, which may lead to wrong decisions.
So, Please do it right and document your work..
- One big query or Many small queries:
I faced some reports where the data were gotten by multiple small queries. One query gets the master data, and other queries computes the details for each record of the master. When these queries are compiled into one big query, the report loaded much faster. This one is a trade off between performance and simplicity of the code. You should pick the right decision on the basis of your application.
Finally, Please do it right.. If you have further tips, add them in the comments.
