Connecting two different ‘user’ tables into one single table [closed]

Posted on

Question :

How to connect three tables so when the admin inserts one row in a table, that row will be copied and pasted in another table? Or how to define a relationship between them?

I have three tables in a database, they are tb_student, tb_teacher, tb_user. Their DDL design is shown below:

-- Teacher Table: Primary Key is `id_teacher`

CREATE TABLE `tb_teacher` (
  `id_teacher` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Sample Data

INSERT INTO `tb_teacher` (`id_teacher`, `name`, `status`) VALUES
('14320', 'Lenny Belardo', 'active'),
('14323', 'Shoffin Nahwa Utama', 'nonactive');

-- Student Table: Primary Key is `id_student`

CREATE TABLE `tb_student` (
  `id_student` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entry_year` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Sample Data

INSERT INTO `tb_student` (`id_student`, `name`, `entry_year`, `status`) VALUES
('352014110577', 'Buddha Gautama', '2014', 'active'),
('352014121153', 'Harie Tanoesoedibjo', '2015', 'active');

tb_user is kind of like the combination of in both tb_student and tb_teacher

-- tb_user: Primary key is ID    

CREATE TABLE `tb_user` (
  `id_user` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entry_year` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `level` enum('admin','teacher','student') COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I intend to create accounts for both students and teachers; therefore, a user table is necessary to create. The data type from tb_student.id_student and tb_teacher.id_teacher is matching with tb_user.id_user which is VARCHAR(13). The sample data is as the following:

INSERT INTO `tb_user` (`id_user`, `username`, `name`, `password`, `entry_year`, `level`, `status`) VALUES
('14231', 'spectrum@unida.gontor.ac.id', 'Nunik Siti Nurzanah', '123456', '2014', 'teacher', 'active'),
('352014121188', 'tukiyem@ngebel.udik.gov.id', 'Tukiyem', '123456', '2012', 'student', 'active');

Where the id 14231 is a teacher and the id 352014121188 is a student.

How to define a relationship between these three tables so that when the admin inserts a new row of student/teacher, the data is copied automatically to the user table?

Right now I still need to insert the data for these three tables manually or one-by-one.

Answer :

Option 1: Single table

If you don’t really have a lot of unique columns that belong only in tb_student or in tb_teacher, then it might make sense to combine everything in the tb_user table, and there would be no need for the tb_teacher and tb_student tables. They would be superfluous. But there could be reasons for keeping them separate, like maybe relationships to the other tables in your schema.

Option 2: Normalisation

So if you try to follow the principles of normalisation, and keep three separate tables, then all columns that are unique to the tb_student tables should be found only there, and likewise with the tb_teacher table. (You don’t have anything unique to the tb_teacher table at the moment, but maybe you’ll think of something later?) You will need to add a column e.g. id_tb_user in each of the tb_student and tb_teacher tables, and declare foreign key constraints for these columns referring to the tb_user table. E.g.:

CREATE TABLE tb_teacher (
...
  id_tb_user int unsigned,
...
  CONSTRAINT tb_teacher_fk1 FOREIGN KEY (id_tb_user)
    REFERENCES tb_user(id_user)
...
)

The tb_user table will contain all the shared columns: name, status and some of the other columns you already have, but not entry_year.

To retrieve information about students, including info about them in the tb_user table, you will then use a query with a simple INNER JOIN:

SELECT s.id_student, 
  s.entry_year, 
  u.username, 
  u.name -- + any other columns you want
FROM tb_student s
  INNER JOIN tb_user u ON s.id_tb_user = u.id_user
WHERE 
  u.username = 'spectrum@unida.gontor.ac.id'; 
  -- or any other condition you want

(To retrieve info about students and teachers in a single query, use instead two LEFT OUTER JOINs.)

By the way, you should also explicitly declare your primary keys as such. It’s also a good idea to use int unsigned as the data type for these columns (or even bigint unsigned, although that is rarely needed), and it’s also often a good idea to use auto_increment (see also the FAQ) so that MariaDB/MySQL does the job of generating a new id when you insert a new row:

CREATE TABLE tb_user (
    `id_user` int unsigned auto_increment PRIMARY KEY,

Leave a Reply

Your email address will not be published. Required fields are marked *