Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!l77g2000hsb.googlegroups.com!not-for-mail From: "Anoop" Newsgroups: comp.databases.oracle.server Subject: Re: Help me design a table Date: 18 Mar 2007 17:50:23 -0700 Organization: http://groups.google.com Lines: 91 Message-ID: <1174265423.218258.155520@l77g2000hsb.googlegroups.com> References: <1174095167.672433.118190@n76g2000hsh.googlegroups.com> <1174143788.624537.196380@y66g2000hsf.googlegroups.com> <1174224447.155613.35890@n76g2000hsh.googlegroups.com> NNTP-Posting-Host: 72.165.204.190 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1174265424 25929 127.0.0.1 (19 Mar 2007 00:50:24 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 19 Mar 2007 00:50:24 +0000 (UTC) In-Reply-To: <1174224447.155613.35890@n76g2000hsh.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: l77g2000hsb.googlegroups.com; posting-host=72.165.204.190; posting-account=4IKwPA0AAABpl0qPN90313unqFUdtb0q Xref: usenetserver.com comp.databases.oracle.server:424364 X-Received-Date: Sun, 18 Mar 2007 19:50:24 EST (text.usenetserver.com) On Mar 18, 9:27 am, "EdStevens" wrote: > On Mar 17, 10:03 am, "Anoop" wrote: > > > > > On Mar 17, 2:49 am, sybra...@gmail.com wrote: > > > > On 16 Mar 2007 18:32:47 -0700, "Anoop" 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