Many-To-Many relationship between Students and heights (Is it necessary?)

Question

So I'm trying to design a database for my local school doctor. (This is somewhat analogous to my real problem)

Currently, a student is scheduled for a health check every half year, where the students height is measured.

As it can be seen, one student therefore may have multiple heights, which is signified by a join table, i.e. Student_Height.

To me, this seems like the best way to associate multiple heights to One student. The only downsite is though, that since we may have 1000 students, it is pretty certain that the same height will be measured multiple times - Is this bad practice, and is it overkill to have an extra table for this?

As I see it, this issue could alternatively be solved in two ways:

• Pre-populate the height table with heights (i.e. 1, 1.1, 1.2, 1.3, ... 2.9,3.0 ... 180.0 cm).
• Put height as a column in the student table, and let it be a Varchar(Max) - Meaning, it could be a comma-separated string of heights. (I don't like this - since it limits/complicates my PowerBi possibilities)

Please let me hear your thoughts on this. Are my thoughts above justified?

Answer 1

A many-to-many relationship is not necessary because the relation is one-to-many.

Each Student is measured zero to many times.

"Measurement" in this context could refer to any number of attributes of Student, not just Height. How we create these entities depends on how you need to conceptualize the information and whether all attributes are collected at each measurement, or if only some may be updated at a point in time, or if you need to quickly (and definitively) answer questions like: "When was the last time (attribute) was measured?"

In each case, the primary key becomes (StudentId,EntryDtm). There is no need for a redundant row identifier.

If you need the value as of a point in time, this is easily done with a correlated subquery and the logic can later be added to a view/TVF:

SELECT
  Student.StudentId
 ,StudentMeasurement.Height
 <etc>
FROM
  Student Student
LEFT JOIN
  StudentMeasurement StudentMeasurement
    ON StudentMeasurement.StudentId = Student.StudentId
        AND StudentMeasurement.EntryDtm =
          (
            SELECT
              MAX(EntryDtm)
            FROM
              StudentMeasurement
            WHERE
              StudentId = Student.StudentId
                AND EntryDtm <= <Some Datetime value>
          )

Other notes:

1. Never, ever, have just Id.
2. Measurement units may differ (or change), so you might want to have a reference UnitOfMeasure to denote the unit used (CM, M, G, KG, FT, etc).

Answer 2

Height is not an entity. A student can't have multiple heights, unless your measurements are sometimes incorrect. Unless there's been some time between measurements. Measurements could be an entity, identified by time_taken and student_id, attributes height, weight, and things perhaps too private