It is far better to have an excellent Database Design and a crappy Application Layer, than an excellent Application Layer with a crappy Database design.
Why is that? Simply put, you can always skim your Application code off the top and write a new one. But you can not simply swap out the Data Layer without completely undermining the Application.
The closer we can model our business logic in the database itself – the better it is for the business. When new unforseen questions arise about the business, we can always run adhoc queries from a well designed database schema to get the answers.
- Who are our top customers?
- What is revenue month over month? Year over year?
- What is the distribution of sales by product types across all income channels?
- What percentage of our customers pay late over 30 days?
- What time of day do we have the most volume?
- What is the percentage of repeat customers vs first time buyers?
- Has revenue gone up since the last website revamp?
All of these questions are very easily answered with a little SQL magic, when the database is well designed. And all of these questions can be horrendously hard to answer when it is not.
For all important systems, the Data Layer must be King.
Somewhere someone started the tradition of capitalizing keywords in SQL. Its like shouting all the unimportant parts of the code and making what’s important harder to read.
SELECT b.id as book_id, a.name as author, b.pub_date as publication_date FORM books b LEFT JOIN authors a on b.id = a.book_id WHERE foo_bar = 'Fibonacci';
I say stop doing that. And use instead super indentation and formatting. Pseudo English is easier to read when it is not capitalized.
b.id as book_id,
a.name as author,
b.pub_date as publication_date
left join authors a on b.id = a.book_id
foo_bar = 'Fibonacci'
The Model-View-Controller Pattern (MVC) is not the only game in town. In fact it is awkward and cumbersome in some cases.
MVC is a Design Pattern, and Design Patterns show us how to solve common problems with common solutions. However we should not just use them directly all the time – but rather learn from them and use them as reference points for solving problems.
The most worthwhile component of MVC is the Model, (or Data Layer). It makes good sense to isolate it and encapsulate it. It make for very robust and reusable code. All your SQL related calls should be in one place.
Code above the Data Layer can express itself in more business friendly terms, ie. book = Book(‘Moby Dick’), and not ‘select * from books where title like ‘%moby dick%’, and status_id = 1;’
In MVC the View and the Controller components are isolated and encapsulated. I disagree — This can makes simple tasks harder. Views and Controller functionality should be able to commingle. This is were a lot of the interesting and sophisticated coding happens.
What should we call it? Call it whatever you like.
I hear that a lot. As coders and builders of systems, we should take the time needed to come up with the best names for the systems, databases, tables, columns, filenames, modules, classes, methods, functions, and variable names, we create.
To name something is to know it.
Good names means less confusion and better understanding. It makes talking about the system more natural. It improves dialog between business users and the tech team. In fact we should think as the business user when naming our components.
It makes it easier for you and others to maintain your code.
So take your time, step back, think hard about what something truly is – and name it accurately.
A stitch in time saves nine, is a fundamental principals we should use to write code and build systems. It might be said, a stitch in time saves you from being stuck forever with unruly software.
People just want to get the job done. But any small mistake or short sightedness up front in critical parts of the system, will trip you up right away in the next wave of feature enhancements, and bug fixes.
An ounce of good design is worth a million dollars a few years down the road, for the people who will be depending on it later.
And good design — takes a little more time.
So often I hear tech folks talking about some implementation as being better than another because “It is more efficient“. Efficient? Efficient for who? The computer? Are we shaving milliseconds off processing time? That’s ridiculous.
We should be in the business of optimizing human time, not computer time. Human time is more expensive – while computer time is cheap.
We want code to be laid out clearly and easy to read. We want code to be like a poem. We want something that we can maintain over time without having to scratch our heads every time we come back to.
A simple example, have a look at,
id, process_date, qty
coalesce(units, '') not in ('K1', 'K2', 'K3')
What the heck is that coalesce doing in the where clause? If you think about it for a minute it make sense. Oh, it’s more efficient for the computer that way. Who cares? How about
id, process_date, qty
units is not null and
units not in in ('K1', 'K2', 'K3')
Here the where clause is immediately easy to understand and to maintain. It speaks to the essence of the problem in clear pseudo English.
Ask yourself, is it Elegant? Is it a thing of beauty?
If this is some important piece of code that others will be using then it should be written well.
I read once in an old programming book, “Born to Code in C”, that first you need to make it work, then make it beautiful. I code by this rule. So often I find folks don’t spend enough time on the second part.