Tag: database

I've been doing a lot of database stuff lately, and not much PHP, so when I returned to make my first Laravel project for a while, I had to check the docs to remind myself how some of this works. I noticed that the default approach to database credentials is still to use separate credentials for the host, port, and other variables. I'm using Aiven databases (because I work there and managed databases are great for demo apps as well as real ones!) which supply connection strings, but Laravel supports these too.

I wrote recently about deployment and I got some followup questions about managing database changes when you've got multiple versions of code hanging around.

There are two things you need to come to terms with:

• There is no silver bullet

• Rigid process is required

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

Someone will probably tell me that this is an elementary-level topic, but I got some good questions regarding joins from my most recent ZCE class students, so I thought I'd put down the examples that I used to explain this to them. Being able to join with confidence is a key skill, because it means that you can refactor and normalise your data, without worrying about how hard something will be to retrieve.

I'm enjoying working with MongoDB but as with any new technology, it can take a little while to find your way around all the tools related to that stack. In particular, I found myself wondering how do I mysqldump for mongodb?

It should have come as no surprise that the command I wanted was called mongodump, really!

The MySQL explain plan is a great tool to help developers and database administrators to improve the performance of specific queries happening against a database. It is very easy to use, but its output can be confusing, so I thought I'd show a very simple example.

I love PHP's PDO (PHP Data Objects) extension; it gives a consistent, object-oriented interface to handling all kinds of relational database backends. One thing that annoys me is that the MySQL driver for PDO defaults to a silent error mode which can make SQL errors tricky to spot!

When I meet a new technology, I like to experience it "just as it comes". I'm happy at the command line and I like to type actual commands and see man pages before I use any wrappers or helper tools. So when I met MongoDB for the first time, I did exactly that. This post shows those first steps of creating a database, and inserting, reading, deleting and updating data.

I've written before about a simple way of patching database versions and there's a much more comprehensive article from Harrie on TechPortal as well. I often find though that projects with patching strategies are missing the scripts to apply these automatically when the code is deployed, so I thought I'd share mine.

My current project (BiteStats, a simple report of your google analytics data) uses a basic system where there are numbered patches, and a patch_history table with a row for every patch that was run, showing the version number and a timestamp. When I deploy the code to production, I have a script that runs automatically to apply the patches.

When I work on database designs, either on my own projects or as advisor to others, I often find people very reluctant to use an enum type for any columns. Now, I'm not about to advocate the gratuitous use of an enum column, they definitely have some pitfalls, but I think it is important to understand these rather than just shouting "enum evil!" if anyone should mention them.

There are cases where an enum is the correct choice for a particular type of data, so let's look at what an enum type actually is and does.

Increasingly I find a very binary split between the professionals I come into contact with. One group of people are very database-aware and take the design of their storage quite seriously - with good results. The other group are more concerned with the functionality of their application, and have little regard for how it is stored other than considering it a keeping-place and making useful table and column names.

Too often though, they don't think about how that data will be retrieved or what the implications are when it gets beyond the thousand records that were used for testing. This is where having an idea of how the data will be retrieved can really help application performance. (note: this article is aimed at users of traditional relational databases, and ignores all other possibilities). This post takes a look at the various index types and when to use them.

Perhaps this is more of a rant than a post but I do keep running into issues with databases with names that are inconsistent - which makes them really difficult to work with. When designing a database, there are a few points to consider:

One problem that seems to need solving too often is how to keep databases in sync across different platforms, different developers working on a project, and deploying database changes along with code changes. There are lots of ways of approaching this, none of them are really excellent however and personally …