db design

[google3luo359]

I have a db that's working nicely so far. Still under construction.
It's quite normal, or normalized but I could use some input on how to
best set up a table or tables.

It must be pointed out that this db will be used just two times each
year by the students (approx. 700), so use will be very light. There
will be approx. 50 or so students accessing it at the same time.

Currently there are five main tables.
'Students' holds just StudentNum, LastName, FirstName, Grade and
Password.
The other four tables hold StudentNum and memo fields for student
plans.

I am now adding a 'Login Date' to the db.
The easiest way for me to do it would be to simply add it to the
Students table.

However it got me thinking. In theory, the Students table should be
used to store static data while the other tables would be storing
dynamic data.

The LoginDate field, wherever it will be situated, will be updated each
time a student logs in. The question is whether to put it in the
Students table or in its own table (Login table).

If I were to create a Login table it would house StudentNum, Logindate
and possibly Password as well. (Once I was creating the table I might
as well throw the password field in there as well.)

I know this isn't a big deal, and it would work either way. But what is
the recommended way to go, if there is one?

TIA Ric

 

[Guest]

I would think a separate table as there could be more than 1 record per
student. This way you are not duplicating student info, but you are
connecting the 2 with the StudentNum field. Also, I don't think you need the
password duplicated just keep it in the Student table. Why keep redundent
info.

 

[google3luo359]

I would think a separate table as there could be more than 1 record per
student. This way you are not duplicating student info, but you are
connecting the 2 with the StudentNum field. Also, I don't think you need the
password duplicated just keep it in the Student table. Why keep redundent
info.
--

Hi James,

Thanks for your reply!
There would only ever be one LoginDate per student.
Each time the student would login, an update query would run and update
the login field. No new records for the student would be created.

As for the password, sorry I didn't make myself clear. I meant to say
that if I were to create the Login table, then I would probably take
out the Password field from the Students table and put one in the Login
table.

Ric

 

[Guest]

I still think that separate tables works better, that way if you do decide
down the road to keep multiple records, you are already set up for it.