Monday, March 06, 2006

When is a schema a squema ?

Squema : (noun) a database schema that provokes nausea, disgust or fear in developers and/or DBAs. [from Latin 'schema' and Middle English 'squeamish']

Okay, it's not a real word, but it was prompted by a typo/spelling error in a post in the XE support forum.
What structures will put shivers down your spine when you first set eyes on a database schema ?

  1. There's only half a dozen tables, the largest of which has just three columns similar to 'Entity Id','Attribute Id' and 'Attribute Value'. Every query in this database will include forty-two joins.
  2. Lots of columns defined as CHAR rather than VARCHAR, all constrained to NOT NULL. A sure sign of a generic product designed to be database-independent.
  3. Lots of columns names like USERDATA..., USER_DEFINED..... or similar, so the column names give you no idea what the contents are. Normally the sign of a third-party product. You can guarantee that there won't be any practical constraints and that, at some point, the wrong data will find its way into the wrong column.
  4. Columns defined as VARCHAR2, but with names suggesting they contain dates/numbers or, in conjunction with 'generic names', just data values that are suggestive of dates and numbers. Look forward to a life of invalid date and value errors.
  5. Spelling mistakes in table, procedure or column names. I once worked with an application that included a table column "proccess_status". A giveaway that some steps are missing from the review process and bound to trip up programs written in languages that don't validate SQL syntax and semantics at compile time.
  6. select * from user_constraints where constraint_type = 'R' gives no rows selected. No foreign key enforcement, no data integrity, no data found....
  7. select * from user_constraints where constraint_type in ('P','U') gives no rows selected. Trust me, the application will enforce it. Yeah, right, with the help of the tooth fairy.
Anything else which makes you squirm ?

1 comment:

Robert Vollman said...

I've met people that think of the database as nothing more than a place to dump data. They're experts in Java, see, so they can write their own code to manage relationships and enforce constraints. Now they're "database independent" even though they're not actually using any other type of database.

Anyway, that's where a lot of squemas come from (love that word!). A database application team without anyone that actually understands databases.