You’ll find a lot of MySQL tutorials go straight to the command line. While there’s a lot that’s right about that approach – the command line is precise, and always works so long as you type commands correctly – its precision and bare-bones interface is also intimidating to new users. Instead, here I’ll do a light, basic introduction to creating a database table via phpMyAdmin.

First, you have to set up a database to contain your table. The name of this database can be almost anything, but should follow standard web naming conventions, with the addition that you should avoid underscores in the name. Click on the Databases tab and enter the name of the database that will contain your tables.

“Collation” is essentially the default character encoding for the database and any tables stored inside it. As a general rule, we should use the same encoding we use on everything else (HTML, CSS, files): utf-8. In phpMyAdmin, there are a bunch of options for this: we’ll stick to utf-8_general_ci.

With our database created, it should appear on the left sidebar of phpMyAdmin. Click on that database entry.

We can now create a new table inside of this database. Remember that tables store values in fields, so the first thing we must determine, after determining our table name, is the number of columns that our table has. (While we can add or modify columns after this point, things are a lot easier if we get this step right at the beginning.)

How Many Columns Do I Need?

For a basic table, this is a fairly simple question to answer: the correct question to ask is “What is the maximum number of discrete pieces of data that I will need for a record, that belong in a single table together?” Another way to think of the question might be “How many ways do I want to sort this table?” For example: making a database table for user records that only records a person’s full name in a single field will make it very difficult to sort by last name, so it would be better to make two fields/columns that separately held the user’s first and last names.

If the database is to be filled from a form, the answer to this question is even simpler: the number of columns in your database table is always the number of inputs in the form plus at least two. That is, a text input will require one field in the table, a drop-down option another, a group of radio buttons another, and so on. Add them up, and then add two to the final total, for reasons we will explain shortly.

Whatever number you come up with, enter this in the number of columns entry area and press Go

Database Table Management

Seeing the phpMyAdmin table creation screen for the first time, you are confronted with what appears to be a bewildering array of options. In reality, it’s not so bad: you just need to keep a few things in mind.

MySQL is always trying to achieve three goals simultaneously:

• arrange the table so that data within it can be organized optimally.
• limit the amount of data that can be put inside a table field (limiting the growth of a table, as the faster a table grows the slower it will respond to queries).
• do some basic validation of the data that enters a field (so that text does not go into a field that expects a numeral, for example).

Your first field is key, literally. The first field should be something that makes each row uniquely identifiable. Words by themselves are not enough for unique identification: many people have the same name. Usually, this key is a unique identification number or code: for a product, it might be a SKU or barcode number, but for most everything else it could be a simple unique integer.  We indicate that this will be our primary key field by choosing just that option. (The name will usually be something like id).

The size of the integer we use for the identifier is important. By default, MySQL uses the int type, which has a limit of integers between 0 and 4,294,967,295. In other words, you could uniquely identify a little fewer than 4.25 billion rows in your table. That’s likely too large, but neither do you want to use TINYINT, which can only store numbers between 0 and 255. SMALLINT (between 0 and 65535) is plenty for most purposes, so we’ll choose that. (Again, we change this later if needed). We’ll also set this field to AUTO-INCREMENT (the checkbox under A_I), so that we don’t have to enter it manually: the first record we enter will automatically be identified as row 0, the second and row 1, and so on.

Second, we’re going to make a field to store a user’s first name.  The easiest way to do this is to reference the form that we’d make to enter information that would be stored in the database, and keep in mind two simple rules:

• Form inputs that are type text are almost always stored as varchar in the database, and…
• …whatever the maxlength of the input is in our form should also be the limit supplied for varchar (which makes sense: there’s no point in trying to make more room in the database for characters that the user cannot possibly provide. Nor would making the number of characters stored in our database field less than the maxlength of the input make much sense: MySQL will happily clip text information that doesn’t fit inside a varchar field, leaving us with incomplete data).

The same would be done for the user’s last name, etc. I usually find it easiest to match the name I give the database field to the value of the name for the associated input.

If we had a textarea in our form, the associated field type in our database would be TEXT. The limits match those for INT: TEXT holds a little over 65,000 characters, TINTTEXT 255 characters, etc.

A few other types that are handy:

• If you are recording the price of anything that includes fractions of a dollar, use DOUBLE as the input type (i.e. double decimal notation, such as 33.93). Adding a dollar sign is not necessary.
• If you know that the field can only have one of a limited number of values (such as provincial information for location of birth) you can use a SET or ENUM field to define the values that are acceptable.
• A TIMESTAMP field is very useful for recording when a record was made or updated. TIMESTAMP will be automatically filled out by the server: it, along with the primary key field, are the two fields I suggested adding to your calculation above.

Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.