Re: Store in one database column an array is a bad practice.

On 18/08/05, Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com> wrote:
> Hi,
> I think to store in one database column an array is a bad practice.
> for example store in one colmun the person name, age and professoin, is wrong,
> it should be another column and/or another table, when a multicolumn
> array is stored in a column.

I suspect it's one of those things that's a bad idea in some cases but a good idea in others. It's a question of making sure that when you use them you do so with a good reason, not just because you think they're cool or assume that because they're new(ish) they must be better.

It's like XML data types. I know people who want to use them for anything cos, well, they're XML so therefore must be better (i.e. they're more buzzword compliant). The fact that XML is crap for the things they're using it for passes these people by.

Arrays would be a bad idea for the example you cite, for one thing those items would be different datatypes/sizes. Also, why would you want to store different names, ages and professions for a person? I don't doubt that someone could come up with a reason but I doubt it would be a common one. Your age changes every year, most people stick with the same name their entire life (perhaps change their last name when they get married) and few people have more than a few professions in their life (they might change jobs but those jobs will tend to be in the same profession).

>
> Do you have a valid reason to do this?
>

The example that comes to mind is for embedded sub-tables rather than arrays, it's invoices. An invoice really consists of two parts. There's the invoice headers and footer information (customer details, delivery address, date of order, date of dispatch &c) and the invoice lines (the details of the items on the invoice; product code, product description, unit cost, number of units &c).

Each invoice will have one set of header/footer information and one or more invoice lines. You could handle this with a pair of tables and a foriegn key relationship between them. By converting the invoice lines table to an embedded sub-table you get rid of the need to think about the foriegn key and should find that the invoice lines information will be in the same datablocks as the invoice they relate to (same as with clusters).

I've never used them outside of a training course myself.

Stephen

-- 
It's better to ask a silly question than to make a silly assumption.
--
http://www.freelists.org/webpage/oracle-l