UNDERSTANDING YOUR DATABASE STRUCTURE

To begin with, let’s look at MySQL — the driving force behind eFiction. SQL (pronounced either Sequel OR ess-cue-ell; both ways are correct) is short for “Structured Query Language.” SQL runs what is called a “relational database.”

To keep it simple (and because we’re just talking about the user page), we’ll skip the explanation of “relational” in this tutorial. A database is basically nothing more than a way to store data. For example, you could consider a phone book or filing cabinet a form of database.

Inside each database, you have tables that help to sort the information and store it in a meaningful way. You could consider that the same as each file folder in that filing cabinet or the white and yellow pages of the phonebook.

The table we’re concerned with at the moment is the authors table.

The authors table is made up of fields that contain author information. Each field is assigned a name, a datatype, length, whether or not it allows for null values, and the default value. Some fields, like the UID, have another value that marks it as the “key” or “primary” field – which just means that field is supposed to contain a value that is unique to each member.

The name is pretty straight-forward. A unique, easily remembered name to identify each field.

The datatype is where things can get a little confusing. All a datatype is is a way to tell the database what kind of information we want to store in that field. There are numerous kinds of datatypes, but the ones we have to worry about are:

• INT: Short for “integer”, this is a field that only allows numbers. This is the datatype used to store your user’s UIDs, since those are just a sequence of numbers. By default, MySQL will store up to 11 characters for any field marked this way.
• VARCHAR: Short for “variable character”, this is a datatype that allows for almost anything. Letters, numbers, and special characters – anything is allowed. This is what is used to store penname, realname, e-mail, password, userskin, contact information, and all kinds of other stuff. By default, MySQL will store up to 255 characters for any field marked this way.
• TEXT: Just what it sounds like. This holds just a bunch of text. The bios are stored this way.
• TINYINT: Short for “Tiny Integer”, this is a much smaller version of the integer datatype. Basically, this means numbers. For eFiction, this is usually used with you only need to store a few numbers, usually no more than 4 characters. An example would be their user level or whether they receive new story notifications if they’re admin.
• DATE: Another datatype that sounds exactly like it this. This datatype exclusively handles dates and times. The format is: DD-MM-YYYY HH:MM:SS. The time is stored in 24 hour time, usually to your server time but you also sometimes find it in GMT. An example would be 29-06-2006 22:02:15; which comes out to 29 June 2006 10:02:15pm.
• CHAR: Short for “character”, this is another short datatype. Like VARCHAR, we can store almost anything in this, so long as it’s very short. This is used for beta reader, ageconsent, and favorite alerts.

(Interested in learning more about datatypes? The official MySQL website has a complete list.)

So where am I going with this?

If you want to add a new field to your user page, you need to store it in the database. In order to do this, you have to create the field in the database.

Knowing the difference between these datatypes will help you save space and use the database more effectively. Why use a field that allows HUGE amounts of data, when all you want to store is a yes or no answer?

Next up is length. Like I said above, sometimes there are limits as to how long a field can be. Sometimes while there is a limit, it’s so huge that you’ll probably never hit the ceiling. (For example, the limit on the TEXT datatype is about 65535 characters.) Try to hit for a length that will store what you need, without wasting extra space. I’ll cover this in greater depth later in this article.

Null is a fancy way of saying “Can this field be left blank?” It can either be set to yes or no. Sometimes it matters and sometimes it doesn’t. Most of the fields in our database say no, something has to be in it.

Default value just tells the database what to put in that field if there’s no data. A good example would be the date, which just contains the way the date is stored.

NOTE: When you delete something from a SQL database, the space is not freed up until you optimize the database. For most hosts, this can be done from either phpMyAdmin or your SQL control panel.