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?


  • Height is like age - it only has a single value at any given point in time. It is that last part you are missing. You cannot derive height based on the passage of time (as you can for age) so it must be measured. There is no such entity as Student_Height without the missing attribute of "measurement" - which is date (or datetime). The idea of "prepopulating" a height table and linking the measurement via a foreign key would generally be considered as wasteful or inefficient.
    – SMor
    Aug 25, 2020 at 12:42

2 Answers 2


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?"

enter image description here

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:

  Student Student
  StudentMeasurement StudentMeasurement
    ON StudentMeasurement.StudentId = Student.StudentId
        AND StudentMeasurement.EntryDtm =
              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).

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

  • However, suppose students can have multiple heights, which stems from measurements taken every half year. Aug 25, 2020 at 9:46
  • 1
    Have you read my answer? Do you understand what I mean by "time_taken"? Aug 25, 2020 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.