Re: Proposal: 6NF

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 Received on Sun Oct 01 2006 - 00:55:02 CEST