|
|
 | | From: | BG | | Subject: | Normalization Question | | Date: | Thu, 20 Jan 2005 21:36:05 +0100 |
|
|
 | 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)
|
|
|
