Six of one, half dozen?

[google3luo359]

Can someone please tell me if this is this a six of one situation or
not?

A db is now set up with one main table housing ID numbers, names, and
grade numbers.
I have four tables to house data for each grade.
As the student moves through the system year by year, their data will
be entered into 9 table, then 10 table then 11 and finally 12.
The tables are identical except for their names and field names.

Is there any advantage to putting all the data into one table, having
four records for each student, and identifying the record by the grade
level?
If there is an advantage could you please explain what it is?

TIA Ric

 

[John Vinson]

Can someone please tell me if this is this a six of one situation or
not?

No. It's a comparison between a spreadsheet (maybe ok for Excel, but
simply wrong for a relational database) and a proper database design.

A db is now set up with one main table housing ID numbers, names, and
grade numbers.
I have four tables to house data for each grade.
As the student moves through the system year by year, their data will
be entered into 9 table, then 10 table then 11 and finally 12.
The tables are identical except for their names and field names.

This design is simply WRONG.

Is there any advantage to putting all the data into one table, having
four records for each student, and identifying the record by the grade
level?
Yes.

If there is an advantage could you please explain what it is?

It avoids redundancy (storing the same data in four different tables);
it makes it unnecessary to "move" the data from table to table; it
makes it much easier to summarize or compare data between grades; it
makes it much easier to find an individual student regardless of her
current grade; ...

John W. Vinson[MVP]

 

[John Nurick]

Hi Ric,

The tables are identical except for their names and field names.

Does this mean
(1) all the tables have exactly the same fields, but corresponding
fields in different tables may have different names, e.g. one table may
have [ID#], while another has [StudentID];
or
(2) each grade needs different information stored, so the tables are in
fact substantially different?

If (1), I'd store the data for all for grades in one table with one
record per student per grade (so the primary key would probably consist
of the two fields Grade and StudentID.

The advantages include: three fewer tables to maintain; it's much easier
to query a student's entire record (using one or two tables instead of
four or five); it's much easier to compare performance across years and
grades; etc.

If (2), it's a judgement call. If the differences between the tables are
small I'd create one table with fields for all four grades. (With big
differences, it might be one table containing all the fields common to
all grades plus four more for the fields unique to each grade - an
approach known as subclassing that captures the advantages listed
above).



I don't know what information you're storing about each grade. If you
have field after field for subjects and marks and comments, the database
would probably benefit from further normalisation.

 

[google3luo359]

I'd first like to thank all who have replied. You folks are great here!

Before I give more details I must explain that this db is not a large
scale project nor is it intended to house important data that must be
queried and accessed regularly.
Data will be entered only twice a year by the students, in memo fields,
and basically just sit there. The data may be viewed by the principal,
but that's about it! Really.
It's meant to simply reduce paperwork/filing etc.

Does this mean
(1) all the tables have exactly the same fields....

I'm not sure I understand you so I'll give details of my tables:

AEP09:
StudNum, 9Goals, 9ActionPlan, 9Review

AEP10:
StudNum, 10Goals, 10ActionPlan, 10Review

etc. (same for AEP11 and AEP12)

If (1):
The advantages include: three fewer tables to maintain; it's much easier
to query a student's entire record (using one or two tables instead of
four or five); it's much easier to compare performance across years and
grades; etc.

I can see the advantages for this.

I don't know what information you're storing about each grade. If you
have field after field for subjects and marks and comments, the database
would probably benefit from further normalisation.

No. As mentioned above, there will be just three large memo fields for
each grade level.


Although I was almost near completion of the project, I am still
willing to revise the table structure and accompanying forms if it will
mean a much better improvement in function of the db.

The way I see it is like this. I'll give an analogy.
A community of families has two options. Families can live in a group
of four low-rise buildings: Parents in one building, their cousins in a
second building, their second cousins in a third low-rise and more
relatives in a fourth low-rise.

Or all family members can live on different floors in one super
high-rise building.

Same family just located in different places.

John V, not clones or duplicate family members, or redundancy of any
kind. Just different locations.

I'm very interested to hear feedback now that I've give more detail.

TIA Ric

 

[mnature]

Before I give more details I must explain that this db is not a large

scale project nor is it intended to house important data that must be
queried and accessed regularly.
Data will be entered only twice a year by the students, in memo fields,
and basically just sit there. The data may be viewed by the principal,
but that's about it! Really.
It's meant to simply reduce paperwork/filing etc.

