A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do you normalize a table?



 
 
Thread Tools Display Modes
  #1  
Old January 19th, 2010, 03:11 AM posted to microsoft.public.access.tablesdbdesign
Maki
external usenet poster
 
Posts: 38
Default How do you normalize a table?

I've been user of Access 2003 for a while and am ok using databases but
somehow lack practical knowledge when it comes to redesigning or normalizing
existing database and hope someone can guide me step-by-step.

I'm trying to redesign our dog training club membership database.
Out membership is based on household but we have only one set of handler
name, mobile (cell) phone and email fields in our enrolment form and such was
the case with our database Member Table. Soon we noticed people putting
multiple names, mobile numbers and emails so we added second set of handler
fields in the Member table to accommodate this.

I decided to normaliise and create a new Handler table, put all surname,
first_name, mobile_no and email records acroos from Member table and link
with Member_ID as foreign key, while each record will have Handler_ID as the
primary key.

Could someone let me know how to go about from here? I'm planning to do
this at home using Access 2007, although ultimately it will have to be made
compatible with Access 2003 as that's the version used with our Club computer.

Thank you.

--
Maki @ Canberra.AU
  #2  
Old January 19th, 2010, 04:46 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default How do you normalize a table?

If a dog could be owned by either an individual or a household (consisting
of multiple individuals), you might like to use the structure suggested
he
People in households and companies - Modeling human relationship
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Maki" wrote in message
...
I've been user of Access 2003 for a while and am ok using databases but
somehow lack practical knowledge when it comes to redesigning or
normalizing
existing database and hope someone can guide me step-by-step.

I'm trying to redesign our dog training club membership database.
Out membership is based on household but we have only one set of handler
name, mobile (cell) phone and email fields in our enrolment form and such
was
the case with our database Member Table. Soon we noticed people putting
multiple names, mobile numbers and emails so we added second set of
handler
fields in the Member table to accommodate this.

I decided to normaliise and create a new Handler table, put all surname,
first_name, mobile_no and email records acroos from Member table and link
with Member_ID as foreign key, while each record will have Handler_ID as
the
primary key.

Could someone let me know how to go about from here? I'm planning to do
this at home using Access 2007, although ultimately it will have to be
made
compatible with Access 2003 as that's the version used with our Club
computer.

Thank you.

--
Maki @ Canberra.AU


  #3  
Old January 19th, 2010, 07:39 AM posted to microsoft.public.access.tablesdbdesign
Maki
external usenet poster
 
Posts: 38
Default How do you normalize a table?

Thank you, Allen, for the posting.

I guess our circumstances are not as complex as your sample, as any member
(household) would have at least one handler (individual) and a dog.
(Our definistion of household is a group of people who share the same
residential address.)

My question here is how exactly to put records of similar fields (e.g.
"surname" and "surname_2", "mobile_no" and "mobile_no_2", etc.) from
tblMember and get them in one field ("surname", "mobile_no", etc.) in the new
table (tblHandler), allocate new primary key (Hanlder_ID) and foreign key
(Member_ID) to each handler record.

Should I be using table analyzer first or should I create Make-Table action
query?

I guess the design decision has already been made so the question could have
been better served in "general question" forum rather than "Database design",
in which case, my apology.

--
Maki @ Canberra.AU


"Allen Browne" wrote:

If a dog could be owned by either an individual or a household (consisting
of multiple individuals), you might like to use the structure suggested
he
People in households and companies - Modeling human relationship
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Maki" wrote in message
...
I've been user of Access 2003 for a while and am ok using databases but
somehow lack practical knowledge when it comes to redesigning or
normalizing
existing database and hope someone can guide me step-by-step.

I'm trying to redesign our dog training club membership database.
Out membership is based on household but we have only one set of handler
name, mobile (cell) phone and email fields in our enrolment form and such
was
the case with our database Member Table. Soon we noticed people putting
multiple names, mobile numbers and emails so we added second set of
handler
fields in the Member table to accommodate this.

