What is the advantage?

[Chaplain Doug]

I made the quantum leap from "spreadsheet" to relational
database design. One of the advantages I was encouraged
with was the ability to modify stuff without having to add
fields (like you must do when you spreadsheet). I now
have a student table linked to a grades table. The grades
table is linked to the subject table. Anyhow, I can add
subjects without a problem. However, if I wish to delete
subjects I cannot as there are related records in the
grades table. I know that this is simply a fact I must
deal with. However, how may I design my tables so that I
CAN delete subjects as well as add subjects? Thanks for
your help.

 

[Kevin3NF]

You are running into the entire point of referential integrity. If you
delete the subject and it is tied to a grade, you now have incomplete
information on the grades given(i.e, what subject was it given for). I am
assumin grade means A, B,etc.

To do what you are asking about, you would need to delete all records in the
grades table that are realted to the subject, then delete the subject. This
may or may not be the best course of action depending on your requirements
and environment.
--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[Chaplain Doug]

I guess I can't have it both ways. Thanks Kevin. God
bless.

 

[Rick B]

I believe you could also set up cascade deletes. Then wehn you delete
records in the subject table, all related records in the grade table would
be deleted.

Use with caution!!!



You are running into the entire point of referential integrity. If you
delete the subject and it is tied to a grade, you now have incomplete
information on the grades given(i.e, what subject was it given for). I am
assumin grade means A, B,etc.

To do what you are asking about, you would need to delete all records in the
grades table that are realted to the subject, then delete the subject. This
may or may not be the best course of action depending on your requirements
and environment.
--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[Jeff Boyce]

Doug

I'm with Kevin -- relational means relational. But I'm not sure to what you
refer when you mention

I guess I can't have it both ways.

It is possible to design an Access database that allows you to add and
delete rows from tables -- that's actually what Access is all about! But
you are prevented from orphaning a child table row that looks to a parent
table for a related ID. Well, actually, you should NOT delete a parent if
it has children, unless your intent is to delete the children too.

That's what the Referential Integrity settings are about in the
relationships window.

 

[Tim Ferguson]

However, if I wish to delete
subjects I cannot as there are related records in the
grades table.

If you step back a moment, you will see that your really don't want to
delete Subject information while it is still current. Look at the Grades
table -- this says that Stuart completed subject 0023 -- so what subject is
that? You won't know if you have deleted the Subject record!

What I guess you are after is that you want to mark certain Subjects as
Unavailable or Historic or Defunct or something, so that they no longer
appear in lists of Subjects That Can Be Taken Now. So the way to do this is
to add exactly that attribute to the Subjects table (i.e. another column)
and then just use that to filter the records for combo boxes and so on.

That is what relational means!

Hope it helps


Tim F