If you have a sourceforge account, and are on your way to becoming the best thing to happen to the web since Yahoo or Google, then I beg of you to put a call out for people who understand database design fundamentals.
Designing an interface with PHP is one thing. Designing an “application” is quite another, as it includes designing the architecture of the application, how the various components of the application will interact and communicate, and also how the data used by the application will be managed and stored. This last piece is a decidedly un-sexy part of application design, and is often also (and unfortunately) trivialized by developers.
Here’s the story: If you’re a PHP developer, I don’t really want you to learn how to design a database. No, really. I don’t. I want you to write PHP. There are few people who do both things extremely well, because both take a good bit of time. If you’re a PHP developer, I want you to write code that’ll make my head spin. However, the path to greatness is to be conscious of your own ignorance — so just acknowledge that you’ve never done or studied database design, and go find someone who has!
I have NEVER seen a help wanted ad on Sourceforge for data designers. I’ve looked. I occasionally go back and repeat my searches. I’m always disappointed. Meanwhile, while there are applications that practice sane database design fundamentals, the overwhelming majority of applications I’ve evaluated have also left me disappointed.
I believe that this problem roots from a few different problems and misconceptions, which I’ll list here:
1. The interface determines database design
I disagree. I think approaching a database this way is somewhat short sighted. Doesn’t that statement imply quite strongly that any future changes in the interface will inevitably mean a change in the database design? And then, if we’ve agreed that interface changes affect database design, doesn’t that raise the chances that future releases are either going to be incompatible with earlier ones, or will involve laborious database migration schemes?
Furthermore, this notion breaks down quite quickly when you get into data warehousing schemes where more than one application is acting on the data. Then what? Which interface is in charge of defining the database?
Really, database design and interface design should only be related through the data entities themselves. The data model is the story of how data is related, and the interface exploits the relationships to provide a view of the data to the user. Aside from that, interface design and interface design are unrelated.
2. “I only need a small, simple database”
Maybe that’s true, but double check yourself. If you think that you only need three tables, but those tables are a mish-mash of duplicate data, null fields, or (the worst) fields that hold more than a single value, then your database design would appear to be the result of making SELECT statements as simple as possible instead of sane design principles. As your application marches ahead, you’ll eventually find that you need to overhaul the database, or scrap it completely and start over, which can be a pretty hellacious event to deal with as a developer. That database API you worked so hard on is all but useless, and it could’ve all been avoided if you’d just run all of this by a database person. In a lot of the instances where I see database design completely fail, it’s because the designers confused “data design” with minimizing the number of tables involved. In reality, more normalized data usually results in more tables, not less.
3. “More tables makes for harder coding”
Well, fewer tables, if it’s at the cost of normalization, makes for certain refactoring when you have to overhaul your data design because it can no longer support the needs of the application. Take your pick.
Really, it doesn’t have to be harder to code against in a lot of cases. Usually, inserts end up being slightly more complex. However, if you’re using a database that supports database “views”, then select statements will hardly have to change at all. If you don’t know what views are or how to create them, you should definitely seek the help of a database professional.
Look, I’m not an advocate of using fifth normal form for everything. In fact, I’ve done a lot of database consulting and have never seen a database in fifth normal form. However, shooting for third normal form generally results in a database that is flexible enough to move in whatever direction your application decides to move, while at the same time keeping things simple enough to keep your PHP coders from having to become hardcore SQL wizards. Data design is a dry, monotonous, maybe masochistic practice. But it’s one that will pay you dividends well into the future.