in reply to Re: Re: Re: Flexible Database Updates
in thread Flexible Database Updates

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...

Replies are listed 'Best First'.
Re: Flexible Database Updates
by rir (Vicar) on Sep 19, 2002 at 21:51 UTC
    grep and I don't seem to be on the same page. Blame me for that,
    I am the one who made the unusual statements.

    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]
    On the Perl side I have found an array to be the best representation
    of this info. Best so far, I'm still ready to refactor everything. 
    my $acct_year = { 
        gl_acct => '100',
        year => 2000,
        bal => [ 9100.03, 783.87, 6339.26, 12.06 ],
};

    [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom