Database Design - Normalisation

[Fev]

Hi
I am busy setting up a database for my market research company, and
have a query about setting up a table to store the Project Detail
information for each research project. While I do have some
understanding of the normalisation process, I also am not sure how to
store this information. The database will store information about the
demographics each research project will target and how many interviews
will be done in each demographic area. Project 1 may only require
interviews of females only in the following age groups 16 - 22; 23 -
30 and 31 - 39 in Regions 1, 2 and 3 for LSM 7, 8, 9 and 10, while
Project 2 may require interviews for both males and females in age
groups 18 - 24; 25 - 35, 36 - 49 and 50 - 65, in Regions 1, 4, 7, 8
and 9 for LSM 6 - 10. For each demographic group I will need to store
how many interviews are required and how many interviews have been
completed.

My first instinct would be to set up my Project Details table with the
following fields:
ProjectDetailID, ProjectID, Gender, AgeGroup, Region, LSM,
NoRequiredInterviews, NoSuccessfulInterviews

Would a crosstab query and report be able to compare the number of
required interviews versus the number of successfull interviews per
demographic group?

I would appreciate any input from more experienced database designers.

Thanks
Heather

 

[John W. Vinson]

Hi
I am busy setting up a database for my market research company, and
have a query about setting up a table to store the Project Detail
information for each research project. While I do have some
understanding of the normalisation process, I also am not sure how to
store this information. The database will store information about the
demographics each research project will target and how many interviews
will be done in each demographic area. Project 1 may only require
interviews of females only in the following age groups 16 - 22; 23 -
30 and 31 - 39 in Regions 1, 2 and 3 for LSM 7, 8, 9 and 10, while
Project 2 may require interviews for both males and females in age
groups 18 - 24; 25 - 35, 36 - 49 and 50 - 65, in Regions 1, 4, 7, 8
and 9 for LSM 6 - 10. For each demographic group I will need to store
how many interviews are required and how many interviews have been
completed.

My first instinct would be to set up my Project Details table with the
following fields:
ProjectDetailID, ProjectID, Gender, AgeGroup, Region, LSM,
NoRequiredInterviews, NoSuccessfulInterviews

Would a crosstab query and report be able to compare the number of
required interviews versus the number of successfull interviews per
demographic group?

I would appreciate any input from more experienced database designers.

Thanks
Heather

I've got a few suggestions. One would be that you store the age range as a
pair of numbers (low and high) rather than a single hyphenated text field;
this will make it easier to create a non-equi join Query selecting only ages
within the range.

Another would be that you not store the NoSuccessfulInterviews at all;
instead, have a table of interviews with fields for the ProjectNo, a link to
the identity of the interviewee (if you keep that information) and the results
of the inteview. You could then do a Totals query to count the number of
records in this table, rather than typing in a count.

Finally, the ProjectDetails table needs to be split into three (or more)
tables - you have one to many relationships from Projects to AgeGroups and
(independently) to Regions. I'm not sure what LSM means but that might be
another.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Fev]

I've got a few suggestions. One would be that you store the age range as a
pair of numbers (low and high) rather than a single hyphenated text field;
this will make it easier to create a non-equi join Query selecting only ages
within the range.

Another would be that you not store the NoSuccessfulInterviews at all;
instead, have a table of interviews with fields for the ProjectNo, a linkto
the identity of the interviewee (if you keep that information) and the results
of the inteview. You could then do a Totals query to count the number of
records in this table, rather than typing in a count.

Finally, the ProjectDetails table needs to be split into three (or more)
tables - you have one to many relationships from Projects to AgeGroups and
(independently) to Regions. I'm not sure what LSM means but that might be
another.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Hi John
Thanks so much for this advice. LSM (Living Standards Measure - It is
a means of segmenting the South African market that cuts across race,
gender, age or any other variable used to categorise people. Instead,
it groups people according to their living standards.), and in my post
I forgot to mention an ethnic grouping as well. One of the problems is
that most of the data is currently being managed in Excel, and input
and reports are largely in "cross-tab" like formats, however all
manually created. I am trying to provide a more automated and
efficient approach using Access 2007. I would also like to ease the
transition to the new system by trying to aproximate (and translate)
our current look and feel of the Excel Input and Reports into the
Access forms and reports.

Your response has provided me with a whole new approach, that will
also help solve some of the data input issues that I was confronting.
I will test this format and see how it performs on my sample data.
Thanks
Heather