Databae Designing

[drrajnishpatel via AccessMonster.com]

Respected Experts,
I am new to designing Access Databases... i am helping my school to create a
database for the students taking Voluntary MCQ exams, the exams are times 3 ,
at a time on month ends, the registration is done at start of the Quater ,
the Student ID for the exams is defined as Exam ID = YYYY- A,B,C or D, as per
Quater, all this is done and fine. The problem is the entry of data and
subsequent Student ID specific for the Exam. eg, 2007-d Exam will have 203
attendents therefore i will need seprate data colleted for 2007 -D exams, now
at the same time some student will want to registr for the 2008 - A exam, so
now the trouble starts, the MCQ Student ID created by Autonumbering
generates one id like 2008-A 5, directly because it has enteries for
autonumbering from 1 to 4 registered for 2007-D, the next student for 2007-d
will have 2007-D 6 and so on. PLease guide me how to set this . I want all
data for each set of exam to go into seprate Table automatically and thereby
creating specific Students ID for tht MCQ test only

Table name is "MCQ Test Registration"

following are columns created in the table

Event ID-RegistrationID-Student ID-First Name-Last Name-Address-Phone Number-
StudentExamDate--
2007-A 001
2008-A 003
2007-A 004
2007-A 005


I think i have clearly expressed my problem,

Thanks
rajnish patel

 

[Amy Blankenship]

Respected Experts,
I am new to designing Access Databases... i am helping my school to create
a
database for the students taking Voluntary MCQ exams, the exams are times
3 ,
at a time on month ends, the registration is done at start of the Quater ,
the Student ID for the exams is defined as Exam ID = YYYY- A,B,C or D, as
per
Quater, all this is done and fine. The problem is the entry of data and
subsequent Student ID specific for the Exam. eg, 2007-d Exam will have 203
attendents therefore i will need seprate data colleted for 2007 -D exams,
now
at the same time some student will want to registr for the 2008 - A exam,
so
now the trouble starts, the MCQ Student ID created by Autonumbering
generates one id like 2008-A 5, directly because it has enteries for
autonumbering from 1 to 4 registered for 2007-D, the next student for
2007-d
will have 2007-D 6 and so on. PLease guide me how to set this . I want all
data for each set of exam to go into seprate Table automatically and
thereby
creating specific Students ID for tht MCQ test only

Table name is "MCQ Test Registration"

following are columns created in the table

Event ID-RegistrationID-Student ID-First Name-Last Name-Address-Phone
Number-
StudentExamDate--
2007-A 001
2008-A 003
2007-A 004
2007-A 005

First, I think you'll find many things become easier in Access if you don't
use spaces in field and table names. Second, you actually need three
tables, not two. This is because a student is not the exam he took/is
scheduled to take. If you consider for a moment, you will realize that the
same student could take multiple exams, and the same exam can be taken by
multiple students. Therefore, trying to put the information on who the
students _are_ in the same table with the table that schedules their exams
is not going to work. The student is still the same person even if he
retakes the exam, and does not need a new record defining who he is.

Let's first put down on paper your Exams table:

ExamAutoID -PK Autonumber
ExamViewID - This is what you called ExamID above.

Note that we're now using an autonumber PK. This is because (in part) this
one number is shorter than your full examID, so it takes less space to
repeat over and over in other tables.

Now, to your Students
StudentID -PK Autonumber
FirstName
LastName
....
EnrollStart
EnrollEnd

Scheduling
StudentExamID - PK Autonumber
ExamAutoID
StudentID
ExamDate

If there are specified dates for each exam rather than being relatively
random by student, I'd structure it a bit differently, but that's a start.

HTH;
Amy

 

[drrajnishpatel via AccessMonster.com]

Amy,
Thanks for your reply....

I will now put your suggestions into effect , but even before i see the reply
i wanted to thank you ,
thank you
rajnish patel

Respected Experts,
I am new to designing Access Databases... i am helping my school to create

[quoted text clipped - 30 lines]

2007-A 004
2007-A 005