It always starts this way. Just a small insignificant database, that will
mostly sit and gather dust. So it doesn't need to be made absolutely right,
but just made well enough to get by. Better, though, to make it as if it
will be a large scale project. Then, if someone decides to add "just a
little more data" or wants a little more information, but sorted a different
way, you can do it easily. Database users are never completely satisfied,
so just plan for changes from the beginning. Makes you look good, then.

 

[google3luo359]

It always starts this way. Just a small insignificant database, that will
mostly sit and gather dust. So it doesn't need to be made absolutely right,
but just made well enough to get by. Better, though, to make it as if it
will be a large scale project. Then, if someone decides to add "just a
little more data" or wants a little more information, but sorted a different
way, you can do it easily. Database users are never completely satisfied,
so just plan for changes from the beginning. Makes you look good, then.

Thanks mnature. I understand what you're saying.

That "someone who wants a little more information" is me.
I was wrapping up the db and thought of a query that would be helpful
for admin. I had no problem coming up with the query, but I got stuck
when it came time to house the data in an existing form.
In this case, had the data all been in one table, it would have been
easier to come up with a form to display it.

I'd still like to hear back from the others on their opinion now,
before I make any big changes.

Ric

 

[mnature]

It always starts this way. Just a small insignificant database, that will

Thanks mnature. I understand what you're saying.

That "someone who wants a little more information" is me.
I was wrapping up the db and thought of a query that would be helpful
for admin. I had no problem coming up with the query, but I got stuck
when it came time to house the data in an existing form.
In this case, had the data all been in one table, it would have been
easier to come up with a form to display it.

I'd still like to hear back from the others on their opinion now,
before I make any big changes.

Ric

I vote to consolidate the four tables into one. You can always split out
information by using queries. You could even create temporary tables to
emulate the four that you are now using, and recreate them at the start of
the school year, if they are useful. However, a well-done query could do
that for a form or report, for that matter. Keep it well-normalized and
flexible, and then the database will do the work, rather than you.

 

[google3luo359]

I vote to consolidate the four tables into one. You can always split out
information by using queries. You could even create temporary tables to
emulate the four that you are now using, and recreate them at the start of
the school year, if they are useful. However, a well-done query could do
that for a form or report, for that matter. Keep it well-normalized and
flexible, and then the database will do the work, rather than you.

Thanks mnature.
Let's assume I go with your vote.
The consolidated AEP table would have the following fields:

StudGrade
StudNum
Goals
ActionPlan
Review
LastEdit

Obviously to get my One-to-many relationship my StudNum would be
Indexed (Dups OK)

What would my primary key be though?
It couldn't be StudGrade because I'll have many many dups with it.

Would it have to be an idAEP (autonumber)?

TIA Ric

 

[mnature]

AEPID (PK)

StudNum (FK)
Goals
ActionPlan
Review
EntryDate

I would like to point out something that might make you redo some of your
thinking. Instead of assigning the student's grade (such as 9th, 10,
whatever), why not simply have their graduation year? Then you don't have to
update it every year (unless they flunk, which won't be very many students.
Right?). Just use a formula in a query to subtract current date from
graduation date, and that would tell you what grade they are in. Then your
AEP table (use an AEPID for the primary key, autonumbered) could just use a
new entry whenever something changes for a student. The date of the entry
could be used to identify what grade level the student was at when the entry
was made. You would then have a number of AEP entries to document the goals
and action plans throughout the students years at school.

Just a thought.

 

[google3luo359]

I would like to point out something that might make you redo some of your
thinking. Instead of assigning the student's grade (such as 9th, 10,
whatever), why not simply have their graduation year? Then you don't have to
update it every year (unless they flunk, which won't be very many students.
......... Just a thought.

And a very good thought it is too.
Despite the fact that the students at my school more often than not
attend for more than the expected 4 years, the graduating year still
might work.
It will all depend on whether the school keeps this data. I'll find out
tomorrow.

If they don't I'd have to use a formula to get the grad. year from
their grade, and that would kind of defeat the purpose, as each year
I'd have to apply this formula to the new students list.

The reason the grad. year would still work in our school (if I can get
this data) is that each year a student's grade level is increased,
whether or not they pass all their courses. When they reach grade 12,
they are on a holding pattern until they complete all their required
courses.

But I see your reasoning. It would eliminate the need for a grade field
altogether.
Mind you that field would be replaced in the Students table with a
Grad. field.
But at least it wouldn't have to be updated each year, as I now have a
provision for.

Thanks again! Ric

 

[google3luo359]

I would like to point out something that might make you redo some of your

thinking. Instead of assigning the student's grade (such as 9th, 10,
whatever), why not simply have their graduation year? Then you don't have to
update it every year (unless they flunk, which won't be very many students.
Right?). Just use a formula in a query to subtract current date from
graduation date, and that would tell you what grade they are in.

