Hey All,

Here's an example table with a few fields. To abide by the rules of
normalization, should the phone & date fields be placed into their own
tables? Even though the data will not be repeated in any other table?

TblMembers
---------------
fname
lname
address
city
state
work_phone
cell_phone
home_phone
add_date
delete_date
promote_date
update_date

Thanks,

Gordon

From:	Jerry Gitomer
Subject:	Re: Normalization Question
Date:	Fri, 21 Jan 2005 16:14:43 GMT

BG wrote:
> Hey All,
>
> Here's an example table with a few fields. To abide by the rules of
> normalization, should the phone & date fields be placed into their own
> tables? Even though the data will not be repeated in any other table?
>
> TblMembers
> ---------------
> fname
> lname
> address
> city
> state
> work_phone
> cell_phone
> home_phone
> add_date
> delete_date
> promote_date
> update_date
>
>
> Thanks,
>
> Gordon
>
>
No, you should not create separate tables for the various phone
numbers and dates because they do not form repeating groups, are
clearly differentiated and are not used elsewhere.

HTH
Jerry

From:	Ethan T
Subject:	Re: Normalization Question
Date:	Sat, 22 Jan 2005 14:38:22 -0500

BG wrote:
> TblMembers
> ---------------
> fname
> lname
> address
> city
> state
> work_phone
> cell_phone
> home_phone
> add_date
> delete_date
> promote_date
> update_date

This is where XML is so beautiful. Is there a database out there that
works like XML, in that it allows any field to have children fields of
any type? (in a DB, you need a separate table for each child type and a
key to relate the two) If you could have a database with the structural
power of XML, that would be awesome. Does one exist?

--
eth'nT

From:	Jerry Gitomer
Subject:	Re: Normalization Question
Date:	Sat, 22 Jan 2005 19:54:24 GMT

Ethan T wrote:
> BG wrote:
>
>> TblMembers
>> ---------------
>> fname
>> lname
>> address
>> city
>> state
>> work_phone
>> cell_phone
>> home_phone
>> add_date
>> delete_date
>> promote_date
>> update_date
>
>
> This is where XML is so beautiful. Is there a database out there that
> works like XML, in that it allows any field to have children fields of
> any type? (in a DB, you need a separate table for each child type and a
> key to relate the two) If you could have a database with the structural
> power of XML, that would be awesome. Does one exist?
>
Lots of them exist. They were displaced by Relational Data Base
Management Systems.

I think it is a question of survival of the fittest with the
marketplace in general having decided that RDBMS were better
suited to the needs of the market as a whole.

Jerry

From:	Neo
Subject:	Re: Normalization Question
Date:	22 Jan 2005 14:29:12 -0800

> Does one exist?

Below script for small experimental db models the original poster's
schema in a fairly normalized manner. It then adds a single person
named John who lives on 123 Main St, Dallas, TX; has home, work and
cell#s 111-1111; an additional cell# 222-2222; was added, updated,
promoted and deleted on 1/1/2005.

