CODEWITHSUNDEEP
mysqlsqlarrays2d
Mr Robot
Mr Robot

Reputation: 1216

2D data stored in SQL

This question is about SQL.

Students data (names, birthday ...) are stored in a first table (STUDENTS). Assessments data (date, time, teacher ...) are stored in a second table (ASSESSMENTS).

Now, I want to store the answers of each student to each assessment. Should I create a new table (ANSWERS for example) that store each answer for each students with two keys (one linked to STUDENTS and the other linked to ASSESSMENTS) ? Is it the best practice ? If yes, I worry about performances with a large number of students and assessments.

Upvotes: 0

Views: 306

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

This is a bit long for a comment.

The use of what you call ANSWERS -- and what I would call StudentAssessments -- is the right way to store this information in a relational database.

This is called a junction table in SQL and it is the standard way of implementing n-m relationships.

Some databases have alternative methods of storing lists within a field -- nested tables, JSON, arrays, XML. However, these generally do no fit the relational model very well. For instance, foreign key references often cannot be defined and enforced.

I would not worry about a "large" amount of data. What you consider to be big, is probably pretty small by database standards.

Upvotes: 2

Related Questions