Skip to main content
Stack Overflow
  1. About
  3. For Teams

Return to Answer

added 65 characters in body
Source Link
PerformanceDBA
  • 34k
  • 10
  • 72
  • 94

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

  4. The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

     SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCodeRaceDate  = @RacecourseCode@RaceDate
AND RaceDateRacecourseCode = @RacecourseCode -- to print =entire @RaceDaterace form,
AND RaceNo = @RaceNo -- remove these 2 lines
ORDER BY Position

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

  4. The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

     SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCode = @RacecourseCode
AND RaceDate = @RaceDate
AND RaceNo = @RaceNo
ORDER BY Position

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

  4. The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

     SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RaceDate  = @RaceDate
AND RacecourseCode = @RacecourseCode -- to print entire race form,
AND RaceNo = @RaceNo -- remove these 2 lines
ORDER BY Position

Added a few words for clarity
Source Link
PerformanceDBA
  • 34k
  • 10
  • 72
  • 94

Note, OwnerId, JockeyId, and TrainerId are all PersonIds. No use manufacturing new ones when there is a perfectly good unique one already sitting there in the table. Just rename it to reflect its Role, and the PK of the table that it is in (the relevance of this will become clear when you code).

MultipleSELECTSare nothing to be scared of, SQL is a cumbersome language but that is all we have. The problem is:

  • the complexity (necessary due to a bad model) of eachSELECT

  • and whether you learn and understand how to use subqueries or not.

    • Single level queries are obviously very limited, and will lead to procedural (row-by-row) processing instead of set-processing.

    • Single level queries result in huge result sets that then have to be beaten into submission using GROUP BY, etc. Not good for performance, churning through all that unwanted data; better to get only the data you really want.

Now the queries.

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

  4. The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

     SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCode = @RacecourseCode
AND RaceDate = @RaceDate
AND RaceNo = @RaceNo
ORDER BY Position

The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

 SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCode = @RacecourseCode
AND RaceDate = @RaceDate
AND RaceNo = @RaceNo
ORDER BY Position

Note, OwnerId, JockeyId, and TrainerId are all PersonIds. No use manufacturing new ones when there is a perfectly good unique one already sitting there in the table. Just rename it to reflect its Role.

Now the queries.

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

 SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCode = @RacecourseCode
AND RaceDate = @RaceDate
AND RaceNo = @RaceNo
ORDER BY Position

Note, OwnerId, JockeyId, and TrainerId are all PersonIds. No use manufacturing new ones when there is a perfectly good unique one already sitting there in the table. Just rename it to reflect its Role, and the PK of the table that it is in (the relevance of this will become clear when you code).

MultipleSELECTSare nothing to be scared of, SQL is a cumbersome language but that is all we have. The problem is:

  • the complexity (necessary due to a bad model) of eachSELECT

  • and whether you learn and understand how to use subqueries or not.

    • Single level queries are obviously very limited, and will lead to procedural (row-by-row) processing instead of set-processing.

    • Single level queries result in huge result sets that then have to be beaten into submission using GROUP BY, etc. Not good for performance, churning through all that unwanted data; better to get only the data you really want.

Now the queries.

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

  4. The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

     SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCode = @RacecourseCode
AND RaceDate = @RaceDate
AND RaceNo = @RaceNo
ORDER BY Position

Source Link
PerformanceDBA
  • 34k
  • 10
  • 72
  • 94

Relational database tag, and you want advice on your schema as per title. Even though the single question is answered, you may have more tomorrow.

I couldn't make any sense of your three flat files, so I drew them up into what they might look like in a ▶Relational database◀, where the information is organised and queries are easy. Going brain dead is not unusual when the information remains in its complex form.

If you have not seen the Relational Modelling Standard, you might need the IDEF1X Notation.

Note, OwnerId, JockeyId, and TrainerId are all PersonIds. No use manufacturing new ones when there is a perfectly good unique one already sitting there in the table. Just rename it to reflect its Role.

Now the queries.

  1. When you are printing race forms, I think you will need the Position scheduled and advertised for the RaceEntry; it is not an element of a Runner.

  2. Now that we have gotten rid of those Ids all over the place, which force all sorts of unnecessary joins, we can join directly to the parents concerned (less joins). Eg. for the Race Form, which is only concerned with RaceEntry, for the Owner, you can join to directly to Person using WHERE OwnerId = Person.PersonId; no need to join HorseRegistered or Owner.

  3. LEFT and RIGHT joins are OUTER joins, which means the rows on one side may be missing. That method has been answered, and you will get Nulls, which you have to process later (more code and cycles). I do not think that is what you want, if you are filling forms or a web page.

The concept here is to think is terms of Relational sets, not row-by-row processing. But you need a database for that. Now that we have a bit of Relational power in the beast, you can try this for the Race Result (not the Race Form), instead of procedural processing. These are Scalar Subqueries. For the passed Race Identifiers (the outer query is only concerned with a Race):

 SELECT (SELECT ISNULL(Place, " ")
FROM Runner
WHERE RacecourseCode = RE.RacecourseCode
AND RaceDate = RE.RaceDate
AND RaceNo = RE.RaceNo
AND HorseId = RE.HorseId) AS Finish,
(SELECT ISNULL(Name, "SCRATCH")
FROM Runner R,
Horse H
WHERE R.RacecourseCode = RE.RacecourseCode
AND R.RaceDate = RE.RaceDate
AND R.RaceNo = RE.RaceNo
AND R.HorseId = RE.HorseId
AND H.HorseId = RE.HorseId) AS Horse,
-- Details,
(SELECT Name FROM Person WHERE PersonId = RE.TrainerId) AS Trainer,
(SELECT Name FROM Person WHERE PersonId = RE.JockeyId) AS Jockey,
ISP AS SP,
Weight AS Wt
FROM RaceEntry RE
WHERE RacecourseCode = @RacecourseCode
AND RaceDate = @RaceDate
AND RaceNo = @RaceNo
ORDER BY Position

lang-sql

AltStyle によって変換されたページ (->オリジナル) /