First, I think you'll find many things become easier in Access if you don't
use spaces in field and table names. Second, you actually need three
tables, not two. This is because a student is not the exam he took/is
scheduled to take. If you consider for a moment, you will realize that the
[quoted text clipped ]

 

[Jamie Collins]

a student is not the exam he took/is
scheduled to take... Therefore, trying to put the information on who the
students _are_ in the same table with the table that schedules their exams
is not going to work.

While I agree with your design proposal of using a relationship table
(a.k.a. junction table), I don't agree your line of questioning <g>. I
think that to a certain extent the classes a student takes *does*
define the student. Can one, for example, be considered a student of
computing science if one is not currently enrolled as a student on a
computing science course? Obviously you want to avoid various update
anomalies, including the ability to model a student as being eligible
to take an exam before the exam itself has been scheduled.

Now, to your Students
StudentID -PK Autonumber
FirstName
LastName
...

IMO (and many regulars agree) autonumbers should not be exposed (if
used at all) to end users, therefore I think your proposal to use an
autonumber for StudentID is inappropriate here. As you've said before,
whether an autonumber should be used as a PK is academic <g>.

Jamie.

--

 

[Amy Blankenship]

While I agree with your design proposal of using a relationship table
(a.k.a. junction table), I don't agree your line of questioning <g>. I
think that to a certain extent the classes a student takes *does*
define the student. Can one, for example, be considered a student of
computing science if one is not currently enrolled as a student on a
computing science course? Obviously you want to avoid various update
anomalies, including the ability to model a student as being eligible
to take an exam before the exam itself has been scheduled.


IMO (and many regulars agree) autonumbers should not be exposed (if
used at all) to end users, therefore I think your proposal to use an
autonumber for StudentID is inappropriate here. As you've said before,
whether an autonumber should be used as a PK is academic <g>.

I didn't suggest it be exposed. If you wanted another ID for displaying you
could have something like DisplayStudentID just like is in the Exams table.
I'm presuming this doesn't need to be terribly robust, so potentially a
combobox showing first and last name filtered by students who are currently
enrolled would serve without exposing the ID in any form.

 

[Jamie Collins]

I didn't suggest it be exposed. If you wanted another ID for displaying you
could have something like DisplayStudentID just like is in the Exams table.
I'm presuming this doesn't need to be terribly robust, so potentially a
combobox showing first and last name filtered by students who are currently
enrolled would serve without exposing the ID in any form.

If this was you intention, why didn't you suggest the PK or a UNIQUE
constraint over the compound (LastName, FirstName)?

I didn't suggest it be exposed. If you wanted another ID for displaying you
could have something like DisplayStudentID just like is in the Exams table.
I'm presuming this doesn't need to be terribly robust, so potentially a
combobox showing first and last name filtered by students who are currently
enrolled would serve without exposing the ID in any form.

If this was you intention, why didn't you suggest the PK (or a UNIQUE
constraint) be over (LastName, FirstName)? My advice would be to defer
the issue of person identifiers to the OP on grounds of complexity
e.g. is this an English state school required by statute to retain not
only a current Unique Pupil Number (possibly required to generate a
temporary one) but also multiple former UPNs that might be duplicated
by a pupil who has attended state school in Scotland? Unless you have
some knowledge of the domain, you risk IMO over simplifying and giving
the OP a false sense of security -- an autonumber cannot verify a
person in reality, check their exam certificates, residency status,
etc.

I've just noticed that you've proposed adding attributes EnrollStart
and EnrollEnd dates in the OP's Student table. Do you realize this
makes the table constraints much more complex? e.g. requires a
sequenced primary key, allowing a student to enrol twice (or more) in
disparate periods while preventing a student being enrolled twice (or
more) in the same period? Put another way: if you consider for a
moment, you will realize that the same person could be enrolled as a
student multiple times. Therefore, trying to put the information on
who the people _are_ in the same table with the table that models
their periods of enrolment is not going to work. The person is still
the same person even if he re-enrols, and does not need a new record
defining who he is.

