Mysql tables into one table

Question

CREATE TABLE emp_u (
        id      INT NOT NULL AUTO_INCREMENT,
        emailAddress    VARCHAR(100) NOT NULL,
        username        VARCHAR(30) NOT NULL,
        passwd          VARCHAR(40) NOT NULL,
        title           CHAR(4) NOT NULL,
        firstName       VARCHAR(50) NOT NULL,
        surname         VARCHAR(50) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY(emailAddress),
        UNIQUE KEY(username),
        KEY idx_fullname (firstName, surname)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE emp_u_p (
    employer_id_fk  INT NOT NULL DEFAULT 0,
    s_mgt           TINYINT(1) NOT NULL DEFAULT 0,
    add_j_mgt       TINYINT(1) NOT NULL DEFAULT 0,
    edit_j_mgt      TINYINT(1) NOT NULL DEFAULT 0,
    del_j_mgt       TINYINT(1) NOT NULL DEFAULT 0,
    j_mgt           TINYINT(1) NOT NULL default 0,
    cp_mgt  TINYINT(1) NOT NULL default 0,
    ph_mgt      TINYINT(1) NOT NULL default 0,
    cb_mgt      TINYINT(1) NOT NULL default 0,
    search_mgt      TINYINT(1) NOT NULL default 0,
    PRIMARY KEY (employer_id_fk)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I have these two tables in my database. Will be better to combine these two tables together or leave then as it is.

I'm new to database and just write some tables for my mysql.

What will you use these tables for? Is `emp` is short for `employee`? — Mark Byers, Jul 07 '12 at 19:53
are the primary keys the same in both? if yes you can merge them — nischayn22, Jul 07 '12 at 19:55
They will be used for employer to log on to system and set them for different permission — user1509201, Jul 07 '12 at 19:55
Can you (just for our benefit) change all of the obscure names to semantic names? i.e. change emp_u_id to employee_user_id or something? — Chris Trahey, Jul 07 '12 at 19:56
yes the primary key for both tales is same because they will be only one record for each employer i dont need to create a another id record — user1509201, Jul 07 '12 at 19:56

Chris Trahey · Accepted Answer · 2012-07-07T20:07:22.197

1

If we are advising toward normalization (always!), then I suggest you leave them as two tables, but change the nature of the permissions table to be a one-to-many instead of 1-1 like it appears to be now.

There should be one row for each employee in the employee table, and then one row per permission in the permissions table.

CREATE TABLE emp_u_p (
 emp_u_id INT NOT NULL DEFAULT 0,
 permission_name varchar
);

This is highly advantageous when you need to add permission types to your system; you will not need to refactor your schema. If you want to really go all the way, make a third table called "permissions", and then have a many-to-many, where your employee_permissions table is just two foreign keys: one for employee_id and one for permission_id

To select employees with a given permission:

SELECT emp_u.* 
FROM emp_u JOIN emp_u_p 
  ON emp_u.id = emp_u_p.emp_u_id 
  AND permission_name = "add_j_mgt";

edited Jul 07 '12 at 20:07

answered Jul 07 '12 at 19:59

Chris Trahey

18,202
1
42
55

so leave the tables as it is and just enter same data into these feilds e.g. 1, add_j_mgt, '1' – user1509201 Jul 07 '12 at 20:01
Well, kinda. The structure of your second table would change (to the DDL I showed here), and then yes, there would be 1 row PER permission in it. – Chris Trahey Jul 07 '12 at 20:03
I modified it a bit, since you do not need the enabled bit for this relationship... simply the existence of the row indicates that the permission is enabled (delete the row to remove the permission) – Chris Trahey Jul 07 '12 at 20:04
OK because I have same layout for my admin table i should then create one table and called it as permission and have they coulmn id, permission_name, who_is_user – user1509201 Jul 07 '12 at 20:06

score 0 · Answer 2 · answered Jul 07 '12 at 20:00

0

As your tables represent, first table conatin the basic info of employee and second one contains the permission right of that employee...I think, there should be relationship between two table as foreign relationship...you can use ID of employee as a foreign key in second table...It'll maintain the database property.....So, I would recommend you to use two table with foreign relationship......

answered Jul 07 '12 at 20:00

Akash KC

16,057
6
39
59

It should be noted that MyISAM does not support these constraints (which can be okay of your code handles it). You should use InnoDB to get MySQL to enforce relationships for you. – Chris Trahey Jul 07 '12 at 20:02
I already have a foreign key employer_id_fk is my key in emp_u_p table – user1509201 Jul 07 '12 at 20:03
@ctrahey thats right because i'm using MyISAM i can't enter constraints between each table – user1509201 Jul 07 '12 at 20:04
I assume, OP is working on MySQL 5.5 in which Innodb is the default storage engine ..... – Akash KC Jul 07 '12 at 20:11
I'm forcing my database to be set as ENGINE=MyISAM DEFAULT CHARSET=utf8; – user1509201 Jul 07 '12 at 20:13
Oh sorry...I just read the column name of the table and answer according to tag made..... – Akash KC Jul 07 '12 at 20:15

Mysql tables into one table

2 Answers2