Databases
StackOverflow2013
Postsdbo
POShould I create a parent table for shared attributes?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POShould I create a parent table for shared attributes?
    text
    copied!<p>I am designing the database (MySQL) in which I have two tables Employees and Guests as following :</p> <pre><code>CREATE TABLE employee ( `EMP_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, `FIRST_NAME` VARCHAR(8) NOT NULL, `MID_NAME` VARCHAR(11), `LAST_NAME` VARCHAR(8) NOT NULL, `BIRTHDAY` DATE, `COUNTRY_ID` INT, `NAT_ID` VARCHAR(8) NOT NULL, `ID_EXP_DATE` DATE, `ID_TYPE` VARCHAR(8) NOT NULL, `Mobile` VARCHAR(8) NOT NULL, `PHONE` VARCHAR(8) NOT NULL, `EMAIL` VARCHAR(27) NOT NULL, `DEPT_ID` TINYINT NOT NULL references DEPARTMENT (ID), `POSITION` VARCHAR(20), `EMP_TYPE` TINYINT NOT NULL references EMP_TYPES (type), `JOINDATE` DATE, `SALARY` MEDIUMINT DEFAULT 0 , `WORKEMAIL` VARCHAR(30), `MARITALSTAT` VARCHAR(7), `EMERGCONTACT` VARCHAR(22), `EMERG_CONT_PHN` VARCHAR(11), `GENDER` VARCHAR(6), `RESUMEURL` VARCHAR(60), `RELIGION` VARCHAR(11), PRIMARY KEY (`EMP_ID`)); </code></pre> <hr> <pre><code>CREATE TABLE Guest ( `guest_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, `FIRST_NAME` VARCHAR(8) NOT NULL, `MID_NAME` VARCHAR(11), `LAST_NAME` VARCHAR(8) NOT NULL, `BIRTHDAY` DATE, `COUNTRY_ID` INT, `NAT_ID` VARCHAR(8) NOT NULL, `ID_EXP_DATE` DATE, `ID_TYPE` VARCHAR(8) NOT NULL, `Mobile` VARCHAR(8) NOT NULL, `PHONE` VARCHAR(8) NOT NULL, `EMAIL` VARCHAR(27) NOT NULL, `WORKEMAIL` VARCHAR(30), `MARITALSTAT` VARCHAR(7), `EMERGCONTACT` VARCHAR(22), `EMERG_CONT_PHN` VARCHAR(11), `GENDER` VARCHAR(6), `RELIGION` VARCHAR(11), ..................................// More attributes specific for guest table PRIMARY KEY (`Guest_ID`)); </code></pre> <hr> <p>Since both of the tables have auto generated primary keys and it would not be appropriate if they share primary key from person. would it be a good idea to create a table named person with all the common attributes and two child tables as Employee and Guest? and what would be the best practice to implement this?</p> <p>Thank You, </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload