PC Services

"From Payroll to Body Scanners"

Some PHP Tips and 'Tricks'

Thinking beyond each page and DESIGNING your web site

Bad database maintenance

Too often sites use database backends very badly, many adminitrators, developers and the like forget the first rule of computing -

GIGO - Garbage In Garbage Out!.

If the data or the structure is wrong in the first place, no amount of programming will get around the issues from calls and emails to support or customer services.

Database entries often lack -

• Consistent category naming or redirecting duplicate names
  Consider LCD monitors, these could described using one or more of these words -
  • LCD
  • TFT
  • Flat
  • Screen
  • Monitor
  Similarly consider memory sticks/USB pen drives/Flash drives/USB flash sticks...
  So be prepared to use phrase dictionaries or redirections for these search phrases as well as adding them all to keywords for the product and web page.
• RESTRICT who can update the database!
• Sanity checks on valid characters or other issues when loading data.
• Not User orientated - How many product categories are different for the same product from all YOUR suppliers?
• Consistent textual descriptions
• Consistent field types, don't be afraid of enumerations, bytes or even boolean entries.
• Use encoding tables, e.g. code numbers for product categories.
• Enable full text indexing for searching on multiple fields.
• Use consistent methods for sorting for reports, too often it is difficult to sort when fields contain too many parts of the information, for example full address as one field so you cannot sort by town or street. Another classic is anything involving measurements, seeing measurements done in ways that are difficult to sort, split measurements into amount and multiplier, that is what the SI units scheme was made for. Consider the following -
  • 1000mg
  • 1g
  • 0.001kg
  These are all the SAME amount, so always consider saving the above example as

  Amount	1
  Multiplier	1
  Units	g (gram)

  The units should be implied by the field in the database, and what the report is for. This becomes easier to sort, by multiplier first and then amount. Remember to use multipliers in 1000's then you can easily use the SI unit methods.
• Consider databases by years (to enable easier purging and server load for searches)

Whatever happens make sure you are willing to maintain the database, and if necessary have a procedure for agreeing what terminologies are used, and how to CHANGE them. Definitions can change or need to be restructured over time, make this possible.

See also next section Bad Reporting as this is also relevant.