I’ve often had discussions with people about whether the “enum” type in MySQL is a good thing or not. Basically there are two ways to use your database:

1. As an unstructured bunch of “stuff” to store whatever the software needs to persist. Such databases use lots of “blob” data with serialized objects (it’s easy to program), tables with multiple functions (“object” table with “type” column), few constraints, and so on.
2. As a representation of the data the program is trying to model. Such databases have meaningful column names, two different types of things are two different tables. Adding constraints is easy.

There are good arguments for both. As one is programming in a programming language, whatever is most convenient for that programming language (e.g. serialize a bunch of objects to a blob) means less effort, less code: which means less cost and less bugs. Those are all good things.

But for some reason I’ve always been the 2nd type. I like to look at a database and understand what data is being modeled. It creates a certain self-documentation which can often be lacking from complicated software. Constraints can be added which acts like functional assertions (functional in the sense that they involve no state: you say that this value must be between 1 and 10 and then it is that way. You don’t have to program any “path” or state to check that).

That an item can be in exactly one a distinct set of states is a fact of life in all types of domain modeling:

• What state is this invoice in? Is it “paid”, “open”, “paid+processed”?
• Is this item deleted or not? (Or marked temporarily suspended, pending administrator checking the content)
• Is this photo public, or does it belong to a user?

To explicitly work with enumerations is to acknowledge their existence within the domain, and to create a more 1:1 mapping between the domain and its implementation. That’s what all software should be about, modeling. Ideally a program is nothing more than a model and a few rules for how that model transitions between one consistent state and another.

If you program databases like #2 above, like I do, then enumerations really do make the data model richer.

• The database maps to the domain easier (thereby documenting the domain, in case this is not done elsewhere)
• The database maps to a domain-consistent implementation in the programming language easier
• The database implicitly then has a constraint, as you can’t set the column to be some value which makes no sense for the domain (and therefore the program)

So enums in DBs are a good thing, and should be used wherever possible. Oracle should implement them just like MySQL does. And enums should be used in programming languages too. Why enums appeared only since Java 1.5 I don’t know. And the fact that php, perl etc do not support them, is yet another reason not to use those programming languages to try and attempt to build any software system whose function it is to model a domain.

It can be said that then it makes it “more difficult” to add a new state, as now you have to “change the database as well”. But if the model changes, and the database reflects the model, then that’s a good thing, not a bad thing. And it isn’t even much effort: If you say that invoices can be in a new state, then there’ll be a lot of programming work to support that change (UI, billing logic, robots, test scripts). The “alter table” statement is no work at all proportionally, and you’ll also be thankful for every extra constraint the database or programming language can offer you (which such a change feels very scary in a Perl program, but not so much in a Java program).

(Enums are supported in PostgreSQL since v8.3)

There are times when one need to prevent a certain “critical section” of code from being executed by more than one process on the same object at the same time. For example, if the requirements state that a user cannot have two subscriptions of the same type active at the same time. So to enable a subscription, if one does “a) check if user can have the subscription, b) enable the subscription”, one need to make sure that there aren’t multiple processes doing that simultaneously.

Oracle (and thus MySQL InnoDB and PostgreSQL) support row-level write locks. This means that (in contrast to other databases) if one database connection, in a transaction, writes to a row (but has not commited yet) then other connections can freely read the row: they get the version which was most recently commited, which was the version before this transaction. Only if a second connection tries to write to the same row, will the connection wait for the first connection to perform a commit or rollback.

The “select for update” statement acts like a “write” above. All rows which are returned from the select to a first connection are not being written to by any other connection, and any other connection which later attempts to write (or “select for update”) the rows will wait until the first transaction has commited or rolled back. Locking rows using “select for update” is thus an appropriate mechanism to implement locking.

As “select for update” still can only lock existing rows, one needs to decide what the appropriate row(s) are to lock for a particular operation. Remember that the rows are only locked to other connections using “select for update” or writing to the row: anyone can still read the rows. If one wishes to exclude the possibilty of a user having two subscriptions of the same type simultaneously, one cannot lock the “zero rows” the user must have in the subscriptions table beforehand, as only existing rows can be locked.

It thus makes sense to lock the “user” row representing the user (or some other table containing exactly one row per user).

Therefore, for certain critical operations, such as adding a new subscription line for a user, I shall perform the following operations:

1. Open transaction
2. “Select account_id from account where account_id=?”
3. Does the user already have a subscriptions row of this type currently active?
4. If not, create the new row for the user
5. Commit

Note that with other database engines, simply reading the row within a transaction may be enough for it to acquire an exclusive lock. Or worse, for the entire page (Informix?) or entire table (MySQL MyISAM?) to acquire an exclusive lock.

And even if it’s just the row being locked (best case), other reads (e.g. a user just wants to view some data) will cause the reading process to block, increasing the number of processes waiting from just those needing to wait due to execution of a critical section, to all processes accessing the table.

The way numbers are stored in C and thus in PHP, Perl etc., and also Java, is with a binary system. So if you have 2 it's "10" in binary, 5 is "101" and so on. And the same is true for fractions. So a half is "0.1" and a quarter is "0.01". That means that just like "one third" is not exactly representable in our decimal system (0.3333) numbers like "one hundredth" which are easy to represent in decimal cannot be represented exactly in binary.

Oracle and the MySQL "numeric" type stores data as decimal. Meaning if you store "a third" to two decimal places, they get stored as "0.33". And if you try and add 3 "one thirds" together you get 0.99 not 1.00.

So those Oracle/MySQL data types, using decimal, are good for representing money, as you can exactly store "one hundredth". And adding 100 "one hundredths" gives you 1.0 exactly. However that doesn't help one much, as all programming language in common use today only support a binary floating-point representation – which can't store "one hundredth" exactly.

This is more of a philosophical issue than a piratical one. Because even if one does add 100 "one hundredths" together, one gets a result like 0.9999 and if one tries to print that to two decimal places, then rounding will take place, and "1.00" will be displayed, i.e. the right answer. So it's not really a practical problem.

However, the solution to the problem is easy to implement: just store a whole number of cents, pence, etc. So there's no reason to accept any inaccuracy when it comes to storing monetary amounts.

This was the way my Mother programmed, when she had to deal with pounds, shillings and pence in the old UK pre-decimalization monetary system: she simply stored the number of pence as an integer. If it was good enough for her, it's good enough for us now. After all, software development doesn't change that much over the generations.