Gene, you asked what was wrong and I think everyone gave you their opinion.
you know by now that what you are told today will change next month. once you begin down this path, you will never stop. you know that as soon as you set this up, and they have written a ton of reports, that a new process will come along and will update the status field. then, you are stuck trying to update the parent record because it would "be too much work" for them to go back and fix their reports. don't give away the farm today if it will possibly cause you to purchase it back at twice the price later! rules that I always follow: 1). keep the design as clean as possible 2). *never* design database tables to make programmers lives easier, while violating rule #1. it's totally up to you. it sounds like your organization should entertain re-designing the warehouse for reporting needs. good luck! Tom Mercadante Oracle Certified Professional -----Original Message----- Sent: Wednesday, August 14, 2002 10:24 AM To: Multiple recipients of list ORACLE-L Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once. --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > Gene, > > You ask what's wrong with this? The main challenge > I see is: how are you > going to maintain this field? If a status gets > updated in the child table, > are you supposed to update the parent table? And to > do that, you will need > to requery the child table to get all the status's > of all children records > whenever one status changes. > > Pretty silly really, > > Why not do this - create a function that, given the > PK of the parent table, > queries all the child records and concatenates all > the status values into > one string. Then either create a view for the > duhveloper to use to return > what he wants to return at query time. > > Something like this: > > Create or replace function > get_child_status(in_pk_column) return varchar2 is > > cursor c1 is > select status_code > from child_table > where pk_column=in_pk_column; > ret_string varchar2(10); > begin > for c1_rec in c1 loop > ret_string := ret_string || c1_rec.status_code; > end loop; > return ret_string > end; > > The duhveloper can then: > > select parent_pk_column, > get_child_status(parent_pk_column) > from parent_table; > > Hope this helps. > > Tom Mercadante > Oracle Certified Professional > > > -----Original Message----- > From: Gurelei [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 14, 2002 9:19 AM > To: Multiple recipients of list ORACLE-L > Subject: what is wrong with this idea ... > > > Hi. We have a table in our data warehouse which > keeps > info about calls made. This table has a child table > with some detailed information about parts of the > call. There may be any number of "parts" within a > call > (1 to many) and every part has a status. > > MY developer wants to add a string field to the > parent > table which will concatinate all the statuses for > all the parts within this call. For example if > a call has 4 parts and their statuses are > "A","B","A" > and "F", the value of that field will be "ABFA". > Then > the developer will be able to query smalle parent > table instead of a large child table in order to see > how many calls had at least one part with status "A" > or statuses "A" and "F" etc by using a INSTR (or > SUBSTR) command. > > Would it be better (from performance/CPU standpoint) > to add several separate fields: STATUS_A_CNT, > STATUS_B_CNT (the list of status codes is fairly > static) instead? There is something about this > string > that rubs me the wrong way, but I can't put my > finger > on it. > > Any thoughts? > > thank you > > Gene > > __________________________________________________ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Gurelei > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
