What is the purpose for a relation in a dataset?
I'm working in SQL CE. In my design I've got:
开发者_JAVA技巧Student table:
- StudentID
- GroupID
- FirstName
- LastName
And Group Table:
- GroupID
- GroupName
I don't know the advantages to use Relation from toolbox. And appears me this window. How do I configure that relation? What are the advantages to use it?
The benefit of having this in a dataset stems back to RDBMS design strategies. When you supply a primary/foreign key relationship (as long as you have relational integrity specified) it creates the link between a parent and a child table. This ensures that you are inserting a record in a table that has a legitimate a pre-defined foreign key from the parent table with the primary key.
Defining a relationship forces your database to enforce referential integrity. Without this definition rows in child tables could be deleted rendering parent table data partially meaningless. For example:
You have Make and a Model tables with the following data:
Make
ID | MakeName
321 | Ford
322 | Toyota
Model
ID | ModelName | MakeId
1 | Focus | 321
2 | Tundra | 322
If you do not enforce referential integrity then it would be possible to delete the "Ford" row out of the Make
table. This would render the Focus
row in the Model
table (partially) meaningless since the MakeId
would no longer reference something that exists.
If you enforce the relationships then this will never happen.
Some people with limited database experience will say, "If I just never delete that record this will not even be an issue." This line of logic has a number of problems, for example:
- You have an off day and make a mistake. Everyone does it.
- Someone new starts working with the DB and they don't know about this implied relationship.
- You get fired and no one knows that this implied relationship even exists.
The bottom line is that humans can make mistakes and computers do not. Your life will be easier if you enforce it.
If setup properly, a relation can prevent orphan records where the parent is deleted leaving the child records behind.
It can be setup to do cascading deletes or raise an exception when an attempt is made to delete a parent record with child records.
The relations in strong typed DataSets are really useful. Let's say you got the Group
object binded by the GroupID
to your student, if you want to know if student bob
is in Group 5
, per se, you can simply do :
if (bob.GroupRow.GroupName == "Group 4")
{ //... }
Also, when you're creating a new student, let's say you associate a group with GroupID
:
Classroom cr = new Classroom();
Classroom.StudentRow student = cr.Student.NewStudentRow();
student.FirstName = "Bob";
student.LastName = "Villa";
student.GroupID = 4;
Console.WriteLine(student.Group.GroupName); // Error
The DataSet hasn't properly set the link yet. You would need to commit and refresh your DataSet to make the link. However, if you go with this :
Classroom cr = new Classroom();
Classroom.GroupRow group4 = cr.Group.NewGroupRow();
group4.GroupName = "Group 4";
cr.Group.AddGroupRow(group4);
Classroom.StudentRow student = cr.Student.NewStudentRow();
student.FirstName = "Bob";
student.LastName = "Villa";
student.GroupRow = group4;
Console.WriteLine(student.GroupRow.GroupName); // Works
I know I didn't answer the "How do I configure that relation?" part, but I'm sure you've figured it out already. If not well... drag and drop the matching ID columns on top of another :-P
Edit: It might not be an academic answer and all, but I like to see what something looks like when I try to learn something new.
精彩评论