Re: Another interview question

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/07/13
Message-ID: <33C86C10.3FD2@postoffice.worldnet.att.net>#1/1

Very interesting analysis. One correction, though: the required solution has to prevent users from entering non-integers into the column, not change non-integer values already in the column into integers.

The solution I came up with (not at the interview, though): use the following CHECK constraint on the column:

ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> CHECK (<column_name>=TRUNC(<column_name>)) EXCEPTIONS INTO <exception_table>;

Using ROUND instead of TRUNC achieves the same result.

Impact on the application: add a few lines of codes to trap the ORAerror  message for constraint violation and display a meaningful message to the user (for most developers I worked with, this is optional). No index required, no disk space used up (except for the data dictionary).

Easy to implement (and as soon as possible): How good are your typing skills ? (mine are lousy ). Seriously now, you can implement it any time the table is not in use (late at night (??), or maybe I could have done it during the interview if I could have come up with the solution then) with no impact on production.

Flexible: If they have to accept non-integers later on, DISABLE or DROP the constraint.

Generic: implemented in standard SQL DDL.

BTW, did the thought ever cross your mind that maybe they pay you too much ? Never heard of a "very well-paid consultant" who would tell his/her clients that "it cannot be done". I thought the opposite was true: they always tell their clients it can be done, for the right price, of course (otherwise known as "bullshitting the client"). I am a DBA, and I just added your name to the list of people I would not want to have as a developer (hey, I just realized how long the list has grown !)

Michael Serbanescu

---

Bryan Dollery wrote:
>
> Michael Serbanescu wrote:
> >
> > "A production database table contains a column defined as NUMBER(6,2).
> > However, at this point in the life of the application using the table,
> > *only integer numbers* can be used (although this may change in the
> > future, hence the column format). All the values currently contained in
> > the column are integers. Can you, the ORACLE DBA, provide as soon as
> > possible, a solution to this problem, i.e. allow only integers in that
> > column ? Your solution must have minimal or no impact on the
> > application, be easy to implement, flexible and generic."
> >
> > Michael Serbanescu
>
> No.
>
> This is the only solution that fits the MUST HAVE requirements of:
>
> minimal or no impact on the application, easy
> to implement, flexible and generic
>
> If these non-functional requirements were dropped however, a trigger
> which truncated values to integers would do the trick. This would slow
> down performance of the application by a factor dependant upon the
> number of inserts into this column, hence violating the minimal, or no
> impact policy.
>
> Also a trigger to do this would be tied to the particular column or the
> particular table. Not particularly generic.
>
> This approach is however very easy to implement, and quite flexible. It
> also fulfils the functional requirement nicely.
>
> However, I still wouldn't do it this way.
>
> I'm a very well paid development consultant, I would never want to be a
> DBA ;-)
>
> Bryan
> ---------------------------
> Bryan Dollery BSc.(Hons).
> MD - ByteSmart Systems Ltd.
>
> ---------------------------------------------------------------
>
> Name: vcard.vcf
> Part 1.2 Type: text/x-vcard
> Encoding: 7bit
> Description: Card for Bryan Dollery
  Received on Sun Jul 13 1997 - 00:00:00 CDT