I think people (students) and their periods of enrolment should be in
separate tables, however I suspect things are not as simple as start
date and end date e.g. if I leave my course part way through the
academic year but I'm eligible to retake the course next year, am I
still a student i.e. does my enrolment end? Again, I think caution is
required rather than prescribing solutions for the OP -- if I've
understood correctly the OP says they've already completed design in
this area.

Jamie.

--

 

[Amy Blankenship]

If this was you intention, why didn't you suggest the PK or a UNIQUE
constraint over the compound (LastName, FirstName)?



If this was you intention, why didn't you suggest the PK (or a UNIQUE
constraint) be over (LastName, FirstName)? My advice would be to defer
the issue of person identifiers to the OP on grounds of complexity
e.g. is this an English state school required by statute to retain not
only a current Unique Pupil Number (possibly required to generate a
temporary one) but also multiple former UPNs that might be duplicated
by a pupil who has attended state school in Scotland? Unless you have
some knowledge of the domain, you risk IMO over simplifying and giving
the OP a false sense of security -- an autonumber cannot verify a
person in reality, check their exam certificates, residency status,
etc.

Because most people asking questions here aren't interested in that type of
information. They just want something that works. Additionally, I probably
_wouldn't_ constrain to that, since there is a possibility that the names
could repeat. The system would only work up to the point that the
names/enrollments _did_ repeat. After that, you'd need to be able to see
their other information for it to work. However, the poster didn't seem to
be wanting a bulletproof design, just one that would meet the current need.

I've just noticed that you've proposed adding attributes EnrollStart
and EnrollEnd dates in the OP's Student table. Do you realize this
makes the table constraints much more complex? e.g. requires a
sequenced primary key, allowing a student to enrol twice (or more) in
disparate periods while preventing a student being enrolled twice (or
more) in the same period? Put another way: if you consider for a
moment, you will realize that the same person could be enrolled as a
student multiple times. Therefore, trying to put the information on
who the people _are_ in the same table with the table that models
their periods of enrolment is not going to work. The person is still
the same person even if he re-enrols, and does not need a new record
defining who he is.

I don't know how it works where you are, but typically here you start at a
school at a given age and continue until you drop out, graduate, or move to
another school. In rare cases a student might move to another school and
then move back, but that is the exception rather than the rule. But, again,
I was giving the poster some general guidelines to point him in the right
direction rather than a final database design. If the poster felt that
multiple enrollments were a likely occurrence, he could come back and ask
for more help.

I think people (students) and their periods of enrolment should be in
separate tables, however I suspect things are not as simple as start
date and end date e.g. if I leave my course part way through the
academic year but I'm eligible to retake the course next year, am I
still a student i.e. does my enrolment end? Again, I think caution is
required rather than prescribing solutions for the OP -- if I've
understood correctly the OP says they've already completed design in
this area.

Ah, it seems you are thinking more of a college model and I was thinking
more of a primary school model. In the case of a college model, you would
indeed want to have a more complicated way of dealing with enrollment.
However, I would approach it completely differently in that case. There
would be a much bigger database defining all courses of study possible as
well as current offerings of those courses. Another table would define what
courses would make you eligible to take the exam, and there would be a table
of what students are enrolled in what offerings and their start and end
dates for those offerings. So, to be eligible to take the exam, a student
would need to meet the following requirements:

Be enrolled in at least one offering
The offering would have to be of a course that makes them eligible
The offering would need to be currently running
The student's end date in that offering would have to be null

Helps?
-Amy

 

[Jamie Collins]

most people asking questions here aren't interested in that type of
information.

I can't see that the OP expressed any interest in discussing PKs or
surrogates, yet *you* introduced these issues into this thread. The OP
mentioned a StudentID and in your proposal you turned this into an
autonumber and promoted it to PK.

The problem I have is that you IMO went off topic to discuss
constraints but didn't complete the job. I challenge you to finish
what you started and come up with other constraints (e.g. candidate
keys to supplement your surrogate) and suddenly you've determined that
the OP won't be interested. I urge you to consider the possibility
that you might be making too many assumptions.

They just want something that works.

