I'm trying to design a database for storing information about users--say name, email, GPA, classes, and more information in the future.
I believe the proper design involves storing each field as a row in separate tables (userId-basicInfo table, userId-email table, userId-GPA table, userId-class table). My previous idea was to have a single table with all of these as entries, but I believe this is a worse implementation because
- it violates normal form conventions on many counts (classes is a list; sometimes there is no data for a column)
- I may need a history of changes to info (say GPA changes), and a single table would require duplicating all the other row info
- I may need to add new columns; (I think?) adding a new table is better than backfilling tons of null values
So this is mostly thought out, my one concern being: I will often need to select all information about a user, which will join together all the tables on userId (effectively recreating a row from the big table in my first DB design). Is this a good operation to perform often? I have not worked with joins in production too much, but in theory it sounds find.