CODEWITHSUNDEEP
mysqldatabase-normalization
Luckyy
Luckyy

Reputation: 1031

Data normalization - mysql

I searched a bit as my case is same as "multiple tables vs comma separated values" but my case is different. I have done both practices but for a new requirement I am bit confused as the columns that contain comma separated columns are around 3-4...so what guys would suggest...

Example (Existing Scenario):

Student Table
---------------------
id name subjectsEnrolled gamesEnrolled       BooksIssued             absentOn

1  Naya yoga, maths      cricket, football    aka#date, baka#date,caka#date   20121010,20130103
2  sait science, botany  football,chess,abc   prea#date, sunbaka#date      20121010,20130103

Possible Fixes I have thought

Student Table (as student master data)

id name moredetailsofstudent like address & blah blah

1  Naya              
2  Naya

then having another table absentTable 

StudentId absentOn
1  20121010
1  20121011
2  20121010
2  20121011
1  20121012

then having another tables master "booksTable" with unique ids of books & booksIssueTable

booksIssueTable

bookid issuedby dated
4       1       a date
2       1       a date

and almost same for games (2 tables) & subjects (2 table) and storing same way i am doing above for books.

gameid enrolledby 
4       1      
2       12
4       10      
3       12

and so on...

Am I going correct...just this is what i want to know from our database & coder experts.

note: I am not creating a school management...its just an example...as i couldn't post real data. In real scenraio I have too big data in main student table like 10-20 lac ...so absentTable, booksIssueTable, gamesEnollTable, subjectenrollTable can have easily 10 lac records * 1-10...= around 30-90 lacs each if i go with this solution. Please suggest.

Update

As of now, I have learnt that I am going in right direction, what would anyone suggest to have the dates in absenttable. Should i create a master table for the dates too, and then using its dateid to bind the absentTable ?

Upvotes: 1

Views: 165

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562240

Yes, normalizing is better than storing comma-separated values. I gave a popular answer for the pitfalls of comma-separated values here: Is storing a delimited list in a database column really that bad?

As for your other question, no I wouldn't make a lookup table for simple date values. The scenario where that might be useful would be when you have many rows in AbsentTable that have the same date, and you would want to change the value for all these rows by updating a single row somewhere else. But I expect your AbsentTable would never need to do this.

By the way, using an id to refer to an entry in another table has nothing to do with normalization. You can use natural values both as the primary key of the referenced table, and the foreign key in the referencing table, and still both tables comply with normal form.

You may also be interested in what I have written about comma-separated values and database normalization in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Upvotes: 1

fcm
fcm

Reputation: 6453

You're doing exactly what's expected to get a solution to your problem, even with large amounts of data, it gets to a point where having proper m-n tables will improve a lot more than having values separated by commas in the same fields.

So good job in finding that solution for yourself. Can't suggest any better.

Upvotes: 0

Related Questions