Video, State of Free Software

Listen to this video of Eben Moglen's presentation on the State of Free Software.

Open Database Conventions

I've been using a set of database table and field naming conventions and related standards that have been gradually adopted and refined over the last 10 years. I decided today that it would be a good idea to share these with others, particularly because I decided to bring Jim from UVOG in on a database project I'm starting on. I realized that sharing them with others on an even wider scale might be a good thing on the off-chance that, if someone else out there adopts them, and if our code ever ends up crossing paths, we will gain greater interoperability and understanding on account of using the same techniques.

Feel free to take some or all of these ideas and implement them in your own projects. I am willing to hear feedback and incorporate it back into the standard if you think there's a better way to do something that I've mentioned.

Some of these conventions will apply specifically to MySQL, while others will apply to the greater spectrum of databases. My experience spans MS Access, MS SQL Server, dBase, FoxPro, the Apollo Database Engine, and MySQL, with some very light experience in SQLite and PostgreSQL, but the bulk of my work today happens in MySQL.

Naming the Database

The name of the database itself is the least of my concerns. I would just recommend that in code that operates with or upon the database, this value should be easily configurable along with the hostname, port, and authentication credentials, so that any database could be selected.

Naming Tables

The name of a table should be a succinct name descriptive of what is to be represented by a single record in the table. If each record in the table represents an account, for example, name the table "account" (in the singular, all lowercase.) The reason we use singular forms is so that dot notation naming of fields makes sense when read out loud, for example, account.balance. There are many reasons we use all lowercase. Most production Database servers are case sensitive, and by using all lowercase we can eliminate the possibility of an error relating to case. It also improves code readability when SQL statements are written out with keywords and function names capitalized and with table and field names in lowercase.

There is a special case of naming in the event of a junction table. (A junction table is used to create a many-to-many relationship between two tables, and consists of nothing more than an id field, and foreign keys for the two tables whose records it is tying together.) The name of a junction table should be the name of the other two tables mashed together, with the name of the greater entity first. For example, if I have a company table and an customer table, and customers are potentially associated with multiple companies, I would name the junction table companyemployee, and it would contain id, kcompany, and kemployee, in that order. If there is a toss-up in determining which entity is "greater," place the two table names in alphabetical order.

Naming Fields

Always name your fields as though they are going to be used in dot notation. We want account.balance, not account.accountbalance, so do not redundantly repeat the table name within the field name. Avoid abbreviations that seem like a hack. For example, number is better than "no" or "num." Widely used abbreviations like ipaddress, ssn, or id are ok--be particularly careful to avoid expanding acronyms that are not expanded in common speech.

The Primary Key

In order to be properly maintained, every record needs to be addressable by a unique identifier. This identifier should not be meaningful in any real-world way for reasons that are explained in depth elsewhere, but I will give one brief example. If you were making an employee database, you might be tempted to use a person's SSN as the primary key for the employee table. Three years after the program is written, your company might hire someone who is in the country on a work visa, and therefore does not have an SSN, and now your system is broken. One will follow this line of thinking out and conclude that the key should be completely arbitrary and meaningless so that no such conflicting situation may ever occur. Additionally, for the sake of consistency, the primary key field in each table you make should be of the same name and type. I recommend naming the first field of each table "id", setting it to "auto_increment," and setting it to become the PRIMARY KEY index.

The value of this id field should be used internally for programming purposes and for describing relations between tables, but it should never be shown prominently to the user. If it is shown, it is merely for debugging or reference purposes. The number in this field, once generated, should not be changeable by the user, is not guaranteed to be sequential with the records around it, and is not guaranteed to fall into any range of values more specific than those allowed by an int (11) field.

Foreign Keys

A foreign key can be thought of as a "pointer" referring from the current record out to another record in another table (or possibly another record in the same table.) All foreign keys should be prefixed with the letter "k" (meaning key), and should either be named "parent" if the key is referring back to the same table for the purpose of producing a hierarchical system, or if referring to another table, it should be given the name of the table into which it points. If more than one key in a record must point to the same table for different purposes, the purpose should be appended to the name followed by an underscore. For example, kaddress_from and kaddress_to would be pointers to two records in the table named "address." The relation to one of them is described as "from" and the other as "to." This is the only time an underscore is permitted to be used in a field name, and the purpose of the underscore is to make the table name unambiguous.

