Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hi All,

Im building a Web App with 3 different types of users:

  1. Students 
  2. Parents
  3. Teachers

Should I store them all in one database table or separate tables? I am looking for the scalable best practice option with 4000+ minimum users.

All in one table option, I would have a column with 1, 2 or 3 to specify the users type.

Any help would be great, I can do both but looking for best practice really.

Thanks in advance! 

 

 

Share this post


Link to post
Share on other sites

More information is needed to answer this. Can the users log into the site? I presume a teacher could also be a parent but can a parent or a teacher also be a student?

In general, the user as a concept is a way of identifying a person. A user may also be a way to identify functionality (e.g., log in). But the user's type in your case may not be a static, singular thing but rather be a description of the relationship that user has to another user (e.g., teacher A may teach student B but also be a parent to student C). 

Share this post


Link to post
Share on other sites

Hi Larry,

Yes each type of user (student, parent,teacher) has to login to view their profiles. A teacher can also be a parent but I think this might be a case of different accounts to not complicate things. 

My thought would always be have 1 table of all users and then have one column specify the relationship in that table, But I have been looking at performance when it comes to over 1000+ users and wanted a more experienced answer that could scale beyond that.   

thanks again,

Sam

Share this post


Link to post
Share on other sites

Okay, so fundamentally the users table should identify the user and allow them to login (i.e., store login credentials). I wouldn't assign a single type in the users table b/c users don't have a single type in your scenario; they're type is defined by context. If you need to do something for or with a group of people--email all the parents, create teacher-specific pages--then you'll want to have an easy way to identify "is a teacher" or "is a parent" (but without limiting a user to being just one thing). My inclination would be to create three 1/0 TINYINTs: student, teacher, parent. A student would likely be 1, 0, 0 for those columns whereas a teacher would likely be 0, 1, 0 or 0, 1, 1. Using this approach you have flexibility as to how you refer to people or access lists of groups.

Share this post


Link to post
Share on other sites

Wow, I had not thought of creating it that way but it actually makes so much sense. It looks so simple but saves so much time later down the road! 

Thank you so much for this, I really like Database design but can easily over complicate projects.

Always love learning from you, Stay safe,

Sam 

Share this post


Link to post
Share on other sites

Hi Larry,

I have one last challenging database design question for you. I am looking at a teacher setting a task for students to complete, what is a better way to store the homework and student relationship. The requirements:

  • The teacher can set homework to a group
  • The teacher can specify exactly which students it is set to 

Originally I looked at a table that stores a record for the student_id and the homework assigned too them  

(student_id, homework_id).

but if a teacher selects 2 classes (of 30 students in each) to set homework too, I will have to insert 60 new records!

insert into homework_set_too(student_id, homework_id) VALUES (111, 01),(222, 01),(333, 01), etc.

 

My idea to combat this is adding a new column for set_to_group

(student_id, set_to_group, homework_id).

If student_id is null the homework is set for the whole group but if student_id has a value the homework is set for one specific student.

Sorry if I didn't explain this well. In a nutshell when a teacher sets homework for a group I don't want to insert 60 records as this is probably going to be a really expensive query to run with more then one teacher!

Am I thinking correctly? thanks again

 

 

Share this post


Link to post
Share on other sites

I would think you would have a table of classes, which creates a class ID. Then, depending upon the school structure, you could either create a classes-teachers table that creates the association or the teacher's ID gets pulled into the classes table directly. Assuming assignments are given to an entire class so you'd want to create an assignments table that maps the specific assignments created by a teacher to a specific class. 

Then there would be a table for tracking homework that includes the student ID, the assignment ID, and the data for that homework (date completed, grade). 

As a general rule, I try avoid creating a slew of INSERTs that just reflect "this is a thing that exists". And as you can tell, I design thinking about the relationships. Yes, the net effect is a teacher assigns work to every student, but what's actually happening is the teacher assigns it to the class and every student happens to be in that class. 

Share this post


Link to post
Share on other sites

That's great, it make so much sense!

If I want to also cater for teachers setting assignments to 5-10 students not just a class, how would I include this with the same thinking? 

thanks again, I really appreciate your time.

 

Share this post


Link to post
Share on other sites

If assignments were to a subset of the class, then you'd want to create a more basic assignments, students, and student_assignments arrangement. But we're getting out of the range of what I know about how teachers work. I'd strongly recommend you talk to the target users and get the true sense of how they work and what they need. I'm just guessing!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...