Re: Help me design a table

On Mar 17, 6:30 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 17.03.2007 16:03, Anoop wrote:
>
>
>
> > On Mar 17, 2:49 am, sybra..._at_gmail.com wrote:
> >> On 16 Mar 2007 18:32:47 -0700, "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:
> >> Do you call this 'design'? It is just a MESS, because it is fully
> >> denormalized. Consequently it can't be updated properly.
> >> I would recommed you go to school and ask your money back.
>
> >> Next time, please do your homework before posting, and don't ask such
> >> ridiculous questions.
>
> >> --
> >> Sybrand Bakker
> >> Senior Oracle DBA
>
> > Oh no this is not homework...It is my work. It is ridiculous I agree.
>
> > I know how to normalize tables and have done a lot of normalization.
> > The problem here is that I cannot attempt normalization as the product
> > I am using offers an interface that needs a single table with a
> > primary key. Had it been a simple normalization of data it would have
> > been a lot easier and I would not have had to post this question
> > here....
>
> Actually I do not understand your question: since you mention that you
> modify values from within Java by doing various conversions to column
> data why can't you ensure proper "constraints" in your Java code? Or is
> there any other piece that changes the data? What exactly is your
> legacy application doing with the data? Tons of open questions...
>
> robert

Hi Robert,

The legacy application uses a single db table to look up id's and show what access each id has on the bank. An id cannot have more than one level of access at any one bank - but may have access to many banks. It is a process we call "reconcile of accounts".

Yes - currently I am using java to check the ordering - but since I use a string to store the bank and access data, in seeming unrelated columns - it may be possible that the order is messed up. basically, a user can request his access to be changed or one of his banks to be removed, we need to at that time (using java) change the access for the correct bank (by the order in which they appear currently) and if removed, we need to again ensure that the currect access is removed. An example: user E123 has current access to Bank1, Bank2,Bank3 with A1,A2,A3 access levels respectively (yes comma separated values).He requests removal of Bank2. We need to ensure that the row now reads as: Bank1,Bank3 and A1,A3. Here it is easy to picturise, but think of say access to 100's of banks and each with an access level. I am just trying to explore if there is a better way of doing this or if there is something oracle provides (some constraint checking or whatever) that will make this more reliable...

Thanks Received on Sat Mar 17 2007 - 22:24:54 CDT