0

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.

2
  • Which database system and version are you using?
    – J.D.
    Feb 28, 2023 at 4:03
  • 1
    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). Depends on the data. If the value is a member of a list, including multi-valued ones, then separate table should be used (classes). If you need to track the changes then separate table should be used (GPAs). But some properties are freely-entered single-valued, these values should be stored in main table (email or primary email if multiple ones can be used). Joining many tables is safe, it only needs in proper indexing.
    – Akina
    Feb 28, 2023 at 5:23

1 Answer 1

0

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.

1
  • I see, thanks! Good to know that JOINs are bread and butter, I remember learning the process from my online databases class but it's intimidating to do it myself in production. I think I will go with the multiple table approach but keep an eye out for storing "freely-entered single-valued" properties in the main table, as Akina said. My concern with using these properties is I may need to eventually migrate them into their own table anyways--say there are more ways to login (facebook, gmail, etc).
    – Dan
    Feb 28, 2023 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.