Representing Statuses

When I am doing system Design, there are some recurring problems. One of the problem is to represent status of certain records. Take an example of a transaction table.

Solution #1: Saving the actual name of the status as is.

Transaction
id	user_id	item_id	status
1	456	416	pending
2	425	416	processing
3	1	211	completed

So, originally there are 3 statuses. Easy enough? Software is a continuously evolving process and now the requirement changes. There will be another status called “contact CS”. Or there will be changes naming, such as “processing” is renamed in to “accounting”. What if there are 5 different classes which have the same query criteria of looking for transaction records with “processing” status? What if there is a select box which show all statuses?

SELECT DINSTINCT(status) FROM transaction

is a very expensive procedure.

Solution #2: Separate status and actual record into 2 tables.

Transaction
id	user_id	item_id	status_id
1	456	416	1
2	425	416	2
3	1	211	3

Transaction Status
id	name
1	pending
2	processing
3	completed

This has a lot of advantages over solution 1. Firstly the query of all statuses can be done quickly, as the procedure speed is proportional to the size of status table. And growth of status records must be less then transaction records. Secondly the status_id column can be indexed. Double speed boost is guaranteed. But it is still not the perfect solution. Our average Gilbert is always lazy in writing programs. When he is assigned to implement a function to query all pending transaction. I am confident he will write this SQL query:

SELECT * FROM transaction WHERE status_id = 2;

And the maintainers are doomed with a non-descriptive 2. Improved Average Gilbert is enlightened by associative array and changed to:

$status = get_status_map();
...
SELECT * FROM transaction WHERE status_id = status[''pending''];

This is also not very helpful. As some of the time we needed to display the word, for example in the view, and sometimes vice versa. We need the key to be the lock and the lock to be the key. Or a more technical analogy would be the relationship of asymmetric key encryption and public key signature.

And More Improved Average Gilbert changed get_status_map() to use name as key and added a parameter for get_status_map(‘full’) to add id into the value. He didn’t celebrate for long. He received yet another feature request to support multiple languages for the system as a mainland China customer and a Vanuatuan customer are using it.

Solution #3: Put the status name as the key for i18n language library.

In the view:

echo i18n.get_string(''status[''pending''][''name'']'');

In my limited experience and intelligence, this should be the best solution for the problem. It has the speed of the solution #2 and has more flexibility because the presentation and model class is separated. The customer can change whatever status and the developer only needed to change the language config files and the database to support such changes.

Do you have a better idea?