0

I have five tables for school (for example) and I use Entity Framework.

Look This Image (Tables Relationship)

for example I want to get "Book Name" of one record of exam.

in "1": I must Join "Exam" and "Teach" and "Book" to get name of Book.

int "2": I must Join "Exam" and "Book" to get name of Book. with 2 "Join" But we have redundancy.

which one is better? 1 or 2?


and another question:
in "2" "Join" is better or using this?

First Step: I get BookId from ExamTable.
Second Step: I get BookName from BookTable by BookId

int BookId = db.Exams.Where(w => w.ExamId == 23).SingleOrDefault().BookId;
string BookName = db.Books.Where(w => w.BookId == BookId).SingleOrDefault().BookName;
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Sasan Salem
  • 149
  • 1
  • 11
  • 2
    I would say neither of your examples is properly normalized. – Sean Lange Oct 06 '17 at 19:54
  • OK @SeanLange. can you explain more? In your opinion, which relationship is better? – Sasan Salem Oct 06 '17 at 20:08
  • 1
    Neither one is better. They are both kind of a mess. I would not use either of those schemas. – Sean Lange Oct 06 '17 at 20:16
  • Hi. You need to read a book on information modeling & database design. One of the things you will learn is normalization. Any justified answer to your question is a chapter or chapters. So this question is "too broad"--reason to lock out answers until improved. If you aren't following one, you haven't "shown any research effort"--see downvote arrow mouseover text. Also, "better" means nothing unless you tell us what *you* mean by it. Effectively you are just asking for others to teach you and/or do your work. (Also there are a zillion other too-broad questions on redundancy vs joins to find.) – philipxy Oct 07 '17 at 06:32
  • tanks you feedback @philipxy. I thought there was a general rule. I always used method 1 and thought that doing any redundancy was wrong. But recently I felt maybe "a lot of connection between tables" is not good. Maybe redundancy is good in some cases. and by ask this question, I realized that there was no general rule. I will Read "information modeling & database design" – Sasan Salem Oct 07 '17 at 18:04
  • I have no idea what your cases are cases *of* or what rules you are applying. ER & (here) pseudo-ER diagrams don't contain sufficient information to justify themselves, but you seem to think they do, so your notions of cases & rules seem misconceived & unhelpful. "Relation(ship)" in "*relational* database" means *business relationship* represented by a *table* & [design is about finding them](https://stackoverflow.com/a/42193358/3404097). FKs/lines, wrongly called "relationships", [are just fallout](https://stackoverflow.com/a/42821657/3404097). Good luck. – philipxy Oct 07 '17 at 19:06

1 Answers1

1

There is no one who can tell you which one is better because this is related to the use case itself.

But one thing I would advise you to do is not using Entity Framework to access the tables but create views which do the joins and use EF to query the views (or even stored procedures).

This makes the use of EF easier and performance tuning a view is simpler than tuning a EF query.

Bave
  • 235
  • 1
  • 6