Database design question

[gil]

I use Access 97 to keep track of students and bus routes in a district,
which includes all levels of schools.

The optimized routing system we use, means that a student probably will
not travel home on the same bus that picked him up in the AM, and may
even have to transfer to a second bus to complete the journey. Only one
transfer would ever be required. Also, the bus may carry students from
more than one school at a time.
To make things more complicated, many students are from splintered
families, and will need to travel to differing locations, on different
bus routes, based on which parent/guardian has custody/visitation rights
this week.

I wish to keep track of each student's personal information,
as well as, which bus picks them up in the AM and the PM,
and information pertaining to the location and time of each stop.
I will need to report information by student, by bus, by school, or by
stop (area).

Present tables are
STUDENT - indexID, name, address, phone, school, grade, etc with
indexID's of other tables
BUS - indexID, bus number, drivername, phone, etc.
STOPS - indexID, location of stop, area, time, etc
and the database works if each student only takes a single bus AM or PM.

How should I redesign the database to allow for the multiple bus
options? As the db is today, I have to enter search criteria in both AM
and PM fields to find all students on a particular bus, and have no way
to easily keep track of bus transfers, or alternate addresses.

Sorry for the length. Thanks for your input.

--
Gil.

If you get the facts first,
You may not need to reconstruct your hard drive later!

Please reply to the group.

-----

 

[GVaught]

Probably the easiest way is to have a Student table listing only student
info with its own Primary Key id, a Bus table with only bus info and its own
primary key Id, a Stops table with only stop info with its own primary key.
Then create another table that will have its own primary key and the three
keys from the other three tables. this is how you can list a students route
info and which bus was taken. Add to this table the dates traveled and any
other info you wish to track. If the data already exists in one of the other
tables, do not add it to this table. Only add fields that do not exist or
can't exist in one of the other tables.

Read up on normalization. Info can be found extensively through the
Internet.

 

[Larry Daugherty]

Hi Gil,

I saw your post yesterday and was hanging back hoping to see some cogent
suggestions from some of the more accomplished players. However, since they
didn't ...

Does it seem to you that you have a pretty complex problem to solve,?
You're correct. The tables you've suggested really won't do a good job of
capturing all of the relevant data and therefore there will always need to
be a very knowledgeable user (probably you) running the application to make
up for what it lacks.

