SQL problem

Hi,

I have a problem with designing a database, basically suppose there is a table STUDENTS
containg SURNAME, FORENAME, AGE.

And say there is a table ATTENDANCE
When a student logs in, the ATTENDANCE table inserts a row of data that includes the STUDENTs SURNAME.

Now suppose the student changes their SURNAME, by using UPDATE...

My problem is that in the ATTENDANCE table, it will still take the old value.
This could be overcome by then updating it in ATTENDANCE.

For bigger applications, this seems very impractical, is there an efficient way of doing this, so I don't have to
redo everything following a small change?

Thanks for any help.

colin shuker wrote:For bigger applications, this seems very impractical, is there an efficient way of doing this, so I don't have to
redo everything following a small change?

Yes. A better design would use soemthing as the key that doesn't change. Like student_id. If there isn't an id, you can still use a sequence that gives each student a unique number.

You should do this even if the surname doesn't change. What if you have two students in a class with the same name?

Thanks, I'm still confused though, I actually have a unique number associated with each student.

But the problem is when I change attributes of the student that are also present in different tables, I will also have to update those tables as well to reflect the first change I made.

Can you see a way round this given the example I started with?

Thanks again

Which database server are you using? In oracle, unlike cascade delete, there is no straight forward way for cascade update. You may have to write trigger to overcome this issue if you want to handle this in database level for cascade update.

Please follow this link.

[url=http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034]webpage[/url]

Thanks for that, I am using oracle, I'm quite new to sql so this is all a bit unknown to me.

Colin,
If you have a unique number for each student, you don't need the surname in both places. Keeping it in the student table and using the id in the attendance table means that data is only in one place. And on ly needs to be changed in one place.

For more information on this topic, search for "normalization."

Thanks, thats quite a good idea, I'll see if it works.

Hello, few suggestions here, hope this could help:

1. If you're using the attendance table to monitor student's attendance in a given time, i suggest that you just add another column inside your students table( for e.g. attendance) having a data type of boolean. By doing this, you can just set it to true or false without worrying updated fields when an update occurs.

2. On the other hand you can also use a foreign key inside the attendance table which must be referenced to the student table's primary id. This will allow you to get the fields from the child table which is the student table. Here's a good link that teaches database design tutorial hope this could help you as well: http://www.visualcase.com/tutorials/database-design-tutorial.htm

More power and good luck