MySQL design patterns

January 7, 2011 — 2 Comments

This isn’t a post about optimizing databases or anything. I don’t do that, unless someone else has already figured it out. I use well supported open source and libre software like WordPress and Drupal. That means that hundreds of other people are optimizing the database for me. It also means that best practices for me mean using sane naming schemes.

I know it can be confusing when you first start using software that requires a database. I remember trying to be clever when I named things, and created passwords that were hard to guess but were somehow connected to whatever I was making it for. It always seems neat at first, but that wears off when you have to actually deal with those databases and passwords. So, what follows is just a few tips to help you cut a lot of future work out. It may not make sense, but you will get it, eventually.

The Big Four (plus one)

For most web software you will need to know four bits of information to install it. There may be other things, like installation paths or something, but as far as the database goes, you will need to know the hostname, database name, database user and database password. The other thing that software sometimes ask for is a table prefix.

hostname

The hostname is the server address of the database. This is used because sometimes the database is on the same machine as the web software, and sometimes it is on a different machine that the web server will connect to. For simple setups this will be localhost, which is to say the same machine.

When I am given the choice to choose my own sub-domain for a database server I choose “db”, as in db.interi.org. Other popular choices are “mysql”, or even “database”. Whatever you choose, stick with it. When it is 3AM and you are trying to restore a nerfed site, having to track down that address is a major pain.

database name

The database name is straightforward, it is how the software knows which database to connect to. This used to trip me up, but now I use a simple design pattern to name them. Since most of the software I use lives on a domain somewhere, I used the domain or acronym for the first part, and then I use the software’s function as the second part. For instance, my weblog’s database is interi_blog, whereas the forums’ database is interi_forum.

Keep reading, because I have a particular and easy way to create databases and users at the same time.

database user

The database user is like other kinds of user accounts, it has a password and a set of permissions that dictate what it can do to a database. Some complicated software requires this kind of functionality, but for most blogging/forum/wiki software, we only need a single account that has most or all permissions to a single database.

I name users the same way I name databases; in fact, I name them exactly the same, meaning that the user for interi_wiki is also interi_wiki. This makes it super easy to remember! Below I describe how I create databases really quickly, using this method.

database password

The database password is how the user is able to log in. This is pretty straightforward, but used to cause me the most trouble. I used to have this crazy scheme for creating partially meaningful passwords that were still random-ish, so I could log into the database later on.

Don’t do that.

Just randomly generate a password. The deal is this: you are hardly ever going to use it again. Most web software keeps it in a single text file, which means you can always look it up if you need, and you will never need to change it in multiple places. So relax. Below I will show you how to take care of all this stuff with no worries.

database prefix

A database prefix (the plus one) is what software will ask for when creating tables for itself. This derives from the idea that you can have multiple types of software all drawing from the same database. WordPress suggests you use wp_, other software will have different suggestions. Generally you can just use the default, or ignore this. Very rarely will you want to actually use a database with different prefixes, and if you are doing that you won’t be reading this.

In case you were wondering, one reason for using the same database to for two different web applications would be to share certain tables between them, such as user information. This makes it easier to enable single login functionality; i.e. using the same username and password to log into your blog and your forums.

In the past it may have also been required due to resource limitations. Some people only had one or two databases available to them. That really isn’t the case anymore, and now even the most basic web hosting plans have a lot of databases available. If your host doesn’t, they are antiquated and you should drop them as soon as possible.

Setting up a database

I am spend a lot of time in a commandline shell. However, I hate connecting to MySQL databases from the commandline. I don’t know what all the commands are, even the ones that I supposedly use all the time. Screw it. I use phpMyAdmin, a web interface that makes enough sense to me that it is usable. Of course you will have to learn the specifics of that software, but it is generally pretty easy once you learn just a few actions, which I am about to go over with you.

One user to rule them all

Instead of remembering all the different databases, users and passwords, I only track one. Sometimes it is root, sometimes you have to create a new user (for web hosted instances), but with just one user that have privileges for all the databases, I can remember one secure password and do all the work I need.

Seriously, this is awesome. Of course you need to remember the password, and it should be very secure; if it isn’t your life will be ruined and you probably shouldn’t be working with websites and databases.

This is also the reason you don’t need to remember passwords for all the different websites, you don’t need them to access a database if you have to.

Database creation

You may find that your needs are different, of course, but this is what works for me. This method allows me to get a site up and going nearly as fast as an automated script can.

  1. Log in with your privileged account
  2. Go to the Privileges tab; below the list of users is “Add a new User”, that is what we are doing.
  3. The first field is the username, follow a pattern: ex. interi_blog
  4. Hostname may be complicated, but if your database is on the same machine as your web server choose local from the drop-down; this will put “localhost” in the field.
  5. The password is super easy, click the button that says Generate right below it; copy that text somewhere, you will need it when you install your software, you can delete it afterward.
  6. Important: Before creating the user, in the next field group there is an option that says: “Create database with same name and grant all privileges”. Check that radio button, it will do the rest for us.
  7. Now hit the Go button somewhere near the button of the page. I know, it is kinda odd, but it is an indicator of what is happening to the database (you are running queries and stuff). Maybe one day it will become Submit and render this entire post invalid.

Pretty simple setup, one name, one host option, clicking a button and checking a radio button.

Now you have all your info. It will really pay off as your collection of databases scale, and if you spend any amount of time working on website, they will. Databases are like Pokémon, except not as interesting.

Conclusion?

I value my mindspace, and I don’t want to hold a bunch of useless data in my head on the off chance that I will need to log directly into a database for some kind of emergency work. Instead, I would rather be clever, make backups compulsively, and employ Vancian methodology to my databases: study, fire and forget.

If you have any other tips to share on making your life easier with databases, please share them in the replies. If you have any problems with what I’ve written, especially from a security standpoint, please, seriously, leave a comment.

Enjoy!

2 responses to MySQL design patterns

  1. Oh thank you! I know we have done this post by voice at least twice. It is nice to see it in letters. The whole process seems simple enough but once you start working with multiple databases I can imagine this will come in handy.

    • I am glad you found it helpful!

      This was all so weird to me when I first started installing web software. I feel like a jaded lazy hack, but it actually comes from actual experience. When you make up your own passwords, they will never be as strong as the randomly generated string in phpMyAdmin.

Leave a Reply

*

Text formatting is available via select HTML.

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>