I decided to normaliise and create a new Handler table, put all surname,
first_name, mobile_no and email records acroos from Member table and link
with Member_ID as foreign key, while each record will have Handler_ID as
the
primary key.

Could someone let me know how to go about from here? I'm planning to do
this at home using Access 2007, although ultimately it will have to be
made
compatible with Access 2003 as that's the version used with our Club
computer.

Thank you.

--
Maki @ Canberra.AU


.

  #4  
Old January 19th, 2010, 08:42 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default How do you normalize a table?

Do you *really* want to use repeating fields like Firstname2, Surname2,
Mobile2, Email2???

You're creating unnecessary problems with that kind of structure. Even for a
simple search to find Fred Smith, which fields do you look in? And what do
you do the day you need a household that has a 3rd person?

I think you will be better off using a Household table (just the address
field), and a Person table, so one household can contain many people.

(Ignore the link I gave you. It's solving a question you haven't faced yet.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Maki" wrote in message
...
Thank you, Allen, for the posting.

I guess our circumstances are not as complex as your sample, as any member
(household) would have at least one handler (individual) and a dog.
(Our definistion of household is a group of people who share the same
residential address.)

My question here is how exactly to put records of similar fields (e.g.
"surname" and "surname_2", "mobile_no" and "mobile_no_2", etc.) from
tblMember and get them in one field ("surname", "mobile_no", etc.) in the
new
table (tblHandler), allocate new primary key (Hanlder_ID) and foreign key
(Member_ID) to each handler record.

Should I be using table analyzer first or should I create Make-Table
action
query?

I guess the design decision has already been made so the question could
have
been better served in "general question" forum rather than "Database
design",
in which case, my apology.

--
Maki @ Canberra.AU


"Allen Browne" wrote:

If a dog could be owned by either an individual or a household
(consisting
of multiple individuals), you might like to use the structure suggested
he
People in households and companies - Modeling human relationship
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Maki" wrote in message
...
I've been user of Access 2003 for a while and am ok using databases but
somehow lack practical knowledge when it comes to redesigning or
normalizing
existing database and hope someone can guide me step-by-step.

I'm trying to redesign our dog training club membership database.
Out membership is based on household but we have only one set of
handler
name, mobile (cell) phone and email fields in our enrolment form and
such
was
the case with our database Member Table. Soon we noticed people
putting
multiple names, mobile numbers and emails so we added second set of
handler
fields in the Member table to accommodate this.

I decided to normaliise and create a new Handler table, put all
surname,
first_name, mobile_no and email records acroos from Member table and
link
with Member_ID as foreign key, while each record will have Handler_ID
as
the
primary key.

Could someone let me know how to go about from here? I'm planning to
do
this at home using Access 2007, although ultimately it will have to be
made
compatible with Access 2003 as that's the version used with our Club
computer.

Thank you.

--
Maki @ Canberra.AU


.

  #5  
Old January 19th, 2010, 06:25 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How do you normalize a table?

On Mon, 18 Jan 2010 22:39:01 -0800, Maki
wrote:

Thank you, Allen, for the posting.

I guess our circumstances are not as complex as your sample, as any member
(household) would have at least one handler (individual) and a dog.
(Our definistion of household is a group of people who share the same
residential address.)

My question here is how exactly to put records of similar fields (e.g.
"surname" and "surname_2", "mobile_no" and "mobile_no_2", etc.) from
tblMember and get them in one field ("surname", "mobile_no", etc.) in the new
table (tblHandler), allocate new primary key (Hanlder_ID) and foreign key
(Member_ID) to each handler record.

Should I be using table analyzer first or should I create Make-Table action
query?

I guess the design decision has already been made so the question could have
been better served in "general question" forum rather than "Database design",
in which case, my apology.


I think Allen may have misinterpreted your question: if I understand, you
already have a denormalized table with fields like surname_2 and so on, and
you are (hooray!) normalizing it, right?

If so, I'd suggest creating your new Handler table with all the appropriate
fields (you might want to post the Household and Handler table fields here if
you'ld like more eyes on them). Create it in the table design window, empty;
use an Autonumber for Handler_ID, and a long integer link to Member_ID (which
I'm presuming is itself an autonumber). Use the Relationships Window to
establish the link and to enforce referential integrity.

You can then create a "Normalizing Union Query" based on your Member table.
You need to go to the SQL window for this, you can't do it in the query grid.
It would be *something like* (depending on the actual structure of Members):

SELECT Member_ID, Surname, Forename, Mobile_No, etc
FROM Members
WHERE Surname IS NOT NULL OR Mobile_No IS NOT NULL
UNION ALL
SELECT Member_ID, Surname_2, Forename_2, Mobile_No_2, etc
FROM Members
WHERE Surname_2 IS NOT NULL OR Mobile_No_2 IS NOT NULL
UNION ALL
etc through all the repeating fields

Take a look at this query as a datasheet and see if it captures the
information you want in the Handler table. If so, create a new Append query
based on *this* query, appending Member_ID to Member_ID, Surname to Surname
etc. in the new Handler table. Run the append query.

One concern I'd have here is that my suggestion implicitly links "Surname"
with "Mobile_no", and "Surname_2" with "Mobile_no_2". That might not be the
case! A family might have three people and three cellphones, but you may not
want to make assumptions about which family member uses which phone. I'd
actually suggest storing phone numbers in a new table linked one-to-many to
the Members table, unless you have explicit informaton that lets you link one
phone to one person.
--

John W. Vinson [MVP]
  #6  
Old January 20th, 2010, 08:00 AM posted to microsoft.public.access.tablesdbdesign
Maki
external usenet poster
 
Posts: 38
Default How do you normalize a table?

Yes, yes, thank you John.
What Allen said "Do you "really" want..." was exactly what we currently have
and he also described the kind of problem we are having at the moment!

I'll experiment with the Normalizing Union Query that you indicated and
report back the results.

Thank you, John, Allen, for taking time to respond.

Cheers,
--
Maki @ Canberra.AU


"John W. Vinson" wrote:


I think Allen may have misinterpreted your question: if I understand, you
already have a denormalized table with fields like surname_2 and so on, and
you are (hooray!) normalizing it, right?

If so, I'd suggest creating your new Handler table with all the appropriate
fields (you might want to post the Household and Handler table fields here if
you'ld like more eyes on them). Create it in the table design window, empty;
use an Autonumber for Handler_ID, and a long integer link to Member_ID (which
I'm presuming is itself an autonumber). Use the Relationships Window to
establish the link and to enforce referential integrity.

You can then create a "Normalizing Union Query" based on your Member table.
You need to go to the SQL window for this, you can't do it in the query grid.
It would be *something like* (depending on the actual structure of Members):

SELECT Member_ID, Surname, Forename, Mobile_No, etc
FROM Members
WHERE Surname IS NOT NULL OR Mobile_No IS NOT NULL
UNION ALL
SELECT Member_ID, Surname_2, Forename_2, Mobile_No_2, etc
FROM Members
WHERE Surname_2 IS NOT NULL OR Mobile_No_2 IS NOT NULL
UNION ALL
etc through all the repeating fields

Take a look at this query as a datasheet and see if it captures the
information you want in the Handler table. If so, create a new Append query
based on *this* query, appending Member_ID to Member_ID, Surname to Surname
etc. in the new Handler table. Run the append query.

One concern I'd have here is that my suggestion implicitly links "Surname"
with "Mobile_no", and "Surname_2" with "Mobile_no_2". That might not be the
case! A family might have three people and three cellphones, but you may not
want to make assumptions about which family member uses which phone. I'd
actually suggest storing phone numbers in a new table linked one-to-many to
the Members table, unless you have explicit informaton that lets you link one
phone to one person.
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.