Re: Help me design a table

On Mar 18, 9:27 am, "EdStevens" <quetico_..._at_yahoo.com> wrote:
> On Mar 17, 10:03 am, "Anoop" <anoopkum..._at_gmail.com> 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....
>
> A couple of thoughts come to mind ..
>
> First, you say you are constrained by the application, yet you say you
> are writing code to access the table. Is that apart from the app's
> own code? It not, then it sounds like you have acess to the data
> before the packaged app sees it, in which case I'd think you could go
> ahead and normalize your table design. But perhaps I misunderstand.
>
> Second, if this really is a legitimate project at a legitimate job
> (not homework).... then it is evidence of astounding stupidity on the
> part of people at higher pay-grades than yourself. You might want to
> seriously consider seeking employment elsewhere. I know it is easy to
> tell someone else to find a different job, when it's not *my* rent
> that has to be paid. But I just recently escaped from a company that
> was habitually going down the same path ... purchasing s****y
> software, then bludgeoning the DBA's for not being able to make said
> s****y software sing, dance, and print money 24x7x365. It was worth
> the $500/month pay cut to preserve my sanity and health.

You are right - I do have access to the data, but the way this product works is:- you can point it to a single table / view or synonym. In addition you can specify which columns of the table are available to be managed by the product. I can read /write only those columns from the programming layer (java). I configured to use the bank and access columns for this. A user logs in (with his id) and the product shows the managed columns for the id - these columns are disparate. The values for each of these columns are stored as csv in the db. When I read the columns I use a simple method call to convert a csv to a list and similarly I use a method to convert a list to a csv when I write it back. That is all. The constraints imposed by the product is that I can use a single table and the ID column must be unique - nothing less - nothing more. I have to work around these limitations (at least for now)... Also the data can be string data or binary data or encrypted data only.

All I am worried about is the specific order of the elements when I do the csv to list or a list to csv - the bank to access data is one-to- one and specific to each item...

So my question is: Is this a reliable way of maintaining order - if not is there a better way (please note the constraints above) or does oracle provide some way of doing this sort of thing. We use Oracle 9iR2.

Thanks Received on Sun Mar 18 2007 - 19:50:23 CDT