Thank you all who took the time to answer the above question. Now, I am going to allow columns types as floating. I copied all the replies to this question so that somebody could benefit from this. Once again thanks to Dick Goulet, Kevin Lange, David A. Barbour, Galen Boyer. Replies to the above question. ---------------------------------------------------------------------------- ----------------------- The boss is somewhat right, but are you absolutely certain that the current program interface to the data will remain forever? What is the likelihood of someone having to "correct" the data via SQL*Plus or some other method where the restrictions will have no effect. The main reason of applying the constraints at the database level is that is the lowest level of granularity for the data and consequently the one place where one can be absolutely certain that business rules get applied irregardless of where the data comes from. Dick Goulet -------------------------------------------------------- I hate to be on the side of management .... but much time is spent going back and resizing fields and applications because the field defined is no longer big enough. Been there and done that many times. Kevin Lange ----------------------------------------------------- In this case, I think your boss is right. If you feel the need to have some integrity checking at the DB level, use FKs. CHECK and possibly NOT NULL constraints will subject you to the type of application/database maintenance a well-thought out plan should endeavor to avoid. Just be sure that your use of the foreign key is tightly tied to business rules. As an example, a three digit location code in a telecommunications package could tie back to an NPA_NXX_PAIR_CODE table. If for whatever reason this becomes a four-digit number (skip the technical explanations of why that will NEVER happen, just think of the logical approach - we dial 1 to get long-distance, why not a an additional digit(s) to access a region, kind of like what you do now if you use one of those 10-10 numbers), you will still have a valid app (although you may have to do some updates on existing records). David A. Barbour ---------------------------------------------------------------------- on Wed, 15 Aug 2001, [EMAIL PROTECTED] wrote: > In one of our applications, my boss wants to define all the numeric columns as NUMBER or leave it as floating point. He > wants to define all the tables like this wherever numeric column is defined in the table. > > Example: > > Location_ID NUMBER > > We know, from our application character, that this location_ID > would never cross beyond 3 digits. Today you know this, but it could change, so allow for it. > Also, we know that this specific column does not require any digits to the right of the decimal point. ---> (This column is only an example). It sounds like you are going to have intelligence in this key. I would recommend against that one. > He does not want to specify any precision or scale for the number type columns. His point is --- Oracle would use only > that much space depending on the actual number of digits he enters into the column. Thus, he is not wasting any space. But Oracle will have a bit of a performance hit when it joins to this table on a number (I assume alot of these columns will be PK's). I believe it is the same penalty that Oracle encounters when it has to query a varchar vs char field, it has to figure out how far to look with the varchar where it doesn't with a char, and the opposite argument also holds, the varchar is much more flexible than the char. So, if these are going to be used to join tables together alot, you might want to consider a straight integer sequence. > Also, he says, during the beginning of application, we might not know the maximum limits for number column. Hence, leaving > them as floating point ( Location_ID NUMBER), gives him flexibility and he need not change the number column precision > or scale during the entire life cycle of the application. Once again, it sounds like you have intelligence in these keys. > He says, it is upto the application program (JAVA/EJB) to control and check the maximum length permissible against a > column depending on the business rules. Yes and no. It is ultimately up to the DB to protect itself, but the app shouldn't rely on the DB's protection to get it correct. -- Author: Galen Boyer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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).
