Is it good practice to join many tables together [User Data Design]?

Question

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.

Answer 1

Couple thoughts:

it violates normal form conventions on many counts (classes is a list; sometimes there is no data for a column)

As Akina noted in comments, it depends on data you are trying to model.

If you need to model M:N relationship (e. g. User attending multiple classes and classes being attended by more than one user), moving that data into separate table is reasonable thing to do. You will probably end up doing something like having three tables:

• User (UserId, ...)
• Attendance (User_FK, Class_FK) - this is also where you could store things like if the User passed the class, or how good grade they got in this class.
• Class (ClassId, ...)

If you'll need to to model 1:N relationship (User having multiple email addresses?), then do what you suggested: Add separate table linking to your User table.

• Email (UserId, EmailAddress, ...)

BUT if you want to store single value relating to the user (Name, GPA), then normalization is not a reason to move it elsewhere.

I may need a history of changes to info (say GPA changes), and a single table would require duplicating all the other row info

Eh, you might store just the changes in separate history table.

But if you know you will be interested in changes of GPA (but not necessarily rest of the User entity), then putting it into separate table with timestamps can indeed be the way to go.

I may need to add new columns; (I think?) adding a new table is better than backfilling tons of null values

Not really, but it comes down (again), to what kind of other data you will end up adding. If it's something directly related to the User (as in 1:1 relationship), there is little reason to put it elsewhere.

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

Yes, JOINs are bread and butter for relational databases and they are able to perform then quickly and efficiently.