I've been setting a Orders system for a Laravel 5.8 app but I presume I've overcomplicated (or not) the DB architecture for the Orders system.
I created the following tables: order, order_items, products, services, prices and of course using the users table as 'client'.
My doubt now is between having the products and services as separate tables or use just on new as items which would state if the item is a 'product' or 'service'.
This because when establishing the Models relationships for the Order::class and OrderItem::class models I seem to have gotten into a 'knot'.
My tables are the following:
orders
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| guid | varchar(191) | NO | | NULL | |
| user_id | int(10) unsigned | YES | MUL | NULL | |
| firm_id | bigint(20) unsigned | YES | MUL | NULL | |
| currency_id | int(10) unsigned | YES | MUL | 1 | |
| total | double(8,2) | NO | | 0.00 | |
| status | varchar(191) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
order_items
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| product_id | int(10) unsigned | YES | MUL | NULL | |
| service_id | bigint(20) unsigned | YES | MUL | NULL | |
| order_id | bigint(20) unsigned | NO | MUL | NULL | |
| quantity | double(8,2) | NO | | 1.00 | |
| quantity_type | varchar(191) | YES | | NULL | |
| notes | varchar(191) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+---------------+---------------------+------+-----+---------+----------------+
products
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| guid | varchar(191) | NO | UNI | NULL | |
| name | varchar(191) | NO | | NULL | |
| name_slug | varchar(191) | NO | | NULL | |
| details | text | NO | | NULL | |
| has_shipment | tinyint(1) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+--------------+---------------------+------+-----+---------+----------------+
services
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| guid | varchar(191) | NO | UNI | NULL | |
| name | varchar(191) | NO | | NULL | |
| name_slug | varchar(191) | NO | | NULL | |
| description | text | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
prices
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_id | int(11) | YES | MUL | NULL | |
| service_id | bigint(20) unsigned | YES | MUL | NULL | |
| currency_id | int(10) unsigned | YES | MUL | NULL | |
| value | double(8,2) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
users (simplified)
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| guid | varchar(191) | NO | UNI | NULL | |
| name | varchar(191) | NO | | NULL | |
| name_slug | varchar(191) | NO | | NULL | |
| email | varchar(191) | NO | | NULL | |
| ... | ... | ... | ... | ... | ... |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| deleted_at | timestamp | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
My goal is to have the Order with one or more Products and/or Services, with their respective prices for a user.
Example:
$order = Order::where('id', 1)->with('users', 'items', 'prices')->get();
But I can fetch the 'Order', with the 'User', 'Items' but not the 'Prices' for each item.
What I've tried
Different relationship approaches on the Order::class and OrderItem::class models (ex: $this->belongsTo(Product::class)) but seems to not getting it right.
As mentioned above I can get almost everything but not the prices.
Question: Should I merge the Products and Services into one table and have a column for the type (like service, product)?
Thanks in advance for any suggestion here.
I think that merge these tables not is the best way. In my opinion, this is a perfect usage for a polymorphic relationship. According to the Laravel's documentation:
A polymorphic relationship allows the target model to belong to more than one type of model using a single association.
Source: https://laravel.com/docs/5.8/eloquent-relationships#polymorphic-relationships
Look at https://laravel.com/docs/5.8/eloquent-relationships#many-to-many-polymorphic-relations. If you have some question, please, comment here. I'll be happy to help you.