What's the 'correct' approach to this design issue? 1

[cascot]

A database with three tables - Student, Rating, RatingHistory.

When the user first adds a new student, the student will be allocated a Rating, even if they have no previous experience.

Subsequent to that, maintenance of the Student's rating will be via the RatingHistory table (StudentId, RatingDate, and RatingId). So that in future a students progress up the ratings ladder can be viewed/analysed.

Should there be a RatingId on the Student record, which gets set when the Student is first added (as well as adding a record to the RatingHistory table), and then routines to update Student.RatingId whenever records are added/amended/deleted in the RatingHistory table?

OR

Should there be NO RatingId on the Student record, ONLY records in the RatingHistory table?

I know the second option is the most 'normalised' database, but what about from a programming perspective, and convenience of data extraction, etc?

Thanks for taking the time to read my post.

 

[JerryDennison]

I think you'll find analysis much easier if you do not split the ratings (i.e. have one value in one table and all others in another table). You may wish to add another field to the rating history table that will let you identify the reason for the rating (i.e. initial rating, etc.)

 

[cmmrfrds]

Products like Access are designrd to work with normalized relational databases. If tables are primarily for reporting like in a data warehouse then you may not want to normalize the data, but you will have an easier time of maintenance and using query tools if you normalize the data.

The studentID should be a foreign key in the ratinghistory table. The ratingID should not be in the student table.

 

[cascot]

Hi JerryDennison,

There was never an intention to split the ratings in the way you mention, merely to either have ALL ratings ONLY in the RatingHistory table, OR have ALL Ratings in the RatingHistory table PLUS a RatingId on the Student table, which would reflect the current Rating.

 

[cascot]

Hi cmmrfrds,

Thanks for your reply. You're right about the correct approach from a normalised database point of view. The problem I have with that approach is obtaining the data. For example if I want to show the Current rating on the Student maintenance form (it would be a disabled field, unless the student was new, and thus being added to the system). For that I need all of the basic student information PLUS the Rating against the MAX RatingDate. To get near to this I use the Totals option on my query, selecting 'Group By' for most entries, except for Max against RatingDate. But how do I get the Rating for that same RatingDate? It's true that in 95% of cases the MAX would also get the correct Rating, unless the student has dropped in Rating.

 

[JerryDennison]

That is what I meant by splitting, having the same field in two different tables.

 

[JJOHNS]

I got lost in reading all of this, so if someone already said this I apologize. Anyway, Student to RatingHistory is one to many. The foreign key has to go on the many side. That means you do not want a RatingID key in the Student table. Instead, put a StudentID key in the RatingHistory table. Personally, I can't see a reason to have the third table at all, but if you do, the StudentID needs to go there as well.

Hope I'm helping.

 

[cmmrfrds]

A couple of thoughts

The data can be extracted differently depending on how you need to show it on a report. If you need to show all students and their last rate and rate amount, then you may want to do it in one query. If you need to show one student at a time, then you could have a main form for student and sub form for rating, thereby, splitting into 2 queries. Instead of using max(date) which requires a group by to display the data, consider using timestamp as a field in your table. Timestamp is maintained by the Database and is chronological. For example, you could say Select top 1 .... from table .. Joins .. order by YourTimestamp desc. This could avoid the use of the group by clause for certain queries.

 

[cascot]

Hi JerryDennison,

Thanks for your reply.

Sorry about the slight misunderstanding. I was thrown by your line "i.e. have one value in one table and all others in another table".

 

[cascot]

Hi JJOHNS,

Thanks for your reply.

You said "Personally, I can't see a reason to have the third table at all".

I only mentioned three tables, though the entire system has quite a few more. A student rating is one from a set of pre-defined ratings, hence the need for the Rating table.

 

[cascot]

Hi cmmrfrds,

The use of a TimeStamp or AutoNumber field would be fine, but let's say a student joins and their current rating is 'Rating5'. Once they are entered, time would be taken at some later date to compile their rating history, i.e. on which dates in the past they achieved 'Rating1', 'Rating2', 'Rating3', etc. In such circumstances use of the latest timestamp or highest AutoNumber would give an incorrect Rating. The current Rating has to be the rating against the record with the most recent ratingDate for the given student.

Any thoughts?

 

[cmmrfrds]

To avoid the group by you can still use something like.

Select top 1 ...... Join student to ratinghistory and ratetype tables where studentID = avariable
order by ratingdate desc

 

[randusoleis]

Here is my two cents......
You have a Students table and a Ratings table. Each table should have it's own primary key.
The ratings table should be a table that only has static values...your ratings. The student table will have all information on students only.
The third table...RATINGHISTORY....will have a connection to each of the other 2 tables. RatingHistory will have 2 primary keys (1 key to match the primary key in each of the other table). Create a one-2-many relation ship between the Students table and the RatingHistory table and create a one-2-many relationship between the Ratings and RatingHistory table.
You have now created a MANY-2-MANY relationship between the Students table and the Ratings table with all the corelating information stored in the RatingHistory table. You can now report rating history for each user for as long as you like.
By the way. In your junction table (RatingHistory), make sure you allow duplicates on each key.

You can also add any other additional columns to the RatingHistory table like dates and stuff to keep track of when things changed. Randusoleis.....

 

[cascot]

Hi cmmrfrds,

Thanks for a very useful tip, re: avoiding the use of Group By. That is fine when I only want data for a single student, but what about when I want to see all students (or a sub-set which includes 2+ students), along with their current rank?

Any ideas on the most efficient way to approach this?

 

[cascot]

Hi randusoleis,

Thanks for the reply.

As you say, that is definitely the correct design from a relational database point of view, it's getting the data in certain situations that made me consider the possibility of other options.

 

[cmmrfrds]

Look at using subqueries. You might need to add aliases to the table names. Here is the basic idea.

Select field1, field2,
(select max(yourdate) from table1 A where A.ID = B.ID
group by ID) as Adate,
field5, field6
From table1 B where yourID = Avariable

You can use subqueries in the From clause also.

Select field1, field2, field3
From table1 A
where yourdate = (Select max(yourdate) from table1 B where
A.ID = B.ID group by ID) and yourID = Avariable