When designing a data layer, there are some fundamental requirements and design decisions that should be made early. Delaying these decisions can be extremely costly.
Requirement |
Design/Architectural Decision |
An important requirement for a reference enterprise architecture is to be able to easily switch databases at a later date. This must be possible with minimum effort. |
Have a well defined strategy when it comes to autogenerated primary keys and the use of stored procedures. Rather than leaving database specifc options out (and compromising on performance) it is more important to use consistant pre-defined strategies for changing databases. These strategies are tied closely with the features provided by the persistence layer. |
Support for optimistic concurrency |
Decide on a uniform optimistic concurrency model based on version or timestamping and introduce into the table design. It's better to err on the side of timestamping more tables than less. This requirement is closely tied to the persistence layer. The persistence layer must support platform or app-server independent optimistic concurrency mechanism. |
Allow user-visible codes to be easily modifiable. In a long-life-span solution, coding schemes can be expected to undergo changes. The design should easily accommodate such changes. |
Always use surrogate keys as primary keys (not user-visible codes) so that user-visible codes can be changed or re-organized easily in the future without impacting foreign key relationships. (Use GUID's or Auto-generated ID's as surrogate keys) |
Should facilitate logging/auditing of business transactions as well as table-level changes |
Design a uniform and simple business transaction logging/auditing mechanism (who did what when). This decision is also tied to the business service layer design and session handling and logging mechanism. |
| You need to extract the best performance out of the database | This requirement puts a constraint on constraints! Although database constraints are a good idea for simple small-scale systems, it is often necessary to do away with database level constraints for highly scalable enterprise systems and rely on application logic to provide consistency. This decision has an important impact on the design of business logic and transaction handling as you cannot rely on the database to throw an exception on consistency issues. If you decide not to have constraints on a system, have a backup mechanism that independantly tests integrity violations on a regular basis at least until the system stabilizes. This can be a bunch of SQL's that tests primary-key duplicates and other such violations. |
| When designing the data model, it is also important to seperate the reporting requirements from OLTP requirements and to decide whether a seperate data warehouse is needed. Because of the locking strategy of most databases, there is a significant toll in terms of running large reports AND doing OLTP transactions on the same database. Oracle's multi-version locking mechanism helps alliviate this but not without causing it's own unique problems for the DBA. | |
| Denormalize when necessary. Denormalize sparingly in instances where you can gain a significant performance enhancement for querying. Denormalizing always adds to the cost of inserts and updates. | |
| Never leave out stored procedures and database-specific load tools for bulk-load type operations. | |
| Just like stored procedures, don't leave out one-to-one relationships. Databases typically load data into memory blocks and having large table widths can hinder internal caching performance. Introduce such relationships based on the expected usage scenarios. | |
Nice to have - A pluggable history recording mechanism (where you can re-create a snapshot of any table as at a given timestamp) |
Use either a trigger based mechanism or a generic mechanism tied to the persistence code-generation layer. See this auditing example with Hibernate. |
| Internationationalization | Internationalization has implications at the data layer that should be thought through. The simplest recommendation would be to use UTF-8 as the default encoding for the DB. This is tied to your overall internationalization strategy. |
Go back to the Architecture Page
Data Warehousing
Expert one-on-one Oracle
Database Design