This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL problem" Watch "SQL problem" New topic
Author

SQL problem

colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
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.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
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
Amarnath Ramasamy
Greenhorn

Joined: Feb 05, 2010
Posts: 7
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]
colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
Thanks for that, I am using oracle, I'm quite new to sql so this is all a bit unknown to me.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

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."
colin shuker
Ranch Hand

Joined: Apr 11, 2005
Posts: 744
Thanks, thats quite a good idea, I'll see if it works.
Ryann Ong
Greenhorn

Joined: Jan 24, 2010
Posts: 19

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
 
Consider Paul's rocket mass heater.
 
subject: SQL problem