Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Attribute-values in separate table

Re: Attribute-values in separate table

From: Authorised User <>
Date: Thu, 25 Oct 2007 13:01:54 GMT
Message-ID: <6t0Ui.4613$>

On Tue, 04 Sep 2007 00:51:51 -0400, Brian Selzer wrote:

> <snip>
> ...There is nothing wrong with having a lot of similar
> tables. Having a separate relation for each type of atomic fact is a
> good thing, because it simplifies the representation of information.

I'm not convinced there's nothing wrong with having lots of tables. Here's a specific example I'm thinking of: One failing project at my place records "peer esteem" measures for academic staff. There is one table for books published, one for papers published, one each for editorships, keynote speeches, and consultancy work etc. The disadvantage was because each table had a slightly different structure, they needed specific programming to handle each table. The information they need to record is for accreditation and, as the accrediting organisation is external, the requirements for what they need to store are driven by the external organisation, and very occasionally change. The (fixed size) development team is delivering the application in stages, so there are versions in Dev, QA and Prod. They found the burden of development/maintenance slowly increased in proportion to the number of tables (consequently development work slowed) until the development team couldn't manage the load any more. Now here's the killer – I looked at the information to be entered into the system (provided by the academics themselves, doubtless scraped from a personal web page) and it was given to me via an Excel spreadsheet: one column for what the academic did, and a second for the date! So they now have the EXTRA work of trying to sort that out, and then work out which table each line goes into. Such is life, they gave an empty database to the academics and told them to fill in the details.

In the successor project, I have a table called Activity_Type with OVER EIGHTY rows, which replaces all of that in one go – one row for each table they would've had if they ever got to the end of the job. In my system, Person_Institution is many-to-many and each row has an Activity_Type_Id, a free text description for most entries, and an XML document for some client-defined items that MUST be specified for some activity types only; I rejected the previous approach after and got as close as I could to the other extreme.

So, having lots of tables is good from a data-purity point of view... but spare a thought for the poor programmer who has to code for each table. One general table is far from perfect, and we will lose of a great many specific constraints that could help reduce data integrity problems... BUT we think we can run with it. In this particular case.

Newlan's Truism:

    An "acceptable" level of unemployment means that the
    government economist to whom it is acceptable still has a job.
Received on Thu Oct 25 2007 - 08:01:54 CDT

Original text of this message