Re: Store in one database column an array is a bad practice.
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: juancarlosreyesp@xxxxxxxxx
- Date: Thu, 18 Aug 2005 16:58:32 +0100
On 18/08/05, Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
