From: Date: May 30 2003 3:25am
Subject: Re: Enum Type...
List-Archive: http://lists.mysql.com/java/5649
Message-Id: <3ED6B2EF.8040804@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

harsh wrote:

>>Does that make it clear?
>
> Thanks for the marvellous explanation,i got the Enum
> but that still won't serve my purpose cause I want to
> store a dynamic array in the database,the contents of which
> are unknown and values might addup or get removed.I thought
> if the Enum serves the same purpose.
>
> Currently i m managing with a text with various entries
> seperated by ":".
>

Sounds like you're violating first normal form there :)

The 'relational' way to store this would be with two tables...One table
with a 'pointer' to a table that has one row for each entry in your
array, consisting of (at the least) a foreign key that is the pointer
from the first table, the value (and optionally, a 'sortOrder' column,
if order is important)...something along the lines of

Table1

+-------------------------+
| someField | dynArrayKey |
+-----------+-------------+
| someValue |           1 |
+-----------+-------------+
| otherValue|           2 |
+-------------------------+

Table2

+-------------------------------------+
| dynArrayKey | someField | sortOrder |
+-------------+-----------+-----------+
|           1 | someValue |          1|
+-------------+-----------+-----------+
|           1 | someValue |          1|
+-------------+-----------+-----------+
|           1 | someValue |          3|
+-------------+-----------+-----------+
|           2 | someValue |          1|
+-------------+-----------+-----------+
|           2 | someValue |          2|
+-------------+-----------+-----------+

You can then have as many or as few items in 'Table2' as needed to
represent your dynamic array, and you can retrieve the array by either
doing a join between Table1 and Table2 on 'dynArrayKey' (more SQL-ish),
or if you already know what dynArrayKey is in your code, you can just do
a straight select on Table2. If you want to retrieve the values in a
particular order, use an 'ORDER BY' clause on the 'sortOrder' column.

The only tricky part is updates...The easy (but inefficient way) is to
just delete all the rows matching 'dynArrayKey' in Table2....Otherwise
you can write more complex code to synchronize your array in Java using
UPDATE and or REPLACE queries.

	-Mark

- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma

    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <mark@stripped>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
        <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+1rLvtvXNTca6JD8RAmZ4AJ9v/hRnwCumlE/nXUeIWLDxaZ9s8gCdGT7c
hkDyaFRYeodbw0JwJh2a0kY=
=31up
-----END PGP SIGNATURE-----