Choosing a field type, and what it costs to get wrong

When you build a table, every field is tempting to make text. Text accepts whatever somebody types, nothing is ever refused, and the table fills up.

The bill arrives later, when you try to total a column, sort by a date, or chart anything. A number stored as text is a label that happens to look like a number.

Pick the type for the question you will ask

The right way to choose is to ask what you will want from the field in six months, not how it gets entered today.

• Will you add it up or average it? That is a number, or currency if it is money.
• Will you sort by it, or ask what is overdue? That is a date.
• Will you filter or group by it? That is a select, with a fixed list of options.
• Is it yes or no? That is a boolean, not a text field containing the word yes.
• Does it point at another record? That is a relation, not the other record's name typed out.

Text is what is left: a name, a note, a reference somebody else issued. Those are genuinely text, and they are a small share of most tables.

Select beats text for anything with a fixed list

A status typed by hand becomes complete, Complete, completed and done within a month, and every one of those is a different value to a filter.

A select fixes the options, so a report by status returns four groups rather than eleven. It also tells a new starter what the options are, which is a piece of training you would otherwise give by hand.

A relation is not a name

Storing a customer as text gives you a customer who exists once per job. Change their phone number and you have changed it in one place out of forty, and a report by customer splits them across every spelling that was ever typed.

A relation points at the actual record. In Diract a relation field can link to another table's records or to a built in list such as your matters, contacts, properties or real company members, and the link is what makes the reverse question possible: every job for this customer, from the customer.

Let the field calculate itself where it can

A field somebody has to work out is a field that will be wrong. A line total typed by hand is a multiplication done in somebody's head under time pressure.

Where a value comes from other values, make it a formula: multiply, add, subtract, divide, or a percentage of another field. Where it comes from related records, a roll up can sum them, take the highest, or take the latest. Neither can be typed wrong, and both update when the numbers behind them change.

Currency is not a number with a symbol

Money has its own type for a reason. It formats the way the company expects, it is treated as money by the widgets that chart it, and it does not quietly drop a trailing zero the way a plain number can.

It also travels. A business working in more than one currency has a real problem the moment amounts are stored as bare numbers, and it is discovered at the worst point, which is a report that adds two currencies together.

Changing a type later is possible and unpleasant

It is not the end of the world to change a field type after the fact. It is a conversion, and the values that do not convert have to be sorted out by hand, which is every row somebody typed about instead of in.

That is the real argument for choosing carefully at the start. Ten minutes on the field types when a table is empty saves a day of cleaning when it has four thousand rows. Build a table for the thing you actually track covers the rest of that first decision.

What to do first

• Ask what you will want from each field in six months, then pick the type
• Use select for anything with a fixed list, so reports group cleanly
• Use a relation wherever a field means another record
• Make money currency, not a number
• Let formulas and roll ups calculate anything derived from other values
• Get the types right while the table is empty, because conversion is the slow way