I guess it's a question of ethics. If someone asks for a kludge do you
give them a kludge? Personally, I try to steer them towards 'best
practice'.

The problem for me here is that you (rightly) picked up the OP on a
design flaw but then committed a very similar (if not the same) one
yourself by modeling a student's periods of enrolment in the same
table that models their personal details. Note the OP said that this
part of their schema is working fine already.

In rare cases a student might move to another school and
then move back

I was thinking more of a primary school model.

Why are you so sure that such cases are rare? My eldest goes to a very
small village school and pupils from *two* families in her year have
left and come back; in both cases the parents migrated overseas then
returned due to unforeseen(?) circumstances. Previous enrolment is an
important factor when considering intake from the waiting list.

I was giving the poster some general guidelines to point him in the right
direction rather than a final database design. If the poster felt that
multiple enrollments were a likely occurrence, he could come back and ask
for more help.

If your design proposal is indeed based on the rule that a person
cannot enrol a second time then where are the constraints to prevent
it? Of all the things you've assumed about the OP's situation, don't
you think "effective constraints ensuring data integrity" should be
one of them? I do.

it seems you are thinking more of a college model

No, I've been thinking of a non-specific academic institution.

The [college] student's end date in that offering would have to be null

"Have to"? I'm not so sure.

My experience of colleges is that they operate in periods of semesters
(or terms) and academic years of known/fixed dates. I doubt a college
would permit a person to remain an undergraduate indefinitely.

As regards 'magic dates' for subatomic period attributes, it is common
practice to use a far future date to model an unspecified end date
('infinity').

Jamie.

--

 

[Amy Blankenship]

I can't see that the OP expressed any interest in discussing PKs or
surrogates, yet *you* introduced these issues into this thread. The OP
mentioned a StudentID and in your proposal you turned this into an
autonumber and promoted it to PK.

The problem I have is that you IMO went off topic to discuss
constraints but didn't complete the job. I challenge you to finish
what you started and come up with other constraints (e.g. candidate
keys to supplement your surrogate) and suddenly you've determined that
the OP won't be interested. I urge you to consider the possibility
that you might be making too many assumptions.


I guess it's a question of ethics. If someone asks for a kludge do you
give them a kludge? Personally, I try to steer them towards 'best
practice'.

If I think they're not ready for best practice I steer them to better
practice. If you give someone so much detail their eyes glaze over and they
don't use your solution, have you really helped them?

 

[Jamie Collins]

If I think they're not ready for best practice I steer them to better
practice.

Let's think the best of the OP and say they have a student ID
available to them, being a number issued by the academic institution's
admissions office (i.e. the trusted source for the identifier
responsible for verifying students in reality), and they've put a PK
on student ID in their Students table in implementation. You've
advised them to add an autonumber and make that the PK while offering
no advice about putting a UNIQUE constraint on their existing student
ID. How is that better practice? Sounds worse to me.

Let's think the worst of the OP and say they are not aware of a real
life identifier with a trusted source and have no PK or UNIQUE
constraint in their Student table in implementation. You've advised
them to add an autonumber and make that the PK and offered no advice
about putting a UNIQUE constraint any other columns. They'll still be
able to add duplicate student entities but each duplicate will have a
unique number. How is that better practice? Sounds like false sense of
security to me.

If you give someone so much detail their eyes glaze over and they
don't use your solution, have you really helped them?

Do you read the 'small print' before signing a document? Ever heard
the saying, "The devil is in the detail"?

http://www.usingenglish.com/reference/idioms/devil+is+in+the+detail.html
"When people say that the devil in the detail, they mean that small
things in plans and schemes that are often overlooked can cause
serious problems later on."

Certainly applies to table design, IMO ;-)

I believe that designing tables with effective integrity constraints
is hard work but worth the effort. I don't see an argument for
_effective_ integrity constraints.

Jamie.

--

 

[Jamie Collins]

I don't see an argument for
_effective_ integrity constraints.

Should read, "I don't see an argument for _ineffective_ integrity
constraints"

Jamie.

--