Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Help me design a table

Help me design a table

From: Anoop <>
Date: 16 Mar 2007 18:32:47 -0700
Message-ID: <>

I need to design a table with the following columns. It is a table that will be read by a 3rd party product which allows little customization. The columns would be something like these:

id - primary key
bank - non null (varchar(100))
access - non null (varchar(100))

The requirement is that every "id" can have multiple "bank"'s associated with it and each of those banks can have an (a single)
"access" associated with it.

In order to satisfy this requirement, I suggested that we have have the "bank" column contain a comma separated list - and similarly - the
"access" column will have comma separated values. And example of a row
is like this:

|    ID       |       BANK       |   ACCESS         |
|  E11      |       B1,B2,B3   |  AL1,AL2,AL3    |

So far so good, we are able to write to the table using the product hooks and look up data.
Now my team lead thinks that we may have an issue with the ordering of the data. The issue we are concerned about is the order or the bank to access relationship... i.e., let us say in the above data we need add a new bank B4 with access AL4 to the same id: can we be sure that the order is maintained when we write and read the data. Since the columns are separate, we are worried that when we read we might get the bank to access relationship mixed up. We write data by converting the column contents into a list (using java) appending the value and converting it back to a csv. (applies to both the bank and access columns)

Note: I cannot have more than one table + the Id must be the primary key..

Is our fear well founded? Can we be sure that the order will be maintained while reading and writing data - or can I modify something in the table / or the way data is written to ensure some reliability in the order of data?

Anoop Received on Fri Mar 16 2007 - 20:32:47 CDT

Original text of this message