Normailzation for large table for school visits

[redsanders]

As central office personnel visit our schools, they are to log details of
each visit. For example, during a visit, one may observe just the Special
Ed. features of a class. Or, during that SAME visit, s/he may also observe
how science, math or social studies is being taught to the Sp. Ed. students
in that class. Additionally, during that SAME visit s/he may observe small
group and large group instruction, and is to take note of such. Furthermore,
the visitor is to use narratives to enter: 1. The Objective being taught; 2.
A summary of activities observed (including comments about those activities)
and; 3. Recommendations. Members from other departments (math, technology,
bilingual, etc.) would have similar experiences.

I've created tbl_Visitors which contains five fields: Employee_Num
(Primary), Last, First, Department and Position. (The last two are lookups
from tbl_dept and tbl_position.). This table is linked to a tbl_visits, with
a one to many relationship, with the Employee_Num being the foreign key in
the tbl_visits.

But, here's where get worred. As currently designed, each record in the
tbl_visits has 22 check box fields (so as to indicate content area(s) and
methods(s) of instruction observed, as described above). Furthermore, each
record has three memo fields for the three narratives described above. Once
launced, I fully expect requests to for the inclusion of even more features
observed during a visit!!

From all that I read, I'm asking for trouble. I have too many fields in the
table, and am risking corruption by using memo fields, especially three per
record. Plans are for almost 60 people to use this database. I am prepared
to create a back end for tables on a shared drive. and a front en on each
user's computer for the remaining components of the database. But, if I have
poorly normalized tables, I expect this thing to crash and burn & I'll have
to leave town! I just don't comprehend normailzation sufficiently to apply
its principals here. ANY help in redesign would be MOST appreciated.

 

[Beetle]

You're right. You're asking for trouble. You can look at
http://allenbrowne.com/casu-23.html
for an explanation of why you shouldn't use a bunch of yes/no fields to
store preferences/data. There is also alot of other good information there
about table design, relationships, etc.

At the very least, it sounds like you need to add some additional tables for
content area, methods of instruction, and possibly one for the narratives.

HTH

 

[Steve]

FYI in case you are interested ---

A service I provide is to design the table structure of a database for a
customer. I have done this for numerous customers. My fee is very
reasonable. I provide a map of the tables that shows all the tables in the
database, all the fields in each table, all the relationships between the
tables and the type of relationship for each relationship. The tables are
arranged on the map generally as the flow of information in the database. I
create a map of the tables for every database I do. The map visually shows
what forms and subforms are needed for data entry, shows what special forms
and subforms can be created for dispaying data in the database and shows
what reports and subreports can be created from the data in the database.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

You really are not very bright. How many times do you have to be told that
your unethical practises are unwelcome.

These newgroups are for FREE peer to peer support.

John... Visio MVP

 

[Michael Gramelspacher]

As central office personnel visit our schools, they are to log details of
each visit. For example, during a visit, one may observe just the Special
Ed. features of a class. Or, during that SAME visit, s/he may also observe
how science, math or social studies is being taught to the Sp. Ed. students
in that class. Additionally, during that SAME visit s/he may observe small
group and large group instruction, and is to take note of such. Furthermore,
the visitor is to use narratives to enter: 1. The Objective being taught; 2.
A summary of activities observed (including comments about those activities)
and; 3. Recommendations. Members from other departments (math, technology,
bilingual, etc.) would have similar experiences.

I've created tbl_Visitors which contains five fields: Employee_Num
(Primary), Last, First, Department and Position. (The last two are lookups
from tbl_dept and tbl_position.). This table is linked to a tbl_visits, with
a one to many relationship, with the Employee_Num being the foreign key in
the tbl_visits.

But, here's where get worred. As currently designed, each record in the
tbl_visits has 22 check box fields (so as to indicate content area(s) and
methods(s) of instruction observed, as described above). Furthermore, each
record has three memo fields for the three narratives described above. Once
launced, I fully expect requests to for the inclusion of even more features
observed during a visit!!

From all that I read, I'm asking for trouble. I have too many fields in the
table, and am risking corruption by using memo fields, especially three per
record. Plans are for almost 60 people to use this database. I am prepared
to create a back end for tables on a shared drive. and a front en on each
user's computer for the remaining components of the database. But, if I have
poorly normalized tables, I expect this thing to crash and burn & I'll have
to leave town! I just don't comprehend normailzation sufficiently to apply
its principals here. ANY help in redesign would be MOST appreciated.

This is what occurrs to me. You may not like multiple column key, so I suppose
you could just create unique indices on the key columns and use an autonumber
surrogate key to link tables. This assumes thtat what is being visited is
classrooms, and observations are made about about classrooms. Consider this as
just some example.

Sub CreateTables()

With CurrentProject.Connection

..Execute _
"CREATE TABLE Personnel" & _
" (personnel_nbr INTEGER NOT NULL" & _
", person_name VARCHAR (50) NOT NULL" & _
", PRIMARY KEY (personnel_nbr));"

..Execute _
"CREATE TABLE PositionTypes" & _
" (position_type VARCHAR (50) NOT NULL" & _
", PRIMARY KEY (position_type));"

..Execute _
"CREATE TABLE Positions" & _
" (position_nbr INTEGER NOT NULL" & _
", position_title VARCHAR (50) NOT NULL" & _
", position_type VARCHAR (50) NOT NULL" & _
" REFERENCES PositionTypes (position_type)" & _
", PRIMARY KEY (position_nbr));"


