2

enter image description here

Is my schema correct? I tried this because my design plan is supposed to have repeating entries of offenses.
Something like this:

StudName   |    OffDesc
-----------+-------------
John       |    Cheating
John       |    Bullying
John       |    Cheating

I wonder if that is correct? If not, how do I remedy it?

  • 2
    Please edit your title to specifically describe your question. – Basil Bourque Aug 12 '17 at 4:53
2

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.

Example:

You have two Dimensions:

  1. Student_DIM / tblStud
  2. Offense_DIM / tblOff

And a FACT table that tells FACTS or aggregates of other dimensions to answer a question.

  1. 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

  1. How many students typically are engaging in theft around Spring during Mondays vs Fridays?
  2. Does a Semester statistically matter? Or does the student?
    • (since we have DIM tables, we can map characteristics or types of students)
  3. Does a students characteristics and social status have influence on types of offenses?
  4. 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?
  • I understand now. You provided a very useful answer. Thank you! @clifton_h – NoobCoder Aug 12 '17 at 4:50
  • @NoobCoder thanks! I take it as my mission to help people understand how structure and data should be answering questions that are not readily seen. It makes it fun and meaningful to you, too. :) Oh, and note the DATE datatype will not keep time of day. If you were to wish to eventually track hours and maybe minutes, use DATETIME2 instead. – clifton_h Aug 12 '17 at 4:56
1

This looks like a perfect example of a cross-reference table that joins offences with students.

You might consider adding other columns to the "get" table, such as offence date, location, and other pertinent details.

Ensure you add indexes on the "get" table to support seeks based on students or offences.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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