Question 261313 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL table with almost same fields – Can i reduce duplication?

I’m creating a school sql system and have this table planning:

enter image description here

Note that i have almost the same tables for students and professor, it only differs by course column. Is there a way to create like a unique users table to join both and differs it only by these fields?

Tags:

2

Answers


  1. 0

    I that you’re asking if you can join different tables together if they don’t have the exact same primary keys.
    The answer is yes in some cases. You can’t have two primary keys of the same name in a database.

    So you could do

    SELECT students.id, professors.id
    FROM students
    INNER JOIN
    on Professors WHERE students.id=professors.id

    This issue with this is that the data type for the primary key in students might be different from the data type of the primary key in professors.

    Below is a rough code for how you could do it. I doubt this specifically will work, but you get the idea. I’m selecting everything from Students and Joining it to professors. This would read as a huge table.
    SELECT *
    FROM Students
    UNION
    SELECT *
    FROM Professors

    What I recommend doing, is creating a third table and just insert data from both the Students and Professors table. Joining them, to me, would be more troublesome.

    By creating a new table you can just have multiple Primary Keys with differing data times. This table would also be big, but rather than creating some super complex joining formula, you can just insert the data piece by piece.

    Then you can do something like:

    INSERT into NewTable Students.ID

    FROM Students…

    Login or Signup to reply.
  2. 0

    You can put common columns into 1 Table (i.e Users) and a separate table for category specific columns.

    Example:

    Users
========
Id
Name
DateOfBirth


Students
==========
UserId (FK to Users.Id)
Classes


Professors
==========
UserId (FK to User.Id)

    There are plenty of approaches, but this is just one of them if you want to normalize your database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search