Most developers know all the fundamental database design stuffs like Primary Key, Foreign Key, Unique constraints, etc. But surprisingly not many of us really put much attention into it. It may not be the coolest thing. It is so 1960s. It ain't Web 2.0. It ain't ESB. But it is the simplest tools that can save you from future pains. Ignore this at your own perils.
Recently, I have been involved in a project to migrate our main web application to EJB3. The architecture of the existing applications is quite advanced for its time. It uses all the good design patterns stuffs like ORM, Dependencies injection, and SOAP webservice. It is full on design patterns. It externalizes all the dependencies into an XML configuration file. Just like Spring but in a much painful way :)). It had so many layers of indirection for any simplest use cases. It was an overengineering gone mad.
It doesn't achieve its goal of flexibility. The flexibility is there, but it is so complex, that makes it so painful to change anything. Just the opposite of what the intended effects are. What can I say. KISS (Keep It Simple Stupid) is the most beautiful thing.
Yet these weren't the worst problem of this application. It is the data integrity or lack thereof. Most of the tables have neither primary key nor foreign key constraints.
The database were full of duplications. It violates every business rules. Mind you, this database supposedly to control authentication and authorization of our main websites.
Lessons learned:
- Never design a table without primary key.
- Put all the constraint directly into the database or just don't use relational database.
- Strive for the simplest solutions. That is often all you need.
