Re: Database design dilemma - not null vs nullable

Well, I'm going to go against the grain of most other respondents and say that you're both wrong.

As is nearly always the case, the truth is in the middle.

Storing spaces is a bad idea. It means you really don't understand your data very well. It causes a lot of problems when writing code as well.

On the other hand, default values have their place.

An open ended date is usually best defined as a very early data, or a very late date. For instance, in a medical claims database, a closed date that is not yet determined is best defined as something like '12/31/9999'.

Why? Those dates are often used in WHERE clauses.

Which query on unclosed claims will return results quicker, one where unclosed is represented by a known date, or where the closed_date column is null?

Making a blanket statement such as 'All columns will be not null' indicates lack of analysis.

But using a known value to represent values that would otherwise be null also has merit.

Jared

On Fri, 5 Jan 2001, Siv Chelakkara wrote:

> Hi all,
>
> We have an outside vendor developing an application, which we will ultimately inherit for maintenance. They are porting this application from a different database platform to Oracle, since we are an Oracle shop. They have decided to make every column
in every table a NOT NULL column. They want to implement default values at the database level for every column (' ', 0 and some low date for varchar2, number and date) and then let the application validate and enforce significant data as required. I am looking for opinions on the pros and cons of this type of approach, both from a database and application point of view.
>
> Thanks,
>
> Siv
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Siv Chelakkara
> INET: Siv.Chelakkara_at_3cc.co.wayne.mi.us
>
> 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: ListGuru_at_fatcity.com (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).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;) Received on Fri Jan 05 2001 - 23:52:34 CST