Mysql shared columns to reduce repetition
<p>I am trying to understand the best way to structure relational database tables which have many shared columns, but also a few extra columns. <a href="https://laracasts.com/discuss/channels/general-discussion/insert-record-in-supertypesubtype-tables" rel="nofollow">Similar to this question on Laracasts</a></p>

<p>For example, lets say I have a user table with the following columns:</p>

<pre><code>|---------|
|  User   |
|---------|
|Name     |
|Email    |
|Password |
|DoB      |
|Address  |
|Etc...   |
|---------|
</code></pre>

<p>If I had an Employee table, this would be a user and thus have all of the attributes in the User table, but may also have extra columns such as <code>employee_id</code>, <code>Payroll_number</code>, <code>Department_id</code>, <code>Start_date</code>, <code>End_date</code>, etc...</p>

<p>A Customer would also have all of the user attributes, but would also need a <code>customer_id</code>, and <code>card_number</code>, etc...</p>

<p>I have thought about a number of ways to do this:</p>

<ol>
<li><p>My first thought was to to just duplicate the columns in each table, so that employee and customer has all of the attributes a user would have. However, I don't think this is a very good approach, as it means duplicating the fields for each table and may also cause some issues when trying to authenticate a user against multiple tables.</p></li>
<li><p>My second approach was to keep all users in one table, with an extra attribute named <code>roles</code>, which has a many to many relationship with a userTraits table which would act like a key => value (i.e. <code>trait_id</code>, <code>trait_name</code>, <code>trait_value</code>). However, I'm worried that there is no restrictions at the database level to prevent a user from having a trait which shouldn't belong to that type of user, (i.e. a customer being assigned a <code>payroll_id</code>)</p></li>
<li><p>My third approach was to just have all users in the same table, but add all of the fields to all of the users and make most of them null-able, however, this has the same issues as my second approach, and wouldn't be very efficient.</p></li>
<li><p>My fourth approach was to try and use Laravels polymorphic relations, but this seems to give me the opposite of what I'm trying to achieve. As far as my understanding of this goes, it seems like this is used when you are trying to have several versions of the same type of data, so giving the Laravel documentation's example, having a <code>post like</code> and <code>comment like</code> share a <code>likes</code> table, by giving an id, likeable_id and likeable_type. However, if these two types of <code>likes</code> had extra attributes, then this would not work.</p></li>
</ol>

<p>Unfortunately I cannot seem to find a design pattern that solves this issue and was wondering which way would be best to go about it? In the actual PHP implementation, I simply have a base class of User, which has subclasses of Employee, Customer, allowing them to inherit all of the functionality of User, but I cannot seem to find a way of achieving this kind of relationship at the database level.</p>

<p>Any help would be greatly appreciated.</p>