..Execute _
"CREATE TABLE PersonnelPositions" & _
" (personnel_nbr INTEGER NOT NULL" & _
" REFERENCES Personnel (personnel_nbr)" & _
", position_nbr INTEGER NOT NULL" & _
" REFERENCES Positions (position_nbr)" & _
", position_start_date DATETIME NOT NULL" & _
", position_end_date DATETIME NULL" & _
", PRIMARY KEY (personnel_nbr, position_nbr" & _
", position_start_date));"

..Execute _
"CREATE TABLE Classes" & _
" (class_nbr INTEGER NOT NULL" & _
", class_name VARCHAR (50) NOT NULL" & _
", PRIMARY KEY (class_nbr));"

..Execute _
"CREATE TABLE Schools" & _
" (school_nbr INTEGER NOT NULL" & _
", school_name VARCHAR (50) NOT NULL" & _
", school_type VARCHAR (50) NOT NULL" & _
", PRIMARY KEY (school_nbr));"

..Execute _
"CREATE TABLE LocationTypes" & _
"(location_type VARCHAR (50) NOT NULL" & _
", PRIMARY KEY (location_type));"

..Execute _
"CREATE TABLE Locations" & _
" (location_nbr INTEGER NOT NULL" & _
", location_name VARCHAR (50) NOT NULL" & _
", location_type VARCHAR (50) NOT NULL" & _
" REFERENCES LocationTypes (location_type)" & _
", PRIMARY KEY (location_nbr));"


..Execute _
"CREATE TABLE PersonnelLocations" & _
" (personnel_nbr INTEGER NOT NULL" & _
" REFERENCES Personnel (personnel_nbr)" & _
", location_nbr INTEGER NOT NULL" & _
" REFERENCES Locations (location_nbr)" & _
", assign_date DATETIME NOT NULL" & _
", UNIQUE (personnel_nbr,assign_date)" & _
", PRIMARY KEY (personnel_nbr, location_nbr" & _
", assign_date));"

..Execute _
"CREATE TABLE InstructionTypes" & _
" (instruction_type VARCHAR (50) NOT NULL" & _
", PRIMARY KEY (instruction_type));"

..Execute _
"CREATE TABLE StaffSchoolVisits" & _
" (school_nbr INTEGER NOT NULL" & _
" REFERENCES Locations (location_nbr)" & _
", personnel_nbr INTEGER NOT NULL" & _
" REFERENCES Personnel (personnel_nbr)" & _
", visit_start_date DATETIME NOT NULL" & _
", UNIQUE (personnel_nbr, visit_start_date)" & _
", PRIMARY KEY (school_nbr, personnel_nbr" & _
", visit_start_date));"

..Execute _
"CREATE TABLE TeacherClasses" & _
" (teacher_nbr INTEGER NOT NULL" & _
" REFERENCES Personnel (personnel_nbr)" & _
", class_nbr INTEGER NOT NULL" & _
" REFERENCES Classes (class_nbr)" & _
", class_start_date DATETIME NOT NULL" & _
", class_end_date DATETIME NULL" & _
", PRIMARY KEY (teacher_nbr, class_nbr" & _
", class_start_date));"

..Execute _
"CREATE TABLE SchoolClassVisits" & _
" (school_nbr INTEGER NOT NULL" & _
", personnel_nbr INTEGER NOT NULL" & _
", visit_start_date DATETIME NOT NULL" & _
", CONSTRAINT fk_school_staff_visits" & _
" FOREIGN KEY (school_nbr, personnel_nbr" & _
", visit_start_date)" & _
" REFERENCES StaffSchoolVisits (school_nbr" & _
", personnel_nbr, visit_start_date)" & _
", teacher_nbr INTEGER NOT NULL" & _
", class_nbr INTEGER NOT NULL" & _
", class_start_date DATETIME NOT NULL" & _
", CONSTRAINT fk_teacher_classes" & _
" FOREIGN KEY (teacher_nbr, class_nbr,class_start_date)" & _
" REFERENCES TeacherClasses (teacher_nbr, class_nbr" & _
", class_start_date)" & _
", instruction_type VARCHAR (50) NOT NULL" & _
" REFERENCES InstructionTypes (instruction_type)" & _
" ,Observations MEMO NULL" & _
", PRIMARY KEY (school_nbr, personnel_nbr,visit_start_date" & _
", teacher_nbr,class_nbr, class_start_date));"

End With

End Sub

 

[redsanders]

Ok fellows, I have this weekend to take a crack at this using the table
descriptions and references y'all have provided. If I get stumped it's good
to know that you will help. Also, thanks for the warning about Steve. Even
so, I didn't plan on using his service, it is imperative that I develop the
necessary skills and knowledge to make this project work.

 

[Tony Toews [MVP]]

A service I provide is to design the table structure of a database for a
customer. I have done this for numerous customers. My fee is very
reasonable.

Please stop soliciting work in these newsgroups.

Please go away.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Ok fellows, I have this weekend to take a crack at this using the table
descriptions and references y'all have provided. If I get stumped it's good
to know that you will help.

We'll be here over the weekend too.

it is imperative that I develop the
necessary skills and knowledge to make this project work.

Excellent. We like reading people saying that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/