Hi Mnature,

OK now I have more food for thought and would value your opinion here.
I checked at the office today, and though we don't have a 'graduation
year' date we do have an 'Entry to Secondary' date, which would
accomplish the same thing you were mentioning.

I checked how the dates match up with student grades to see how well
they jive.
While not perfect, (out of approx. 600 students, a handful enrolled in
the middle of the year and that screwed up their grade.) it certainly
was workable. One student enrolled 07-Feb-05 and was in Gd. 10 while
another enrolled in 03-Feb-04 and was also in Gd. 10.

So the question is, is it worth it to make the change? What exactly
would I gain/lose?

For the most-part the grade is used to select which Form tab to open
when a student is entering their Annual Plan. Now the grade is passed
as the student logs in. They log in with their StudNum and their grade
is part of the combo box record.

With an 'Entry to Secondary' date, I could have a calculation done each
time a student logged in or use a look-up table that had the values
there already:
2005 - Gd.9
2004 - Gd.10
etc.

But what would I really gain in the end? I would eliminate the Grade
field that I now have in my Students table. But that would be replaced
with another field (a bigger one at that), a date field. My grade field
is 2 characters.

I only see one real difference. My update query would not have to be
run each year in September. But that's no big deal at all. I have to
run a bunch of code to start each year anyways. It would just mean
cutting out one extra query from the other jobs.

BTW, I made the changes that you recommended wrt my AEP tables. They've
been eliminated and I now have just one AEP table.
I thought it would take a huge amount of time and effort to make all
the changes, but I was able to do it all last night.

TIA Ric

 

[google3luo359]

mnature wrote:

Ooopps!

Looks like I spoke way too soon.

Let's Back............up.
Up to the revised AEP table.
AEP09, AEP10, AEP11, AEP12 have been replaced with AEP.

Before I did the revision, I could log in (as a student) be taken to
the correct tab on the form, and enter data for the current grade. I
could change the grade of the student (pretending it was the previous,
or next year) and be taken the the correct tab for that year. I could
thus enter the data, and the data would always be there (on the correct
tab) to refer to when logging in each time.

Now, after the revision, with one AEP table I have no field in the AEP
table to identify what grade the comments belong to! Sure I have a last
edit field ( I had that before too) but will that really be able to
positively identify which grade/tab the comments belong on?

Surely I don't have to include the grade level field from the students
table in the AEP table? That would be including a redundant field.

I'm puzzled at the moment. Cause this thing ain't working right now.

TIA Ric

 

[mnature]

Before I did the revision, I could log in (as a student) be taken to

the correct tab on the form, and enter data for the current grade. I
could change the grade of the student (pretending it was the previous,
or next year) and be taken the the correct tab for that year. I could
thus enter the data, and the data would always be there (on the correct
tab) to refer to when logging in each time.

Now, after the revision, with one AEP table I have no field in the AEP
table to identify what grade the comments belong to! Sure I have a last
edit field ( I had that before too) but will that really be able to
positively identify which grade/tab the comments belong on?

Surely I don't have to include the grade level field from the students
table in the AEP table? That would be including a redundant field.

Let me review what I think is basically in your tables:

tbl_Students
StudentID
GradDate

tbl_AEP
AEPID
StudentID
LastEditDate

Set up a variable, which calculates something called RecordGrade: int(12 -
(GradDate - LastEditDate)). This assumes that the LastEditDate will not
change beyond the school year that the record is for. This will be in a
query to bring up the student records, and you just choose the max
RecordGrade in that query, which should bring up the highest grade that is
available for that student. If the highest one is from the previous year (no
new entries have been made for their newest school year), then have a button
that allows them to create a new record. Then, the next time the query is
run, it will bring up that record. You could even tie the query into the
current date, with some formula for whether a new year has started, that
would automatically bring up a blank record for them to fill out.

Always make the database do as much of the work as possible. Once you have
set up a graduation date, that can be used as a benchmark against other dates
to sort, filter or create information.