Re: Attribute-values in separate table

"Authorised User" <bg_at_microsoft.com> wrote in message news:6t0Ui.4613$CN4.2731_at_news-server.bigpond.net.au...
> 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.
>

It is a truly poor programmer indeed who would trade off data integrity for a faulty expectation of reduced work. If there are eighty types of atomic facts, then whether you're coding for one table or for eighty, you still have to code for eighty types of atomic facts. Is it really less work to lump it all together?

> --
> Newlan's Truism:
>
> An "acceptable" level of unemployment means that the
> government economist to whom it is acceptable still has a job.
Received on Sat Oct 27 2007 - 09:02:51 CEST