Looks good to me. In order to keep this as a viable entry in SO, lets answer the basic scheme and what I mean by historical reference with an example.
Your Scheme: Star Schema
In most design theories, you will hear about Star and Snowflake schemas. Without going too deep, Star Schema about Dimensions and Facts:
- Dimensions tell horizontal details about an entity that is unique across the database. [Bob's details]
- Facts are the aggregation of these Dimensions that answer a question. Example:
Tables [Students_DIM] and [Teachers_DIM] have a Fact table [Class_FACT] that show how the students are mapped to each other.
You have two Dimensions:
- Student_DIM / tblStud
- Offense_DIM / tblOff
And a FACT table that tells FACTS or aggregates of other dimensions to answer a question.
- Offense_FACT / tblGet
Your Fact table correctly uses a Database key between the dimension tables. Avoid natural keys that expose information in the keys and usually limit your design to wasteful strings and similar concatenations.
Since your FACT table needs to answer a question and also is neatly fit for reporting, take advantage and add meaningful historic columns:
CREATE TABLE Offense_FACT
( RecordNum INT
, StudID INT
, OffID INT
, Semester TINYINT
, Offense_Date DATE
Notice now how this FACT table can not only let you query for all the offenses a student has had, neatly, but also is able to answer business, sociological, and seasonal questions such as
- How many students typically are engaging in theft around Spring during Mondays vs Fridays?
- Does a Semester statistically matter? Or does the student?
- (since we have DIM tables, we can map characteristics or types of students)
- Does a students characteristics and social status have influence on types of offenses?
- For students that are older than 24 (when in California you can rent a car on your own), is there a generational difference between older and younger students?