title: Laravel DB design: how to model different types of similar entity
body: <p>First of all I have to mention that I am modernising our ERP system that is build in-house. It handles everything from purchasing of parts, sale orders, quotes and inventory to invoicing and statistical data. The system is web based and heavily dependent on ORM. EloquentORM will be used in the redesign.</p>
  <p>My main question is about the data model of certain entities that are very similar. Currently three of most widely interconnected entities in the app are: <strong>Orders, Products and Invoices</strong>.</p>
  <h3>1. Orders</h3>
  <p>In current DB design I have one big <code>orders</code> table in which there is a <code>order_type</code> attribute to distinct between different order types: Purchase orders, Sale orders, Quotes and Service orders. About 80% of fields are common to each order type and there are some specific fields for each order types. <em>Currently at ~15k records.</em></p>
  <h3>2. Products</h3>
  <p>Similarly I have one big <code>products</code> table with an attribute <code>product_type</code> to distinct between different product types: Finished products, Services, Assemblies and Parts. Again there is a fair % of fields that are common throughout all product types and some that are specific to different product type <em>Currently at ~7k records.</em></p>
  <h3>3. Invoices</h3>
  <p>Again one table <code>invoices</code> with <code>invoice_type</code> attribute to distinct between 4 invoice types: Issued invoices (for things we sell), Received invoices (for things we buy), Credit notes and Avans Invoices. More or less all invoice types use the same fields. <em>Currently at ~15k records.</em></p>
  <p>I am now trying to decide which is the optimal way for this kind of DB model. I see three options:</p>
  <p><strong>1. Single Table Inheritance</strong></p>
  <p>Leave as is, everything in the same table. It feels kind of awkward to always filter records like <code>where order_type = 'Sale order'</code> to display right orders in the right place in GUI... Also when doing sale and purchase analytics I need to include the same where condition to fetch right orders. Seems wrong!</p>
  <p><strong>2. Class Table Inheritance</strong></p>
  <p>Have a master tables <code>orders</code>, <code>products</code> and <code>invoices</code> with common set of fields between each of entity types and then <code>one-to-one</code> child relation tables for every different type of each entity: <code>sales_orders</code>, <code>purchase_orders</code>, <code>quote_orders</code>, <code>finished_products</code>, <code>reseller_products</code>, <code>part_products</code>, <code>assembly_products</code>, <code>received_invoices</code> and <code>issued_invoices</code> with FK in each of the child tables to master table... This seems like a good idea but handling that with ORM brings in a little more complexity...</p>
  <p>In this method I have a questions which FK should be used around. For example each invoice can belong to one order. Received invoice will go with Purchase order and issued invoice will go with Sale order. Should the master <code>orders</code> table's PK be used as a FK in the master <code>invoices</code> table to relate these entities, or should the child <code>sale_orders</code> PK be used in the child <code>issued_invoices</code>?</p>
  <p><strong>3. Concrete Table Inheritance</strong></p>
  <p>Having completely separated tables for every type of each entity. This would avoid me having parent-&gt;child relationship between master table but would result in a lot of similar attributes in each table...</p>
  <p>What would be the best approach? I am aiming at ease of use in EloquentORM and also speed and scalability for the future.</p>