On Mar 16, 6:32 pm, "Anoop" <anoopkum..._at_gmail.com> wrote:
> 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: E11...how 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?
>
> Thanks,
> Anoop
Anoop,
Can you implement a nested table for the bank and access data? Maybe
you could better keep the 2 values "together" if they formed a single
row in a nested table.
Received on Mon Mar 19 2007 - 10:26:04 CDT
