Re: Proposal: 6NF

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sun, 01 Oct 2006 16:33:09 GMT
Message-ID: <95STg.6689$OE1.6081@tornado.ohiordc.rr.com>

Karen Hill wrote:
> Brian Selzer wrote:
>
>

>>multiple tables.  Even if you separated only the NULLable columns into
>>different tables, the effort required to perform many simple queries can
>>become impractical.  For example, if you have a table with 30 columns where
>>15 are NULLable, you would need as many as 16 tables to eliminate NULLs.  As
>>a consequence, every query that would have involved a NULLable column would
>>now require a join.

>
>
> Wrong wrong wrong.
>
> You don't have to seperate data into 16 tables to eliminate nulls..
> Just create a type for each field. For example, let us say "first
> name" is not a varchar but a firstname type. A first name has the
> states: 1. First name was provided. 2. First Name Not Given. 3.
> Does not have a first name. Instead of putting a NULL if someone has
> not provided their first name or does not have one, you would instead
> put in the type "First Name Not Given". With a type, a bool can be
> used to mark whether a the field is in states 1. or (2 , 3).
>
> Example using postgresql:
>
> CREATE TYPE firstname(
> nameProvided bool,
> name VARCHAR
> );
>
> CREATE TABLE foo
> (
> name firstname NOT NULL;
> );
>
> Now with this, if I do know the first name I can do this:
> INSERT INTO foo(name) VALUES ( ('t', "jeff"));
>
> Now if the name is not given the INSERT would look like this:
> INSERT INTO foo(name) VALUES (('f', "FIRST NAME NOT GIVEN"));
>
> Now instead of having a NULL which tells you NOTHING, you have
> something you can actually work with!
>
> For example:
>
> SELECT * FROM foo where name.nameprovided = 'f' AND name.name = 'FIRST
> NAME NOT GIVEN';
>
> You can distinguish between somone who does not have a first name with
> someone who has chosen not to provide you with their first name.
>
> Could this be called 7NF (since 6 is taken) or in honor of Date, the
> Date Normal Form?
>
> regards,
> karen
>

I can't see that you've proposed anything new. In fact, I think you've confused a couple things.

Using TYPE firstname, /firstname.name/ holds a first name -- unless /firstname.nameProvided/ is false, in which case it holds... What? What is '"FIRST NAME NOT GIVEN"?' A magic value? More magic than a NULL not-a-value?

I think you are wrestling with the various flavors of NULL, and by flavors I refer to that many myriad things NULL is supposed to represent: sometimes "Not Applicable," sometimes "Not Known," sometimes "this OUTER JOIN had none." (Or some such.)

You're on the right track when considering TYPEs and the key feature your TYPE system needs is an equality comparison operator that can determine that ('t', 'John'), ('f', 'not given), and ('f', 'not applicable') are different values in /firstname/.

But I see nothing that looks like a proposed normal form. Received on Sun Oct 01 2006 - 11:33:09 CDT