[My] Database schema designed and ready to work with

I’m starting to really like this project. I spent this afternoon designing and inputting the database schema - and what a schema it is!

The heart of the project

Since My Buster Tests is going to rely heavily on a database, the schema is really, really important. It is the very heart of the application, because it defines exactly what it can and can’t do. In fact, I prefer having the schema in place because if you make it correctly it tells you exactly what you need to code.

As I went through it, I realised things needed to be added and things could be removed. That’s really great, because by designing the application around the schema (rather than vice versa), I’ve missed a few big potholes and realised some great functionality is just round the corner and very easy to implement.

Keeping it logical

Part of the ‘fun’ of creating a schema is that you have to be so absolutely pedantic to make it work properly. There is very little room for mistakes, and while you don’t necessarily have to make the most complex database which uses lots of funky functions and features, you do need to stick to the rules of things like database normalisation.

When it comes to normalisation, which essentially means keeping everything neat and orderly, by creating keys and tables in a way that stops you from repeating yourself. It’s helpful when it comes to this and the programming you’ll have to do later on to use thoughtful names that are logical rather than cryptic.

For example, the respondents to the tests are identified with their respondent_id and kept in a table called my_respondents, and leave responses with a response_id kept in my_responses. The my_ prefix on all tables lets me know where I am when I’m looking at code, and groups all the tables together so I know what data is where in the larger Buster Tests database. Quite interesting, I think.

For later

As I’ve said, all this scheming gives you a very clear idea of how the application will work and exactly what you’re going to have to write. Mainly this is because as you lay out the design for the tables, you must think about what code will deal with this data, and where abouts that will be in the whole scope of the application.

Despite the fact that I’ve made a rather good plan for how the application will work, the database schema actually helps me much more and gives me a much better vision.

Moving on

Well, now that the database is completely ready to be used by some PHP, it’s time to get a move on a code the app!

But before I do that I’ve got to sort out the ways the user is going to interact with the application. That is quite an important part of this process. BUT in the spirit of keeping the data, actions and UI separate, I’ll be doing my best to… well… keep them separate.

Right now, however, it’s time to go to another 18th birthday party! Happy birthday, Julia!!!