Re: Single Code Table or Separate Code tables dilemma
- Original Message - As to the encapsulation: Yes things should be encapsulated, fine. But are you saying you do not use any queries in your encapsulating procedures? I am using cursors ( mostly implicit ). Have you ever seen an app that was completely encapsulated? Yes, I had. Not every app needs ad hoc queries.. What about the developer that gets repeatedly reprimanded for not following corporate standards but is married to the CEO's daughter? If the CEO still keeps him, he is dumb. If the CEO is dumb, the whole company goes to hell sooner or later. What's the point of taking care of the project then? :-) You keep referring to code that has to be changed anyway. Perchance another example, we'll use the DOCTOR table from the Ghosalkar/Kilchoer thread: ... The users tell us: We need to track specialties as being primarily inpatient or outpatient so that extra information can be collected for inpatient situations (I know, those of you doing work in health care can shoot all kinds of holes in this.) Then I copy all the entries from the code table. (script that must run at the production site - asking for trouble), remove them from the old table (even more dangerous). Update the master table entry program and procedures to not allow the archived code any more. Rip through every scrap of code in the system to find any reference to I_DONT_LIKE_MASTER to make sure it does not have any need of the archived code type. I would put this into code_type = 'specialty_type_inpatient' and code_type = 'specialty_type_outpatient', not adding the inpatient_yn column. Please bear in mind that although you are being a very good programmer and using encapsulation, the idiots that got fired last week were not, so we still have to look. I would say we have to prevent this :-) "Code supervizing" they call it.. Any that are found will have to be changed. Now I create my new entry form to add the field and update the registration code. Then I sit back and wait for 1) one of the client's Access reports to return no data because they did not realize the data had been moved, So Access report does not use the packaged code? Too bad.. or 2) some of our code to break because we overlooked something. The multiple table way: CREATE TABLE SPECIALTIES (INPATIENT_YN VARCHAR2(1)) Now I create my new entry form to add the field and update the registration code. Done. Where are these other code changes that have to be done? Changes to the doctor table. All the old code (encapsulated or not) still works the way it used to. If I overlook something, it may not have the added functionality, but it will not break. PS: I think this discussion would best be continued with copious amounts of alcohol. If you are ever in Dallas, look me up. Agreed ;-) If you'll be in Washington DC, let me know! -Original Message- From: Michael Netrusov [mailto:[EMAIL PROTECTED]] Sent: Friday, March 23, 2001 4:31 PM To: Multiple recipients of list ORACLE-L Subject: Re: Single Code Table or Separate Code tables dilemma Well, in the case of 100 tables you are still hunting all the code looking for pieces which are relevant to that changed table. Depends on your code. I prefer to encapsulate mine, so reference to a table is enclosed in its own package. My point is that I can use grep to search the entire code for "order_status" and get a short list (maybe 10) of places to look. It is much more difficult to search for "code_table", get a list of 1, then filter through the results for some bastardized variety of "code_type = 'STATUS'" to get down to those same 10. Not everything can be encapsulated in a procedure. This is a database we are talking about, so people do still write queries where it is easier to join the lookup table to the master table directly. If the code has to be broken out of the central lookup table, you have to update every query that joins it in, even if all that query needs is the description. If it is already split out, the only code I really have to be concerned with is the places where the new functionality applies. Queries still can be ( and should be ) encapsulated in packages. If you are taking this to-be-enhanced 'status' entity out of the master lookup table, all you have to do is to develop a new package, change the calling code and prohibit the usage of code_type = 'status' in the master lookup table. This still would be changed if you had a separate package and table for the 'status' entity. If I were starting a product from scratch and there was a central code table I would probably code against a set of views in anticipation of the above event, so the DBA ends up creating 1 objects anyway. Views consume resources and bring new dependencies, so the administr
RE: Single Code Table or Separate Code tables dilemma
RE: Single Code Table or Separate Code tables dilemma
A 'single' code table is something that Gurry and Corrigan favor as a performance enhancement. I have not tried it myself; I feel about it as you do. If I were trying to wring every last bit of performance out of an app though, I would probably give it a shot. Jared On Fri, 23 Mar 2001, Jacques Kilchoer wrote: I hate to be picky, but I don't like either of the solutions proposed below. For the first solution, that means that I would have to have the repeating values 'SPLT' and 'TYPE' in every row of the DOCTOR table, which is a table with a lot of rows in it. For the second solution, I have to have a table that contains every possible combination of SPLT and TYPE. Both solutions seem to be wasteful and clumsy. Please don't take this as a direct criticism of you, but I still don't see how a single code table can be implemented well using FK relationships. -Original Message- From: Mandar Ghosalkar [mailto:[EMAIL PROTECTED]] CREATE TABLE I_DONT_LIKE_MASTER ( CODE_TYPE VARCHAR2(4) NOT NULL, CODE_VALUE VARCHAR2(3) NOT NULL, CODE_DESC VARCHAR2(20) , PRIMARY KEY (CODE_TYPE, CODE_VALUE) ) SPLTN SDFLJDSL SPLTFP SDFSDKFDS SPLTOBG SFLSDJFSD TYPEMD SDFLSDFSDF TYPEFP SDFJDSFJ TYPEOPH SDLFJDSKF CREATE TABLE DOCTOR (., SPECIALITY_CODE_TYPEVARCHAR2(4), SPECIALITY_CODE_VALUE VARCHAR2(3), TYPE_CODE_TYPE VARCHAR2(4), TYPE_CODE_VALUE VARCHAR2(3), ., foreign key (SPECIALITY_CODE_TYPE,SPECIALITY_CODE_VALUE) references I_DONT_LIKE_MASTER, foreign key (TYPE_CODE_TYPE, TYPE_CODE_VALUE ) references I_DONT_LIKE_MASTER ) D1 SPLTN TYPEOPH D2 SPLT N TYPEMD but u can also create surrogate key and make (CODE_TYPE, CODE_VALUE) as alternate key (unique) and then reference the surrogate key in ur doctor table, instead of two cols as one foreign key -Original Message- I have a table called DOCTOR that has two fields - SPECIALTY and TYPE. The acceptable values for SPECIALTY are N, FP, OBG, etc... The acceptable values for TYPE are MD, FP, OPH, etc... If I have a "single code table", how can I create a Foreign key constraint on the SPECIALTY column and another one on the TYPE column? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
Re: Single Code Table or Separate Code tables dilemma
Mandar Tell them that (if they are really lazy) you can build them a function that returns ref cursor with Code, description columns and they pass in the domain/context they want - same for the inserts - so you can get rid of their comments. To be sincere, I use combination - separate look-up tables for the entity related types, statuses etc and one for generic stuff like boolean i.e. if site uses 1,0 or Y/N or TRUE/FALSE and date/time formats etc. which does not map to any particular entity/object. HTH Regards Kresimir Fabijanic -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kresimir Fabijanic 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).
Re: Single Code Table or Separate Code tables dilemma
Nice, but this construct violates Second Normal Form. I only denormalize for compelling reasons, like for performance reasons, and I don't see how performance is enhanced by jamming all this stuff into one big table. You may end up with an index that has an extra level of index blocks, which comes with a performance hit. [EMAIL PROTECTED] 03/22/01 01:55PM developer combat starts Referential integrity is still present if you create Master lookup table with type attribute: lookup_id varchar2(20) pk lookup_type varchar2(20) pk description varchar2(255) 1. specific attributes for a particular code type is logically and physically separated from other code types. It does not matter - just don't read the attributes' values that are irrelevant 2. a table lock affects only the concerned code table who needs a table lock in a lookup table??!! :-) 3. granular control over the individual code table still present with the lookup_type column. /developer combat ends So now for the DBA side: do you prefer to have multiple numerous small tables or one large? :-) Regards, Michael Netrusov, www.atelo.com - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, March 22, 2001 11:50 Code tables... been there done that (with PowerBuilder/Oracle) and I don't like it. Here's why... Large apps may consist of 100's or 1000's of lookup tables so duhvelopers like the "master code table" idea because they only have to build one front end for maintaining all the "lookup" values. But what about referential integrity? If you have to do it against one massive code table via triggers or from front end code then you're adding work back to the coding effort. What about database tuning? Lookup tables are good candidates for caching... Are you going to cache one huge, denormalized code table? If your lookup values are in multiple normalized tables then you the DBA can choose which tables are suitable for caching. With a few exceptions, most "Lookup tables" have a common structure with just two columns: one for the PK value and another for the description. You could review all the referential integrity/data lookup requirements in your app and come up with a common structure for all lookup tables that could handle most situations. Here's are some example columns: table name_ID (the primary key); short_label; long_label; short_description; long_description; enabled_flag; effective_date; expiration_date; date_created; last_update; last_updated_by. I'd put my foot down and place the following challenge to the duhvelopers: "Any SAVVY developer worth his salt should be able to create a robust, object oriented design to make coding a snap no matter how many lookup tables there are. [Good] Developers can do this by inheriting from a parent window or set of objects in his class library. The label and description columns could be for GUI display. The enabled_flag could default to 'Y' and be referenced as standard practice in the where clause of every lookup query. Ditto for the effective_date and expiration_date columns where your validations have a time fence constraint such as a 'date_DBA_hourly_rate_increase_becomes_billable column." :) Ready for duhveloper combat... Steve Orr -Original Message- Sent: Wednesday, March 21, 2001 4:32 PM To: Oracledba (E-mail); ORACLE-L (E-mail) Guys, We r working on a Datawarehouse solution. Our Duhvelopers want to merge all code tables into a single table by adding a codetype column. with reference to this, i came across this article from Steve's site http://www.ixora.com.au/tips/design/meta-data.htm i want to put them into different individual code tables instead of a single table, for the foll reasons. 1. specific attributes for a particular code type is logically and physically seperated from other code types. 2. a table lock affects only the concerned code table 3. granular control over the individual code table i am short of arguments wld be grateful, if ull can advise me which would be better from performance perspective. -Mandar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Orr 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: Michael Netrusov INET: [EMAIL PROTECTED]
Re: Single Code Table or Separate Code tables dilemma
What's wrong with a composite foreign key constaint? Works for me all the time.. As a duhveloper wont u like to pass one argument to a procedure rather than two? I don't care - I am using the wonderful copy-paste. granular control over the individual code table You DON'T have granular control for caching specific tables because everything is mixed together. (Hawaiian pidgin translation: "All kalikaka li' dat. Da' kine chop suey. Easy Brah." :) No control for different indexing requirements or not even having an index for small tables where it would be better to do a FTS. I DO. Nothing is mixed - you can even partition this table if you prefer to keep unlike data in different places. so u will create one single table and then partition it. do u think partitioning is cheap from maintenance point? No, it's not cheap. I only said it could be partitioned, if someone wants to. Do you think adiministering of a 100 similar tables is cheap? prefer to have multiple numerous small tables or one large? Numerous small tables are not a problem, actually a benefit because you have more granular control for tuning. I once had the misfortune of having to do reports where the "mother of all code tables" had around 100,000 rows. (It was a big, dumb 3rd party app with roots in COBOL and it had a lot of unmaintained junk in it). To pick up a description in a large multi-table join query I had to join against the mother of all code tables where a small 10 row table would have sufficed. I don't think a join to 100K rows table versus a join to 10 rows table would make a big difference.. Most likely the join was slow was it? ) because of some other factors. u dont mind searching 10 starbuck's shops to find one cup of coffee? are you searching those rows manually? :-) DB does not care. also u dont mind releasing 10 different versions of ur programs to ur client and then let the client search thru them for the best one. how's that related? I'm not exactly a relational purist and for some apps code tables may work just fine. But usually it's just for duhveloper convenience (laziness) and why make life easy for them ;-) at the expense of "normal" relational design. Especially if they haven't bothered to answer the challenge I posed below? When I was a developer I had a set of objected oriented class libraries and could bang out a new window for a lookup table in 60 seconds. Of course I probably spent 600 hours developing and maintaining my libraries... sigh. Oh yeah, developer designed tables containing metadata come in handy also. Do developers design ER models in your shop? It should be you or a DA. :-) Duhveloper combat is so much fun! :-) Unfortunately they usually outnumber us DBA's so we have to be particularly nimble. ;-) In my current shop I am a pl/sql developer :-) ... as well as a DA and a DBA. This saves me a LOT time and nerves. so whose is the PM and client? PM is a atavistic fugure. No PMs - no problems. Clients are happy so far :-) I taste blood :) Har Har Mahadev ... Thats a Battle Cry :-) :-)) Regards, Michael -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Netrusov 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).
Re: Single Code Table or Separate Code tables dilemma
Some good points... different strokes for different folks ya know. :) exactly. Partitioning a code table? Really??? Show me the money, er, cache. If your lookup/validation data is not "mixed together" then why would you need to partition it? Why not? If you can imagine a code table with 10M rows, somebody might want to partion it with local indices. ( For those who prefer to search for data among 10 rows :-)). Regarding "lookup" or validation tables and more complex "rules" tables... do they ALL go into your master code table? It depends. "Rules" tables do not go into master lookup. Validation - maybe.. if the number of values is small and unvariant, they can go into check constraint. For instance, what about a "code table" for all valid U.S. zip codes? Do they go into your master code table? State abbreviations too? What about valid city/state/zip code combinations which you can get from the U.S.P.O.? At what point are validation/lookup/rules data not in the master code table and how do you decide? If you have relationship between lookup tables, probably they will not go to the master lookup table ( although is is still possible ). Depends on a common sense. If you have 100 tables all of them are of ( t_id, name, description ) - they will sure go. The code table technique may be fine for smaller apps but couldn't it become unwieldy for larger apps with 5000+ tables? Of which 1-3000 would be "lookup/validation tables?" I think it still work for large models. Large app with 5000 tables of which 3000 tables are lookups is a medium app with 2001 tables :-). Do you prefer to administer 3000 tables instead of one, even partitioned? Do Oracle ERP or SAP ERP apps do this? Curious. They do not. But I do not consider them a good example of desing and programming. Regards, Michael -Original Message- Ghosalkar Sent: Thursday, March 22, 2001 3:01 PM To: Multiple recipients of list ORACLE-L -Original Message- From: Michael Netrusov [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 4:23 PM To: Multiple recipients of list ORACLE-L Subject: Re: Single Code Table or Separate Code tables dilemma Referential integrity is still present if you create Master lookup table with type attribute This requires a "composite referential integrity constraint" such as: alter table tname add constraint fk_whatever foreign key (extra_column_for_code_table_key, column_i_really_care_about) references master_codes(lookup_id, lookup_type); Still don't like it. Maybe it's just a preference thing... What's wrong with a composite foreign key constaint? Works for me all the time.. As a duhveloper wont u like to pass one argument to a procedure rather than two? granular control over the individual code table You DON'T have granular control for caching specific tables because everything is mixed together. (Hawaiian pidgin translation: "All kalikaka li' dat. Da' kine chop suey. Easy Brah." :) No control for different indexing requirements or not even having an index for small tables where it would be better to do a FTS. I DO. Nothing is mixed - you can even partition this table if you prefer to keep unlike data in different places. so u will create one single table and then partition it. do u think partitioning is cheap from maintenance point? prefer to have multiple numerous small tables or one large? Numerous small tables are not a problem, actually a benefit because you have more granular control for tuning. I once had the misfortune of having to do reports where the "mother of all code tables" had around 100,000 rows. (It was a big, dumb 3rd party app with roots in COBOL and it had a lot of unmaintained junk in it). To pick up a description in a large multi-table join query I had to join against the mother of all code tables where a small 10 row table would have sufficed. I don't think a join to 100K rows table versus a join to 10 rows table would make a big difference.. Most likely the join was slow was it? ) because of some other factors. u dont mind searching 10 starbuck's shops to find one cup of coffee? also u dont mind releasing 10 different versions of ur programs to ur client and then let the client search thru them for the best one. I'm not exactly a relational purist and for some apps code tables may work just fine. But usually it's just for duhveloper convenience (laziness) and why make life easy for them ;-) at the expense of "normal" relational design. Especially if they haven't bothered to answer the challenge I posed below? When I was a developer I had a set of objected oriented class libraries and could bang out a new window for a
Re: Single Code Table or Separate Code tables dilemma
At one of the Oraccle Applications User meetings, Oracle displayed the new table layout for the 11i version. It basically was one table with a lot of columns for the application that was talked about. I asked about the normalization of the data and the reply was in the form: it will take more disk reads to gather data from many tables where if the data is in one table, the heads are already in position and you get your data faster than multiple reads. For what it's worth ROR mm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers 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).
Re: Single Code Table or Separate Code tables dilemma
- Original Message - Preface: I am a developer! If the DBA wants to have 1 tables, no skin off my nose. A generic maintenance form and LOV form for a set of tables all with the same format is not that huge a task. The best developer argument AGAINST a centralized code table is that eventually someone will request an enhancement that adds functionality to one of the codes beyond a simple code/description table. This means the codes have to be pulled out of the central code table and created as a new table with the 3 flags and 5 coded fields to support the new functionality. The DBA work on that is easy, but then the developers go hunting in all the existing program code that hits the code table (haystack) to find all the places where that program code is actually using the code in question (needle). Well, in the case of 100 tables you are still hunting all the code looking for pieces which are relevant to that changed table. Depends on your code. I prefer to encapsulate mine, so reference to a table is enclosed in its own package. If I were starting a product from scratch and there was a central code table I would probably code against a set of views in anticipation of the above event, so the DBA ends up creating 1 objects anyway. Views consume resources and bring new dependencies, so the administration becomes more complicated. What is your point? If somebody makes a change request, youl still will have to change your code. If you are adding columns and prefer not to change the existing procedures, add a new procedure ( with the same name ) which works with new attributes. On the other hand: 1. Everywhere I have worked, there has been a central code table of some sort. 2. In all cases that code table was put in place by the DBAs, not the developers, because they didn't want all those tables and were not really hung up on referential integrity that the application was enforcing anyway. As Steve pointed out, it's just a matter of preference. A few years ago I prefered multiple similar tables vs one master lookup.. Now I implemented the opposite approach and I am happy with it. Less code ( packages consuming memory ), less database objects to administer. Regards, Michael Netrusov www.atelo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Netrusov 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).
RE: Single Code Table or Separate Code tables dilemma
Snipped text The DBA work on that is easy, but then the developers go hunting in all the existing program code that hits the code table (haystack) to find all the places where that program code is actually using the code in question (needle). Well, in the case of 100 tables you are still hunting all the code looking for pieces which are relevant to that changed table. Depends on your code. I prefer to encapsulate mine, so reference to a table is enclosed in its own package. My point is that I can use grep to search the entire code for "order_status" and get a short list (maybe 10) of places to look. It is much more difficult to search for "code_table", get a list of 1, then filter through the results for some bastardized variety of "code_type = 'STATUS'" to get down to those same 10. Not everything can be encapsulated in a procedure. This is a database we are talking about, so people do still write queries where it is easier to join the lookup table to the master table directly. If the code has to be broken out of the central lookup table, you have to update every query that joins it in, even if all that query needs is the description. If it is already split out, the only code I really have to be concerned with is the places where the new functionality applies. If I were starting a product from scratch and there was a central code table I would probably code against a set of views in anticipation of the above event, so the DBA ends up creating 1 objects anyway. Views consume resources and bring new dependencies, so the administration becomes more complicated. What is your point? If somebody makes a change request, youl still will have to change your code. If you are adding columns and prefer not to change the existing procedures, add a new procedure ( with the same name ) which works with new attributes. The difficulty is not in making the changes, but in doing the analysis to figure out where the changes need to be made. On the other hand: 1. Everywhere I have worked, there has been a central code table of some sort. 2. In all cases that code table was put in place by the DBAs, not the developers, because they didn't want all those tables and were not really hung up on referential integrity that the application was enforcing anyway. As Steve pointed out, it's just a matter of preference. A few years ago I prefered multiple similar tables vs one master lookup.. Now I implemented the opposite approach and I am happy with it. Less code ( packages consuming memory ), less database objects to administer. Brian Norrell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Norrell, Brian 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).
RE: Single Code Table or Separate Code tables dilemma
I've found so many bugs in code caused by developers forgetting to add "AND code_type = 'SOME_VALUE'" to there WHERE clauses when multi-purpose code tables are used. Kevin Toepke - The information in this electronic mail message is Cendant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. - The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. - -Original Message- Sent: Friday, March 23, 2001 3:46 PM To: Multiple recipients of list ORACLE-L Snipped text The DBA work on that is easy, but then the developers go hunting in all the existing program code that hits the code table (haystack) to find all the places where that program code is actually using the code in question (needle). Well, in the case of 100 tables you are still hunting all the code looking for pieces which are relevant to that changed table. Depends on your code. I prefer to encapsulate mine, so reference to a table is enclosed in its own package. My point is that I can use grep to search the entire code for "order_status" and get a short list (maybe 10) of places to look. It is much more difficult to search for "code_table", get a list of 1, then filter through the results for some bastardized variety of "code_type = 'STATUS'" to get down to those same 10. Not everything can be encapsulated in a procedure. This is a database we are talking about, so people do still write queries where it is easier to join the lookup table to the master table directly. If the code has to be broken out of the central lookup table, you have to update every query that joins it in, even if all that query needs is the description. If it is already split out, the only code I really have to be concerned with is the places where the new functionality applies. If I were starting a product from scratch and there was a central code table I would probably code against a set of views in anticipation of the above event, so the DBA ends up creating 1 objects anyway. Views consume resources and bring new dependencies, so the administration becomes more complicated. What is your point? If somebody makes a change request, youl still will have to change your code. If you are adding columns and prefer not to change the existing procedures, add a new procedure ( with the same name ) which works with new attributes. The difficulty is not in making the changes, but in doing the analysis to figure out where the changes need to be made. On the other hand: 1. Everywhere I have worked, there has been a central code table of some sort. 2. In all cases that code table was put in place by the DBAs, not the developers, because they didn't want all those tables and were not really hung up on referential integrity that the application was enforcing anyway. As Steve pointed out, it's just a matter of preference. A few years ago I prefered multiple similar tables vs one master lookup.. Now I implemented the opposite approach and I am happy with it. Less code ( packages consuming memory ), less database objects to administer. Brian Norrell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Norrell, Brian 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: Toepke, Kevin M 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
Re: Single Code Table or Separate Code tables dilemma
Well, in the case of 100 tables you are still hunting all the code looking for pieces which are relevant to that changed table. Depends on your code. I prefer to encapsulate mine, so reference to a table is enclosed in its own package. My point is that I can use grep to search the entire code for "order_status" and get a short list (maybe 10) of places to look. It is much more difficult to search for "code_table", get a list of 1, then filter through the results for some bastardized variety of "code_type = 'STATUS'" to get down to those same 10. Not everything can be encapsulated in a procedure. This is a database we are talking about, so people do still write queries where it is easier to join the lookup table to the master table directly. If the code has to be broken out of the central lookup table, you have to update every query that joins it in, even if all that query needs is the description. If it is already split out, the only code I really have to be concerned with is the places where the new functionality applies. Queries still can be ( and should be ) encapsulated in packages. If you are taking this to-be-enhanced 'status' entity out of the master lookup table, all you have to do is to develop a new package, change the calling code and prohibit the usage of code_type = 'status' in the master lookup table. This still would be changed if you had a separate package and table for the 'status' entity. If I were starting a product from scratch and there was a central code table I would probably code against a set of views in anticipation of the above event, so the DBA ends up creating 1 objects anyway. Views consume resources and bring new dependencies, so the administration becomes more complicated. What is your point? If somebody makes a change request, youl still will have to change your code. If you are adding columns and prefer not to change the existing procedures, add a new procedure ( with the same name ) which works with new attributes. The difficulty is not in making the changes, but in doing the analysis to figure out where the changes need to be made. please see above. The changes still would be made. On the other hand: 1. Everywhere I have worked, there has been a central code table of some sort. 2. In all cases that code table was put in place by the DBAs, not the developers, because they didn't want all those tables and were not really hung up on referential integrity that the application was enforcing anyway. As Steve pointed out, it's just a matter of preference. A few years ago I prefered multiple similar tables vs one master lookup.. Now I implemented the opposite approach and I am happy with it. Less code ( packages consuming memory ), less database objects to administer. No arguing with the latest passage? :-) Regards, Michael Netrusov -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael Netrusov 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).
RE: Single Code Table or Separate Code tables dilemma
Title: RE: Single Code Table or Separate Code tables dilemma I'm still not quite sure I understand the single code table business. Let me give an example (taken from a live example at one of my previous jobs). I have a table called DOCTOR that has two fields - SPECIALTY and TYPE. The acceptable values for SPECIALTY are N, FP, OBG, etc... The acceptable values for TYPE are MD, FP, OPH, etc... If I have a single code table, how can I create a Foreign key constraint on the SPECIALTY column and another one on the TYPE column? -- Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
RE: Single Code Table or Separate Code tables dilemma
CREATE TABLE I_DONT_LIKE_MASTER ( CODE_TYPE VARCHAR2(4) NOT NULL, CODE_VALUE VARCHAR2(3) NOT NULL, CODE_DESC VARCHAR2(20) , PRIMARY KEY (CODE_TYPE, CODE_VALUE) ) SPLTN SDFLJDSL SPLTFP SDFSDKFDS SPLTOBG SFLSDJFSD TYPEMD SDFLSDFSDF TYPEFP SDFJDSFJ TYPEOPH SDLFJDSKF CREATE TABLE DOCTOR (., SPECIALITY_CODE_TYPEVARCHAR2(4), SPECIALITY_CODE_VALUE VARCHAR2(3), TYPE_CODE_TYPE VARCHAR2(4), TYPE_CODE_VALUE VARCHAR2(3), ., foreign key (SPECIALITY_CODE_TYPE,SPECIALITY_CODE_VALUE) references I_DONT_LIKE_MASTER, foreign key (TYPE_CODE_TYPE, TYPE_CODE_VALUE ) references I_DONT_LIKE_MASTER ) D1 SPLTN TYPEOPH D2 SPLT N TYPEMD but u can also create surrogate key and make (CODE_TYPE, CODE_VALUE) as alternate key (unique) and then reference the surrogate key in ur doctor table, instead of two cols as one foreign key -MANDAR -Original Message- Sent: Friday, March 23, 2001 7:31 PM To: Multiple recipients of list ORACLE-L I'm still not quite sure I understand the "single code table" business. Let me give an example (taken from a live example at one of my previous jobs). I have a table called DOCTOR that has two fields - SPECIALTY and TYPE. The acceptable values for SPECIALTY are N, FP, OBG, etc... The acceptable values for TYPE are MD, FP, OPH, etc... If I have a "single code table", how can I create a Foreign key constraint on the SPECIALTY column and another one on the TYPE column? -- Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Ghosalkar 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).
RE: Single Code Table or Separate Code tables dilemma
Code tables... been there done that (with PowerBuilder/Oracle) and I don't like it. Here's why... Large apps may consist of 100's or 1000's of lookup tables so duhvelopers like the "master code table" idea because they only have to build one front end for maintaining all the "lookup" values. But what about referential integrity? If you have to do it against one massive code table via triggers or from front end code then you're adding work back to the coding effort. What about database tuning? Lookup tables are good candidates for caching... Are you going to cache one huge, denormalized code table? If your lookup values are in multiple normalized tables then you the DBA can choose which tables are suitable for caching. With a few exceptions, most "Lookup tables" have a common structure with just two columns: one for the PK value and another for the description. You could review all the referential integrity/data lookup requirements in your app and come up with a common structure for all lookup tables that could handle most situations. Here's are some example columns: table name_ID (the primary key); short_label; long_label; short_description; long_description; enabled_flag; effective_date; expiration_date; date_created; last_update; last_updated_by. I'd put my foot down and place the following challenge to the duhvelopers: "Any SAVVY developer worth his salt should be able to create a robust, object oriented design to make coding a snap no matter how many lookup tables there are. [Good] Developers can do this by inheriting from a parent window or set of objects in his class library. The label and description columns could be for GUI display. The enabled_flag could default to 'Y' and be referenced as standard practice in the where clause of every lookup query. Ditto for the effective_date and expiration_date columns where your validations have a time fence constraint such as a 'date_DBA_hourly_rate_increase_becomes_billable column." :) Ready for duhveloper combat... Steve Orr -Original Message- Sent: Wednesday, March 21, 2001 4:32 PM To: Oracledba (E-mail); ORACLE-L (E-mail) Guys, We r working on a Datawarehouse solution. Our Duhvelopers want to merge all code tables into a single table by adding a codetype column. with reference to this, i came across this article from Steve's site http://www.ixora.com.au/tips/design/meta-data.htm i want to put them into different individual code tables instead of a single table, for the foll reasons. 1. specific attributes for a particular code type is logically and physically seperated from other code types. 2. a table lock affects only the concerned code table 3. granular control over the individual code table i am short of arguments wld be grateful, if ull can advise me which would be better from performance perspective. -Mandar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Orr 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).
Re: Single Code Table or Separate Code tables dilemma
Referential integrity is still present if you create Master lookup table with type attribute This requires a "composite referential integrity constraint" such as: alter table tname add constraint fk_whatever foreign key (extra_column_for_code_table_key, column_i_really_care_about) references master_codes(lookup_id, lookup_type); Still don't like it. Maybe it's just a preference thing... What's wrong with a composite foreign key constaint? Works for me all the time.. granular control over the individual code table You DON'T have granular control for caching specific tables because everything is mixed together. (Hawaiian pidgin translation: "All kalikaka li' dat. Da' kine chop suey. Easy Brah." :) No control for different indexing requirements or not even having an index for small tables where it would be better to do a FTS. I DO. Nothing is mixed - you can even partition this table if you prefer to keep unlike data in different places. As for not creating indices for small tables: small lookup tables do not need any index except PK index. Not having a PK constraint is not a good thing anyway - how you will define an intergrity constraint? For small tables FTS would be done by CBO regardless of index presence ( unless you specify hints ). prefer to have multiple numerous small tables or one large? Numerous small tables are not a problem, actually a benefit because you have more granular control for tuning. I once had the misfortune of having to do reports where the "mother of all code tables" had around 100,000 rows. (It was a big, dumb 3rd party app with roots in COBOL and it had a lot of unmaintained junk in it). To pick up a description in a large multi-table join query I had to join against the mother of all code tables where a small 10 row table would have sufficed. I don't think a join to 100K rows table versus a join to 10 rows table would make a big difference.. Most likely the join was slow was it? ) because of some other factors. I'm not exactly a relational purist and for some apps code tables may work just fine. But usually it's just for duhveloper convenience (laziness) and why make life easy for them ;-) at the expense of "normal" relational design. Especially if they haven't bothered to answer the challenge I posed below? When I was a developer I had a set of objected oriented class libraries and could bang out a new window for a lookup table in 60 seconds. Of course I probably spent 600 hours developing and maintaining my libraries... sigh. Oh yeah, developer designed tables containing metadata come in handy also. Do developers design ER models in your shop? It should be you or a DA. :-) Duhveloper combat is so much fun! :-) Unfortunately they usually outnumber us DBA's so we have to be particularly nimble. ;-) In my current shop I am a pl/sql developer :-) ... as well as a DA and a DBA. This saves me a LOT time and nerves. Regards, Michael Netrusov. -Original Message- Netrusov Sent: Thursday, March 22, 2001 10:56 AM To: Multiple recipients of list ORACLE-L developer combat starts Referential integrity is still present if you create Master lookup table with type attribute: lookup_id varchar2(20) pk lookup_type varchar2(20) pk description varchar2(255) 1. specific attributes for a particular code type is logically and physically separated from other code types. It does not matter - just don't read the attributes' values that are irrelevant 2. a table lock affects only the concerned code table who needs a table lock in a lookup table??!! :-) 3. granular control over the individual code table still present with the lookup_type column. /developer combat ends So now for the DBA side: do you prefer to have multiple numerous small tables or one large? :-) Regards, Michael Netrusov, www.atelo.com - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, March 22, 2001 11:50 Code tables... been there done that (with PowerBuilder/Oracle) and I don't like it. Here's why... Large apps may consist of 100's or 1000's of lookup tables so duhvelopers like the "master code table" idea because they only have to build one front end for maintaining all the "lookup" values. But what about referential integrity? If you have to do it against one massive code table via triggers or from front end code then you're adding work back to the coding effort. What about database tuning? Lookup tables are good candidates for caching... Are you going to cache one huge, denormalized code table? If your lookup values are in multiple normalized tables then you the DBA can choose which tables are suitable for caching. With a few exceptions, most "Lookup tables" have a common structure with just two columns: one for the PK value and another for the description.
RE: Single Code Table or Separate Code tables dilemma
-Original Message- From: Michael Netrusov [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 4:23 PM To: Multiple recipients of list ORACLE-L Subject: Re: Single Code Table or Separate Code tables dilemma Referential integrity is still present if you create Master lookup table with type attribute This requires a "composite referential integrity constraint" such as: alter table tname add constraint fk_whatever foreign key (extra_column_for_code_table_key, column_i_really_care_about) references master_codes(lookup_id, lookup_type); Still don't like it. Maybe it's just a preference thing... What's wrong with a composite foreign key constaint? Works for me all the time.. As a duhveloper wont u like to pass one argument to a procedure rather than two? granular control over the individual code table You DON'T have granular control for caching specific tables because everything is mixed together. (Hawaiian pidgin translation: "All kalikaka li' dat. Da' kine chop suey. Easy Brah." :) No control for different indexing requirements or not even having an index for small tables where it would be better to do a FTS. I DO. Nothing is mixed - you can even partition this table if you prefer to keep unlike data in different places. so u will create one single table and then partition it. do u think partitioning is cheap from maintenance point? prefer to have multiple numerous small tables or one large? Numerous small tables are not a problem, actually a benefit because you have more granular control for tuning. I once had the misfortune of having to do reports where the "mother of all code tables" had around 100,000 rows. (It was a big, dumb 3rd party app with roots in COBOL and it had a lot of unmaintained junk in it). To pick up a description in a large multi-table join query I had to join against the mother of all code tables where a small 10 row table would have sufficed. I don't think a join to 100K rows table versus a join to 10 rows table would make a big difference.. Most likely the join was slow was it? ) because of some other factors. u dont mind searching 10 starbuck's shops to find one cup of coffee? also u dont mind releasing 10 different versions of ur programs to ur client and then let the client search thru them for the best one. I'm not exactly a relational purist and for some apps code tables may work just fine. But usually it's just for duhveloper convenience (laziness) and why make life easy for them ;-) at the expense of "normal" relational design. Especially if they haven't bothered to answer the challenge I posed below? When I was a developer I had a set of objected oriented class libraries and could bang out a new window for a lookup table in 60 seconds. Of course I probably spent 600 hours developing and maintaining my libraries... sigh. Oh yeah, developer designed tables containing metadata come in handy also. Do developers design ER models in your shop? It should be you or a DA. :-) Duhveloper combat is so much fun! :-) Unfortunately they usually outnumber us DBA's so we have to be particularly nimble. ;-) In my current shop I am a pl/sql developer :-) ... as well as a DA and a DBA. This saves me a LOT time and nerves. so whose is the PM and client? I taste blood :) Har Har Mahadev ... Thats a Battle Cry :-) Regards, Mandar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Ghosalkar 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).
RE: Single Code Table or Separate Code tables dilemma
Preface: I am a developer! If the DBA wants to have 1 tables, no skin off my nose. A generic maintenance form and LOV form for a set of tables all with the same format is not that huge a task. The best developer argument AGAINST a centralized code table is that eventually someone will request an enhancement that adds functionality to one of the codes beyond a simple code/description table. This means the codes have to be pulled out of the central code table and created as a new table with the 3 flags and 5 coded fields to support the new functionality. The DBA work on that is easy, but then the developers go hunting in all the existing program code that hits the code table (haystack) to find all the places where that program code is actually using the code in question (needle). If I were starting a product from scratch and there was a central code table I would probably code against a set of views in anticipation of the above event, so the DBA ends up creating 1 objects anyway. On the other hand: 1. Everywhere I have worked, there has been a central code table of some sort. 2. In all cases that code table was put in place by the DBAs, not the developers, because they didn't want all those tables and were not really hung up on referential integrity that the application was enforcing anyway. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Norrell, Brian 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).
RE: Single Code Table or Separate Code tables dilemma
at last i convinced my duhvelopers to go for individual tables. -Original Message- From: Steve Orr [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 22, 2001 5:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: Single Code Table or Separate Code tables dilemma Regarding "lookup" or validation tables and more complex "rules" tables... do they ALL go into your master code table? For instance, what about a "code table" for all valid U.S. zip codes? Do they go into your master code table? we r in the process for defining country---state/province---city and airports tables. there would be another 30-40 masters and all in different tables :-) sweet smell of victory State abbreviations too? What about valid city/state/zip code combinations which you can get from the U.S.P.O.? At what point are validation/lookup/rules data not in the master code table and how do you decide? i blive every logical entity should hv its seperate physical presence. but then every solution is an "Engineering Compromise". So i wont mind some give and take. but its fun to have an occasional fight with a duhveloper. The code table technique may be fine for smaller apps but couldn't it become unwieldy for larger apps with 5000+ tables? Of which 1-3000 would be "lookup/validation tables?" Do Oracle ERP or SAP ERP apps do this? Curious. check out PSAPOOLD tablespace in http://www.oreilly.com/catalog/sapadm/chapter/ch01.html#18406 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Ghosalkar 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).
RE: Single Code Table or Separate Code tables dilemma
Check "The Data Warehouse Lifecycle Toolkit" by Ralph Kimball for the definitive answer. -Daniel -Original Message- Sent: Wednesday, March 21, 2001 7:32 PM To: Oracledba (E-mail); ORACLE-L (E-mail) Guys, We r working on a Datawarehouse solution. Our Duhvelopers want to merge all code tables into a single table by adding a codetype column. with reference to this, i came across this article from Steve's site http://www.ixora.com.au/tips/design/meta-data.htm i want to put them into different individual code tables instead of a single table, for the foll reasons. 1. specific attributes for a particular code type is logically and physically seperated from other code types. 2. a table lock affects only the concerned code table 3. granular control over the individual code table i am short of arguments wld be grateful, if ull can advise me which would be better from performance perspective. -Mandar Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daniel Harron 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).
