A Design Question using a fictious example

Question

Post reply

A Design Question using a fictious example

eichnerm

Old Hand

Points: 359
More actions
November 19, 2024 at 2:02 am

#4485689
Background:
I have two tables. Tests and Grades. For each Tests record there are many Grades; one for each student. The Grades table has a field called: StudentsGrade. Each time a Tests record is inserted into the database, an associated Grades record is inserted into the database, one for each student where the value of StudentsGrade is set to null.
Question:
My app has to list all of the Tests that the teacher hasn't finished grading. This query happens quite a bit. I have two ways of doing this:
1. Select each Tests record where the associated students Grades record has a StudentsGrade of null, meaning the teacher hasn't graded all of the tests.
2. Add a flag to the Tests table called EveryTestHasBeenGraded and set it's value to false until the teacher assigned a grade for each student who took that specific test.
Normally I would always go with option number one. This is my first phone app. I was told that on phone apps that I need to minimize trips to the database as well as database joins where ever possible. Adding the flag into the Tests table does this by eliminating a join with the Grades table.
I am curious if option number 2 is a better option for a phone app than option number one.
Thanks
November 20, 2024 at 10:38 am

This was removed by the editor as SPAM
November 20, 2024 at 10:39 am

This was removed by the editor as SPAM
November 20, 2024 at 10:40 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Sounds like you're missing the other table, Student.

Student(StudentID (PK),...)

StudentTest(StudentID (PK1), TestID (PK2), TestScore)

Test(TestID (PK), TestDate...)

if you allow nulls in StudentTest(Score), then you can just cross join a filtered view of Test with the Students and append it. Then you can update the scores...

All the tests the teacher hasn't finished grading would be stupid simple:

SELECT * FROM STudentTest WHERE TestScore IS NULL.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

I would use option 2 in this case. The safest way to set the flag consistently accurately is to use a trigger on the StudentsGrade table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

eichnerm Old Hand Points: 359 More actions · Answer 3

Thanks for getting back to me. I may not have explained myself properly with my fictitious example, but it appears that your StudentTest table servers the same purpose as my Grades table. That being said, my example should have included a Students table.

The real question is should I put a flag in the Tests table that indicates if all the Tests have been graded or use a join on the Grades/StudentTest table with the Tests table?

I have always avoided using flags to determine the state of something when I can determine its state via a query. Someone I know who builds phone apps for a living told me that I should do everything I can to minimize database access. Using a flag in the parent table gets rid of the join with the child table. I am not sure if the flag is a good or a bad idea.

Ignore any syntax errors I am just re learning SQL after years of not using it.

Select * from Tests where Tests.EveryTestHasBeenGraded = True

versus

Select * from Tests
Inner Join Grades
On Tests.TestID = Grades.TestID
Where Grades.TestScore = Null

Thanks

This reply was modified 1 month ago by eichnerm.
This reply was modified 1 month ago by eichnerm.

eichnerm Old Hand Points: 359 More actions · Answer 4

Thanks for getting back to me.

I have always been hesitant to use a flag in one table when I could get that same information using a database query. Since the only person I know that builds phone apps told me that I should avoid joins and other database access when possible when building a phone app, I came up with the idea of using a flag in the parent table to avoid querying the child table. It just feels wrong. Thanks for verifying that its not a bad idea.

Steve Collins SSCrazy Points: 2173 More actions · Answer 5

eichnerm wrote:

Thanks for getting back to me.
I have always been hesitant to use a flag in one table when I could get that same information using a database query. Since the only person I know that builds phone apps told me that I should avoid joins and other database access when possible when building a phone app, I came up with the idea of using a flag in the parent table to avoid querying the child table. It just feels wrong. Thanks for verifying that its not a bad idea.

Option 1 for all the reasons you've always been hesitant. What difference the client is a phone?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101120 More actions · Answer 6

> What difference the client is a phone? <<

Because to determine all the Tests that haven't been fully graded could require scanning a very large number of StudentGrade rows.

If, instead, the query is based on a flag in the Tests table, it can be satisfied with a nonclus index on the Tests table, reading far fewer rows and all in contiguous blocks.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Collins SSCrazy Points: 2173 More actions · Answer 7

ScottPletcher wrote:

> What difference the client is a phone? <<
Because to determine all the Tests that haven't been fully graded could require scanning a very large number of StudentGrade rows.
If, instead, the query is based on a flag in the Tests table, it can be satisfied with a nonclus index on the Tests table, reading far fewer rows and all in contiguous blocks.

Which has precisely nothing to do with the data client being a phone

Storing summarized information is a practical optimization which might make sense if there's an issue to address. Without a reason tho it seems like it could be an early optimization which is wasteful. SELECT statements are reliable and high performance if the query and tables etc are well designed

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Using your fictitious example, if you have the correct indexes in place, the code can be designed to do a seek to the correct row in "Grades" based on the test and the student id. There's no need for a "flag" of sorts because the whole thing should be setup to very quickly identify test/student/grade combinations where grades have a NULL for the grade.

So, Option 1 is what I'd go for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eichnerm Old Hand Points: 359 More actions · Answer 9

I have never built a phone app before. The only phone app builder that I know told me that because of hardware and memory limitations on a phone that you have to consider doing things for a phone that you wouldn't do for say a website. I don't know if this is true, but that's what he told me.

eichnerm Old Hand Points: 359 More actions · Answer 10

Thanks for getting back to me. I prefer letting the database do the work.

eichnerm Old Hand Points: 359 More actions · Answer 11

Thank you. I prefer that option as well. The only reason that I asked the question was because of something a phone app programmer told me.