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

In reply to Re: Re: Re: Re: Flexible Database Updates by grep
in thread Flexible Database Updates by jerrygarciuh

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.