Linking multiple entries to a particular User email/Id in a database

LATEST QUESTION TITLE:Linking multiple entries to a particular User email/Id in a database.
Having a form as below, what should I do to my database or how should I structure my database so that for instance, a particular user will have all the three entries attached to him:

First Entry

Second Entry

Third Entry

To submit my data to the database in mysql, I tried using a table "course" in a database with the respective numbers of Columns "email"(Primary key), "subject", "grade", "year". It complained of duplicate entries (Primary key). But when I changed the structure of the "course" table to "Id_no"(Primary key), "subject", "grade", "year", the data was submitted. But herein lies the big question? How do I associate the three entries to that one particular user using his email or his user ID??? PLEASE YOUR HELP WILL BE APPRECIATED!

What I’d do is have a table containing users (including a surrogate key for their userID), and then a second table for the user grades. The relationship between the user and grades tables will be a one-to-many relationship (i.e. one user can have many grades). You can enforce this relationship by placing a foreign key (the userID) in the grades table, thus linking a user to their grades.

The relationship between the tables are one to many (one for tables Students and Subjects, many for table grades)

The depictions of your Diagram shows that I should have 3 tables, but my question is dont you think from the “Grade Table” that I will be having duplicate entries in terms of the primary key? Because if were to use a an email/Id of sheep@yahoo.com: You could have:
Entries as below:
sheep@yahoo.com, 323, 2001, A (query will likely stop here!, because a duplicate primary key (sheep@yahoo.com) entry exist below)
sheep@yahoo.com, 324, 2001, A

Look at the data you’re storing. A user can have a number of grades, correct? Thus, you could have a table that looks like the following:

Your StudentID field would be the surrogate primary key - a key that will always be unique because it is automatically generated (via auto incrementing). But look at how the data in it would be represented:

Even from just one student’s records, you have duplication in there (the email field), which means if you would like to update a student’s email, you’d have to do so in multiple places. (Plus duplication takes up unnecessary space in the database). The duplication issue gets worse when multiple users are inserted who do the same course, thereby replicating the course names too.

@molona’s solution identified three main entities: students, courses, and grades, which resulted in three different relations. This prevents duplication, and if you have a look at his picture, we can identify the primary keys:

  • The Students table will have a surrogate primary key (StudentID)
  • The Subjects table will also have a surrogate primary key (SubjectID)
  • The Grades table will have a composite primary key, consisting of the studentID (from the Students table) and SubjectID (from the Subjects table). This works because a student will only ever have one grade for each subject - though if a students grades for multiple years are recorded, then you may wish to add the gradesYear column to that composite primary key to ensure unique records.
1 Like

@tpunt A brilliant explanation. Thank you.

@erabxes I’m truly sorry that I haven’t been more detailed. I don’t know how much experience you have with databases (I suspect that not much but you’re learning fast ;))

tpunt has explained brilliantly but maybe you’re not familiar with terms such as surrogate key or primary key, let me know and I’ll try to add up to what tpunt said

After digging a bit, not deeply though into relational database. I came up with something like this below:

Well, what about me using something like this below for my table so that no matter the number of entries entered in, as long as a subject is not repeated, the computer still has a way of identifying that the present crop of data all still belongs to a particular email_id. My table is below:

Login_table: {“email_id”, “Password”}.
Courses_table: {“Subject_id”,“grade”, “year”, “email_id”}.
Subject_id: {“Subject_id”, “Subject”}.

Yeah am not yet good @ database stuff. I am not very clear with some terms like foreign keys, surrogate keys and something like a composite key. I will just have to study a little bit more on database and relational database. @molona, @tpunt A very big thanks to you guys, (whom i can refer to as worthy mentors), I will definitely do more to increase my knowledge base as regards this concept. Thanks once again.

The only problem I can see with that setup is with using a student’s email as a primary key for your login_table. Emails are lengthy (I believe the maximum length is 254 characters) and are vulnerable to change (i.e. if a student has forgotten/lost their email, then they may need a new one). This does not make them suitable as a primary key, despite the fact that they are guaranteed to be unique. It would therefore be more suitable to use a surrogate* primary key instead of a natural* primary key for login_table, and place a unique key constraint upon the email_id field.

*A surrogate primary key is a generated key (typically via auto-incrementing from the previous ID). It has no relevance nor attachment to the record that it is apart of. It is simply there to uniquely identify that row in a more efficient way that using textual-based keys.
*A natural primary key is a key that is already unique according to your business rules (like your email_id field) and is suitable to be a primary key in that it’s short and isn’t likely to change (unlike your email_id field).

1 Like

@tpunt is right.

I like to see that you’re trynig to find your own solution and it is a good try. I do have to say email_id may not be as unique as you think (maybe two students using the same e-mail, for whatever reason?) and there’s a reason to create an artificial key: speed.

If you use the email as the table key is text and computers handle numbers much better than text. And even if another field could be used as a unique identifier (I don’t know, the number of a passport if we forget the characters :p) still creating an artificial (= surrogate) key will help to make the database faster because a passport has a minimum of 8 digits and the surrogate key can start with number 1. That’s 7 digits less and it will take quite a while till the table needs a number of 8 digits for the next student :smiley: