How do you normalize a table?
in [Tables]
|
From: Maki on 18 Jan 2010 21:11 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
From: Allen Browne on 18 Jan 2010 22:46 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 here: 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" <Maki(a)discussions.microsoft.com> wrote in message news:37220B0C-0519-4FC4-86E9-E409490E22B6(a)microsoft.com... > 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
From: Maki on 19 Jan 2010 01:39 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 > here: > 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" <Maki(a)discussions.microsoft.com> wrote in message > news:37220B0C-0519-4FC4-86E9-E409490E22B6(a)microsoft.com... > > 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 > > . >
From: Allen Browne on 19 Jan 2010 02:42 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" <Maki(a)discussions.microsoft.com> wrote in message news:3D43AA3B-F5F1-42E1-88ED-AEC9B61A2018(a)microsoft.com... > 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 >> here: >> 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" <Maki(a)discussions.microsoft.com> wrote in message >> news:37220B0C-0519-4FC4-86E9-E409490E22B6(a)microsoft.com... >> > 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 >> >> . >>
From: John W. Vinson on 19 Jan 2010 12:25
On Mon, 18 Jan 2010 22:39:01 -0800, Maki <Maki(a)discussions.microsoft.com> 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] |