When possible, try to put keys to parent records or records which are considered to "own" the current record near the top of the field listing, following just after id.

If these standards are followed for the primary key and foreign key fields, I could look up any reference using the following technique:

Given a field whose name begins with "k": Take everything in the field name following the "k" up to the end of the name, or up to (but not including) the first encountered underscore "_" character, and consider this to be the "lookup table name," if such a table exists. Retrieve the data by issuing a query in the form of:

SELECT * FROM [lookup table name] WHERE id = [value of the field]

Choosing Appropriate Data Types for Fields

Always choose the most restrictive type that can safely store the data, including valid values that you might not anticipate.

KEY FIELDS

For id fields or foreign key fields, use INT (11)

For boolean (yes/no or true/false) fields, use TINYINT (1) A value of zero means false, and any nonzero value means true. You can distinguish between individual nonzero values for record housekeeping if you wish, as long as others reading the data as merely true or false will not be under a misconception from not making such a distinction.

CURRENCY

For currency values less than a million dollars, use DECIMAL (8, 2) --- increase the first size by a sufficient number of digits if you need larger currency values.

NUMERIC DATA

For numeric data involving whole numbers or integers only, use INT (11) --- unless the size is astronomical, in which case, you will need to upgrade to a larger type.

For weights or non-integer quantities less than ten thousand, use DECIMAL (8, 4) --- increase the first size if values beyond ten thousand are needed, or both sizes if more precision than four decimal positions is needed. Four decimal places was chosen so that a number like 12.05% could be fully represented as 0.1205.

DATES AND TIMES

For date or time values or stamps where the "time part" has meaning (particularly in sorting), use the DATETIME type. A value of "0000-00-00 00:00:00" means not entered or unknown.

For date values where the time is irrelevant, use the DATE type. A value of "0000-00-00" means not entered or unknown.

MULTIPLE CHOICE

For multiple choice values: If there are a discrete number of choices presently and in the near future, few choices (about 8 or less), and little or no benefit would be had by allowing the user to customize the set of choices, an enumerated value may be used. Example: alignment ENUM ('left', 'right', 'center') DEFAULT 'left'

For multiple choice values where the choices are numerous or user defined or potentially user managed, a foreign key and a lookup table should be used instead of an enum.

STRINGS OF TEXT (NO MULTI-LINE VALUES)

For any string data: If the options are very well established, choose the smallest size category from the table below that can hold all possible values, or for data that is free-form, choose one category larger than you think is really necessary. I will give some example with each size category:

VARCHAR (4) -- A standard name suffix like Jr., Sr., II., III.

VARCHAR (8) -- Data known for certain to be less than or equal to 8 characters.

VARCHAR (16) -- A zip code. (Minimum to store a US zip code is presently 9 digits plus one hyphen.)

VARCHAR (24) -- A phone number, with decorations. The following number is 23 characters long, including spaces: +1 (541) 375-0448 x8888

VARCHAR (32) -- Product codes, SKU numbers, or generated numbers that are guaranteed to be less than or equal to 32 characters.

VARCHAR (48) -- A first(given) name by itself, or a last name(surname) by itself.

VARCHAR (64) -- A full name field (first and last name together.)

VARCHAR (128) -- An email address.

VARCHAR (248) -- A web address.

PARAGRAPHS OF TEXT, NOTES, MESSAGE CONTENT, CODE SNIPPETS

For anything multi-line, or potentially longer than 248 characters, use the TEXT type. If the data or text will be more than a couple of kilobytes, investigate the LONGTEXT type.

IMAGES OR BINARY FILES

I don't like big files taking up space in my InnoDB tables and slowing down my replication server. Instead, I store the original filename only (and only as a convenience) in a VARCHAR field, and then save the file separately named based upon the table name and record number the file is associated with. If I need to replicate these files, I let rsync take care of that. If the "attached" files MUST be synchronized with the other data in the record at any given moment, then it might be acceptable to resort to BLOB fields.

Order of Fields

When creating tables, the order of the fields really doesn't matter to the database engine. The sequence, therefore, should be chosen for purposes of clarity in documentation.

Well, that's all I have for now. Have at it, tear it up, and send me suggestions.

Migrating MySQL Data

