Not to overwrite

[aishapot]

I have a field in my tables where the guidance counselor would have to type in Notes about the student. For example, Alex is in Grade 1 and he have an offense wherein he pushed his classmate, that note will be saved in the database. And then when he turned Grade 6 he had another offense wherein he was caught cheating in exams. I would need to create another note for his actions.
What can I do to not overwrite the notes I entered when he was in Grade 1?

[Reverend Jim]

Without knowing about the structure of your tables, you could have a table just for notes with a structure like

*Student_ID
*Incident_Date
Note

Where * indicates primary key (compound key consisting of the first two fields). A compound key would ensure a unique primary key.

[ChrisPadgham]

Yes since incident has a many-to-one relationship with student you need a separate table to hold incidents (probably called something like "Incident"). Personally I prefer an autonumber field as a primary key of the Incident table, say IncidentNo.

StudentId should be included in the Incident table as a foreign key.

[bluehangook629]

creating an extra field called Incident_ID (object id to index records stored) in your entity might help indexing each record.

CREATE TABLE dbo.(your_table_name)
(
	Incident_ID INT IDENTITY(1,100) PRIMARY KEY,
	Student_ID VARCHAR(20) NOT NULL,
	Incident_Date SMALLDATETIME NOT NULL,
	Note VARCHAR(MAX) NOT NULL
)

Then you don't have to worry about having compound key for primary key

[Reverend Jim]

Normally I would disagree because most retrievals on that table would not be based on a incident number (which imparts no useful information other than sequence), but on student ID or date (or date range). You want to index your table based on the most likely method of access. However, because the table is not likely to be unwieldy there will likely not be a noticible performance hit.

Further thought, you might want to add another field to contain the student IDs of any other students also involved in the incident. The other IDs could be included as part of the note text but that would make it more difficult to extract those IDs automatically.