// Create items in directory to classify things.
(CREATE *person.item ~in = dir)
(CREATE *address.item ~in = dir)
(CREATE *street.item ~in = dir)
(CREATE *city.item ~in = dir)
(CREATE *state.item ~in = dir)
(CREATE *phone#.item ~in = dir)
(CREATE *home.item ~in = dir)
(CREATE *work.item ~in = dir)
(CREATE *cell.item ~in = dir)
(CREATE *date.item ~in = dir)

// Create necessary adverbs
// Note: db already knows first and last
(CREATE *add.cls = adverb)
(CREATE *promote.cls = adverb)
(CREATE *update.cls = adverb)
(CREATE *delete.cls = adverb)

// Create name John
(CREATE *john.cls = name)

// Create state Texas
(CREATE *texas.cls = state)

// Create city Dallas
(CREATE *dallas.cls = city)
(CREATE it.state = texas)

// Create steet 123 Main St
(CREATE *123 main st.cls = street)

// Create address: 123 Main St, Dallas, TX
(CREATE *.cls = address)
(CREATE it.street = 123 main st)
(CREATE it.city = dallas)

// Create two phone#s
(CREATE *111-1111.cls = phone#)
(CREATE *222-2222.cls = phone#)

// Create a date
(CREATE *1/1/2005.cls = date)

// Create a person named John John.
// Note: His first and last names are the same.
(CREATE *.cls = person)
(CREATE it.name,first = john)
(CREATE it.name,last = john)
(CREATE it.address = (SELECT %.street=123 main st))

(CREATE it.phone#,home = 111-1111)
(CREATE it.phone#,work = 111-1111)
(CREATE it.phone#,cell = 111-1111)
(CREATE it.phone#,cell = 222-2222)

(CREATE it.date,add = 1/1/2005)
(CREATE it.date,update = 1/1/2005)
(CREATE it.date,promote = 1/1/2005)
(CREATE it.date,delete = 1/1/2005)

// Find a person whose last name is John
// and was updated on 1/1/2005.
// Finds person named John John.
(SELECT %.cls = person & %.name,last=john & %.date,update=1/1/2005)

From:	ak_tiredofspam at yahoo.com
Subject:	Re: Normalization Question
Date:	21 Jan 2005 20:21:34 -0800

>> similar for dates - a date type and value. A foreign key to the
member would
be needed.
... <<

this is a very very bad advice. This approach is a disaster, and I've
personally seen it fail twice.

Why don't you go on Ask Tom web site, and search on "Do not use Generic
Data Models"

From:	Lee Fesperman
Subject:	Re: Normalization Question
Date:	Fri, 21 Jan 2005 02:02:17 GMT

BG wrote:
> Here's an example table with a few fields. To abide by the rules of
> normalization, should the phone & date fields be placed into their own
> tables? Even though the data will not be repeated in any other table?
>
> TblMembers
> ---------------
> fname
> lname
> address
> city
> state
> work_phone
> cell_phone
> home_phone
> add_date
> delete_date
> promote_date
> update_date

What normalization rules are you referencing? Assuming a proper primary key, your table
seems normalized. As Joe would say, the actual DDL would make it much easier to answer
your question.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

From:	EricF
Subject:	Re: Normalization Question
Date:	Fri, 21 Jan 2005 04:53:08 GMT

In article <41F060C4.3E85@ix.netcom.com>, Lee Fesperman wrote:
>BG wrote:
>> Here's an example table with a few fields. To abide by the rules of
>> normalization, should the phone & date fields be placed into their own
>> tables? Even though the data will not be repeated in any other table?
>>
>> TblMembers
>> ---------------
>> fname
>> lname
>> address
>> city
>> state
>> work_phone
>> cell_phone
>> home_phone
>> add_date
>> delete_date
>> promote_date
>> update_date
>
>What normalization rules are you referencing? Assuming a proper primary key,
> your table
>seems normalized. As Joe would say, the actual DDL would make it much easier to
> answer
>your question.

I probably shouldn't presume to answer for BG but I could see something like
...

table phones
type number
work 123-456-7890
cell 123-456-0987
home 123-456-7689

similar for dates - a date type and value. A foreign key to the member would
be needed.

eric

From:	Lee Fesperman
Subject:	Re: Normalization Question
Date:	Fri, 21 Jan 2005 09:14:46 GMT

EricF wrote:
>
> In article <41F060C4.3E85@ix.netcom.com>, Lee Fesperman wrote:
> >BG wrote:
> >> Here's an example table with a few fields. To abide by the rules of
> >> normalization, should the phone & date fields be placed into their own
> >> tables? Even though the data will not be repeated in any other table?
> >>
> >> TblMembers
> >> ---------------
> >> fname
> >> lname
> >> address
> >> city
> >> state
> >> work_phone
> >> cell_phone
> >> home_phone
> >> add_date
> >> delete_date
> >> promote_date
> >> update_date
> >
> >What normalization rules are you referencing? Assuming a proper primary key,
> > your table
> >seems normalized. As Joe would say, the actual DDL would make it much easier to
> > answer
> >your question.
>
> I probably shouldn't presume to answer for BG but I could see something like
> ..
>
> table phones
> type number
> work 123-456-7890
> cell 123-456-0987
> home 123-456-7689
>
> similar for dates - a date type and value. A foreign key to the member would
> be needed.

Pending clarification from the op, I would call that over-normalization. What is the
purpose of it? Each phone and date column is apparently functionally dependent on the
primary key of the main table.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

From:	Ja Lar
Subject:	Re: Normalization Question
Date:	Fri, 21 Jan 2005 13:00:11 +0100

"Lee Fesperman" skrev i en meddelelse
news:41F0C622.4171@ix.netcom.com...
> EricF wrote:
> >
> > In article <41F060C4.3E85@ix.netcom.com>, Lee Fesperman
wrote:
> > >BG wrote:
> > >> Here's an example table with a few fields. To abide by the rules of
> > >> normalization, should the phone & date fields be placed into their
own
> > >> tables? Even though the data will not be repeated in any other
table?
> > >>
> > >> TblMembers
> > >> ---------------
> > >> fname
> > >> lname
> > >> address
> > >> city
> > >> state
> > >> work_phone
> > >> cell_phone
> > >> home_phone
> > >> add_date
> > >> delete_date
> > >> promote_date
> > >> update_date
> > >
> > >What normalization rules are you referencing? Assuming a proper primary
key,
> > > your table
> > >seems normalized. As Joe would say, the actual DDL would make it much
easier to
> > > answer
> > >your question.
> >
> > I probably shouldn't presume to answer for BG but I could see something
like
> > ..
> >
> > table phones
> > type number
> > work 123-456-7890
> > cell 123-456-0987
> > home 123-456-7689
> >
> > similar for dates - a date type and value. A foreign key to the member
would
> > be needed.
>
> Pending clarification from the op, I would call that over-normalization.
What is the
> purpose of it? Each phone and date column is apparently functionally
dependent on the
> primary key of the main table.

1) How do you define "over-normalizarion"
2) What is the concept of a "repeating group" in your definition
3) Is "phone" a distinct concept, eg. is "phone data" functionalle
dependent of "phone number"
4) Is 3NF "over-normalization"?

From:	Lee Fesperman
Subject:	Re: Normalization Question
Date:	Fri, 21 Jan 2005 23:27:19 GMT

Ja Lar wrote:
>
> "Lee Fesperman" skrev i en meddelelse
> news:41F0C622.4171@ix.netcom.com..
> > EricF wrote:
> > >
> > > In article <41F060C4.3E85@ix.netcom.com>, Lee Fesperman
> wrote:
> > > >BG wrote:
> > > >> Here's an example table with a few fields. To abide by the
> > > >> rules of normalization, should the phone & date fields
> > > >> be placed into their own tables? Even though the data
> > > >> will not be repeated in any other table?
> > > >>
> > > >> TblMembers
> > > >> ---------------
> > > >> fname
> > > >> lname
> > > >> address
> > > >> city
> > > >> state
> > > >> work_phone
> > > >> cell_phone
> > > >> home_phone
> > > >> add_date
> > > >> delete_date
> > > >> promote_date
> > > >> update_date
> > > >
> > > >What normalization rules are you referencing? Assuming a
> > > >proper primary key, your table seems normalized. As Joe would say,
> > > > the actual DDL would make it much easier to answer your question.
> > >
> > > I probably shouldn't presume to answer for BG but I could see
> > > something like ..
> > >
> > > table phones
> > > type number
> > > work 123-456-7890
> > > cell 123-456-0987
> > > home 123-456-7689
> > >
> > > similar for dates - a date type and value. A foreign key to the
> > > member would be needed.
> >
> > Pending clarification from the op, I would call that
> > over-normalization. What is the purpose of it? Each phone and
> > date column is apparently functionally dependent on the primary
> > key of the main table.
>
> 1) How do you define "over-normalizarion"

In this case, moving columns that are functionally dependent on the primary key to a new
table. I can see only a coupla reasons for doing this:

+ The columns are some type of repeating groups, or there is some potential they will be
changed to repeating groups in the future.

+ The column values are optional, and you wish to avoid using database NULLs.

> 2) What is the concept of a "repeating group" in your definition

I didn't see these columns as being repeating groups, but I could be wrong.

> 3) Is "phone" a distinct concept, eg. is "phone data" functionalle
> dependent of "phone number"

Possibly you could call them distinct, however I don't see normalization applying to
such concepts.

> 4) Is 3NF "over-normalization"?

No, nor is 4NF and 5NF. The table 'seems' to already be in 3NF+.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)