Its time to migrate a copy of the MySQL data so that I can begin testing the websites running on Pericles in a closed environment before I let this out into the wild.

sudo /etc/init.d/mysql stop

The data lives in /var/lib/mysql

I'm cool with that, but I'm going to replace it with my existing data, so first I'll rename it to preserve the original:

cd /var/lib
sudo mv mysql mysql-original
sudo mkdir mysql
sudo chown mysql.mysql mysql

Next, I unpack my MySQL backup, which was archived from my replication slave a couple days ago. Since I'm going to make this into the new master, I'll first use this data as a testbed and afterwards I'll replace it with a fresh copy when I'm really ready to switch masters. My data is currently about 1 gigabyte, which shrinks to 111 megabytes when compressed, so it takes about five minutes to copy and longer to unpack. I should point out that innobase and myisam data on Linux and Windows are "binary compatible" so they can be copied directly across the board, but you need to make sure your mysql configuration files have the same innodb settings in them, or else. Oh yeah, I should also mention that the MySQL version numbers are both very close, and up to date, so the fields in the user tables and so forth are the same format. I wouldn't recommend migrating a MySQL 4.0 database to a MySQL 5.0 environment using this technique because the user fields changed a bit.

I deleted the master.info file so that this server won't try to connect to the replication master like the replication slave from which it was copied was programmed to do. I'll configure it to be a replication master once I do the real thing.

I had to change my bind-address=0.0.0.0 in /etc/mysql/my.cnf in order to get the MySQL to bind to all network interfaces (I need to be able to connect from several networks.) This is ok, because the mysql users table has restrictions on which users are allowed to connect from which hosts. I wanted to change it in /var/lib/mysql/my.cnf which seemed to be recommended by the heading in /etc/mysql/my.cnf, but copying the cnf file and changing it there did not make any difference, so I resorted to changing the /etc/mysql/my.cnf version of the file.

Starting up MySQL I see this:

error:  'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

Now I need to add back in the debian-sys-maint user that I lost by overwriting the user tables. This is easy, because they leave the random password sitting unencrypted in /etc/mysql/debian.cnf

I did this by logging into MySQL from the command line interface as root, and executing the following commands:

USE mysql;
CREATE USER 'debian-sys-maint'@'localhost';
UPDATE user SET password = PASSWORD('PasswordCopiedEarlier') WHERE user = 'debian-sys-maint';
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost';
FLUSH PRIVILEGES;

I now stopped and started the MySQL server:

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

This worked like a charm; I no longer see the error message, which means debian-sys-maint can do its magic.

WAMP to LAMP

I'm starting a series to document my progress on converting a very specialized WAMP server over to a LAMP server.

WAMP = Windows, Apache, MySQL, PHP
LAMP = Linux, etc.

I am going to name this box Pericles (Mostly for the sake of giving it a tag in the blog so that you can read all about its life by clicking Pericles here or on the sidebar.)

To begin with, I want to explain why I was using a WAMP server to begin with. Most people either go all Microsoft or all Open Source. This server was born to fulfill one pressing need: A friend of mine had a website already written in ASP that he needed a new hosting provider for, and neither of us had time to deal with anything as serious as a rewrite at that time. So, we found a neat plug-in for Apache on windows that allowed it to execute ASP code. This worked, and our small Web-hosting business was born.

Fast-forward two years.

We now host around twenty websites on this box, and the need for ASP is gone because the original site has been rewritten in PHP. Furthermore, the specs on that box were outdated when we started, and it is time for a faster CPU. We bought a new system at the Day After Thanksgiving sale and we are now ready to go Open Source.

We've installed Ubuntu 6.10 (Edgy Eft) from the Server disc, and then added the ubuntu-desktop package manually to get a GUI desktop. We need the GUI desktop for a couple of reasons: 1) We're new at administering Linux and it helps us feel a little more confident. 2)
I've developed a few tools over the past two years that handle frequent back-end tasks as Windows applications. I don't have time to port them all at once, so I am going to have to do it piece by piece, thus I will run the unported tools by using Wine in the meantime.

Once I got Edgy booting smoothly and configured for our graphics card (it required a resolution tweak using dpkg-reconfigure xserver-xorg to get it looking correct on our LCD) this is our official starting point.

To follow our ongoing drama, click on the Pericles category/label in the sidebar.