Simplistically stated, the creation of these applications involves
Problem/Goal statement, Analysis, Design and Implementation. Goal Setting
starts it all. That should be a complete statement of the problem that must
be solved. Of the remaining three elements, Analysis is by far the most
important. Your prose analysis seems pretty good but the tables don't
reflect the complexity you suggested.
Every relational application, no matter how complex, is made up of simple
elements. In order to keep them simple, there may be lots of them (tables).
One of the things that seems to need further consideration is ADDRESS. I
believe you need a separate table for addresses. Given the splintered
family paradigm, you have to identify all of the legal destinations for a
child and the name and relationship of the adult(s) there - so you probably
need a table for Adults too. (Actually, Address could be contained in
Adults - assumes the child doesn't have an address independent of an adult).
You need to identify the adult(s) with authority and a fallback pecking
order for failed communications.

It also seems that you are required to deal with lots of dynamic situations.
Each of those situations requires definition of the gating factors: If a
child is transferring from one bus to another, how is the hand-off made to
assure that a child isn't abandoned? Somehow you already know how to deal
with exceptions.

You should probably have a lookup table for School. I'd imagine that Route
Name is the main route identifier with the Number of the bus normally
running that Route and the name of the Driver normally assigned to that
route. Do you take note of driver or equipment substitutions day by day? I
can see where it might be valuable to know that a rookie is driving a
connecting route where kids have to transfer. It might be reassuring to
know the visible number of substituted equipment in case of concerned
parents or police.

Do you ever have half-days? How do you deal with them in terms of bus
routing and child delivery? Do you folks have snow days? Other types of
weather related shut-downs. Ever have delayed pick-up? Civil defense
emergency? Any of those that you might have you have to deal with you
must/should address in your application. Is every school in lock-step with
every other school or do they maintain semi-autonomous schedules?

Given the highly fragmented nature of the child/parent relationships and the
highly changeable nature over all, you may also need to track holidays and
special days and have various events fired or tracked by weekday.

Access 97 will serve to get the job done.

HTH. Post back if you have more questions.

p.s. my daughter is a school teacher, formerly in California and now in
Massachusetts so things that impinge on her world get my attention.

 

[gil]

Thanks Larry and GV for your tips. The existing db is actually more
complicated than I sketched out. There are more linked tables.

If I read GV's posting correctly, he suggests having a new 'INDEX'
table. All other tables would be related to it, and forms would be
redone with this new table as the 'parent'. This method appears to offer
some solutions to my problems. Larry's comments about a separation of
children and their parents make a lot of sense, and I will try the
changes suggested.

Also this db is limited in scope to student travel, with just enough
additional information about busses, and drivers to make the db
workable. It does duplicate some data in other databases to which my
department does not have access.

In answer to your other questions,
most schools have a separate start and end time but they are in lock
step with each other. Delayed openings and early closings don't make any
difference as the entire district will be affected by the same differential.
Transfers require that one bus actually meet the other bus at a stop, or
that students transfer at a schoolyard, exceptions are extremely rare.
BTW, the bus# of the regular bus is used as the route# and as the
drivers# (not the index numbers though), the route number will only
change when a different bus is assigned permanently to that route.

I can see the reasoning behind tracking spare busses (of differing
sizes), and spare drivers, but this information is only required by a
single office and is easily put on a board; but, I will try to
incorporate them in a future update, if the PTB (Powers That Be) allow it.

At approximately 2004-06-12 04:35, Larry Daugherty typed these characters:

Hi Gil,

I saw your post yesterday and was hanging back hoping to see some cogent
suggestions from some of the more accomplished players. However, since they
didn't ...

Does it seem to you that you have a pretty complex problem to solve,?
You're correct. The tables you've suggested really won't do a good job of
capturing all of the relevant data and therefore there will always need to
be a very knowledgeable user (probably you) running the application to make
up for what it lacks.

Simplistically stated, the creation of these applications involves
Problem/Goal statement, Analysis, Design and Implementation. Goal Setting
starts it all. That should be a complete statement of the problem that must
be solved. Of the remaining three elements, Analysis is by far the most
important. Your prose analysis seems pretty good but the tables don't
reflect the complexity you suggested.
Every relational application, no matter how complex, is made up of simple
elements. In order to keep them simple, there may be lots of them (tables).
One of the things that seems to need further consideration is ADDRESS. I
believe you need a separate table for addresses. Given the splintered
family paradigm, you have to identify all of the legal destinations for a
child and the name and relationship of the adult(s) there - so you probably
need a table for Adults too. (Actually, Address could be contained in
Adults - assumes the child doesn't have an address independent of an adult).
You need to identify the adult(s) with authority and a fallback pecking
order for failed communications.

It also seems that you are required to deal with lots of dynamic situations.
Each of those situations requires definition of the gating factors: If a
child is transferring from one bus to another, how is the hand-off made to
assure that a child isn't abandoned? Somehow you already know how to deal
with exceptions.

You should probably have a lookup table for School. I'd imagine that Route
Name is the main route identifier with the Number of the bus normally
running that Route and the name of the Driver normally assigned to that
route. Do you take note of driver or equipment substitutions day by day? I
can see where it might be valuable to know that a rookie is driving a
connecting route where kids have to transfer. It might be reassuring to
know the visible number of substituted equipment in case of concerned
parents or police.

Do you ever have half-days? How do you deal with them in terms of bus
routing and child delivery? Do you folks have snow days? Other types of
weather related shut-downs. Ever have delayed pick-up? Civil defense
emergency? Any of those that you might have you have to deal with you
must/should address in your application. Is every school in lock-step with
every other school or do they maintain semi-autonomous schedules?

Given the highly fragmented nature of the child/parent relationships and the
highly changeable nature over all, you may also need to track holidays and
special days and have various events fired or tracked by weekday.

Access 97 will serve to get the job done.

HTH. Post back if you have more questions.

p.s. my daughter is a school teacher, formerly in California and now in
Massachusetts so things that impinge on her world get my attention.

--
Gil.

If you get the facts first,
You may not need to reconstruct your hard drive later!

Please reply to the group.

-----