CODEWITHSUNDEEP
mysqlsql
Robbie Dc
Robbie Dc

Reputation: 701

storing multiple data in one mysql column and retrieval of data individually

I have a mysql table used to score marks of an individual student:

studentID int(11)
studentname varchar(70)
maths_score int(11)
science_score int(11)
english_score int(11)

This table is in-efficient compared to the atomicity rule of maintaining databases. Is it possible to store marks of maths-science-english in one column, and can be retrieved individually? If its not possible, how do I conform to the atomicity rule?

Upvotes: 0

Views: 1173

Answers (4)

Oleg
Oleg

Reputation: 2821

You can't store many attributes in one column and then retrieve them individually. You have to store every attribute in a seperate column, to conform to 1 NF.

Upvotes: 0

Polynomial
Polynomial

Reputation: 28316

You should always normalise such data into separate columns. Placing a selection different data into one combined column is a violation of first normal form, which is a standard (if not a requirement) for relational database models.

However, you may duplicate the data into an additional column that allows you to perform certain queries more efficiently. Duplication is a lesser evil than denormalisation. For example, you could store a total_score column that saves you recomputing some form of weighted average, but it would be a bad idea to collapse scores down into one column.

If you're looking for a way to store an arbitrary number of scores, create a table for "subjects" and a table for "scores", and link them so that each student can have multiple scores, each of which relates to a subject.

Upvotes: 2

stivlo
stivlo

Reputation: 85496

Never encode columns into one in a database, you'd lose the ability to search easily. You can use tinyint if you're concerned about the space used.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

Yes, it's possible. But your table will not even be in First Normal Form.

To conform to the atomicity rule in this cas, you need to separate your table in three different tables. One to store student data, another one to store courses (English, Science) and other to store test scores for each student/course combination.

Upvotes: 1

Related Questions