Re: Re: Re: Flexible Database Updates

I think grep & I agree that using the array mechanisms
of RDMSes is usually a bad thing.
I disagree that "Array/extents will add unneeded complexity"
to your code period.

I do believe that fixed size arrays can be and often are
best put in a single record. Some examples are so often
done that they are invisible.

First, middle, and last- names are an example that could be

create table name (
   id   keytype,  -- constrained to parent
   seq   integer, -- place in name set
   name text
);
[download]

but who wants to deal with that?

The usage of address_line_1 and address_line_2 can be
viewed as an array embedded in a record.
Financial applications show arrays for the 12 or 13 months
of the year.
I used a pair of arrays to describe a set of items. There
could be upto 5 dimensions & 5 dimension_names.
All of these belonged in one record.

That the fullname or address should be one field is a
excellent alternative, but not always do-able.
Printing envelopes with a single address string may
be problematic.

Using a single fullname may induce the use of a nickname,
possibly recreating the initial issue.

The solution that I am currently implementing/trying is
to use arrays on the Perl side and separate fields on the
SQL side. A naming convention maps $o->{elem}[0] to elem_0,
and $o->{address1} to address1.

Comment on Re: Re: Re: Flexible Database Updates Select or Download Code

Replies are listed 'Best First'.

Re: Re: Re: Re: Flexible Database Updates
by grep (Monsignor) on Sep 19, 2002 at 04:32 UTC

I do believe that fixed size arrays can be and often are best put in a single record. Some examples are so often done that they are invisible.

This violates 1NF (Database Normalization First Normal Form) a field like that should be broken into another table by a relationship

eg.

CD          |Tracks
------------------------------------------
1984        |Jamie's Crying,Jump,...
Diver Down  |Little Guitars,Pretty Woman,...
Body Count  |Evil Dick,Cop Killer,...
[download]

This should end up being broken up into a CD table and a tracks table. Imagine a world where 'Cop Killer' was removed from the CD. Well now you have to retrieve that record parse it in to seperate entries remove the song then string is back together then do your update. As opposed to finding the song in the tracks table and removing it with one DELETE statement. You have now taken a one line operation and made it 2 SQL statements, a split, a removal and a join. You now have at least 5 lines of code to make a mistake in instead of 1.

So your solution is 5X's the amount code that you could've written, not too invisible to me (nor is it very Lazy).

First, middle, and last- names are an example that could be

create table name (
   id   keytype,  -- constrained to parent
   seq   integer, -- place in name set
   name text
);
[download]

This is not a good example. The 'name' field should follow your business rules. If you business rules state implicitly or imply that name should be broken into 'First','Last', and 'Middle' then your data should reflect that. If your business rule don't state or imply that then you should not break them up. Either way you have a distinct set of data you need to deal with.

Any time you split data out of a field, you fall into the trap of having your code do what your data model should support

These are just some of the reasons for database normalization, there are many others (including preventing update anomalies). If you follow the rules database normalization for your schemas, I promise you will write less buggy code

grep

Mynd you, mønk bites Kan be pretti nasti...

[reply]
[d/l]
[select]

Re: Flexible Database Updates

by rir (Vicar) on Sep 19, 2002 at 21:51 UTC

grep

imagine a world where 'Cop Killer' was removed from the CD.

I can imagine that, it wrecks your argument because we were
talking about fixed size arrays.

That your data structures need to support the requirements of
your business rules is true. But that is not an argument against
embedding arrays in a record, it would depend on the business rules.

I haven't recommended putting a fixed size array into a field.
I have suggested putting it into a set of fields. As an example:

create table acct_year (    -- G/L balances for a year
   gl_acct   varchar,
   year      int,
   bal_0     numeric(12,4), -- ending balances for qtrs
   bal_1     numeric(12,4),
   bal_2     numeric(12,4),
   bal_3     numeric(12,4)
);
[download]

my $acct_year = { 
        gl_acct => '100',
        year => 2000,
        bal => [ 9100.03, 783.87, 6339.26, 12.06 ],
};
[download]

[reply]
[d/l]
[select]