Database Schema
* Merchant
* Customer
* Order
* Product
We have two databases, a Postgres database, and a legacy MySQL database.
Postgres address:
cfbi.cmh8ezndqrcb.us-east-2.rds.amazonaws.com
the master username is postgres.
MySQL address:
database-1.cmh8ezndqrcb.us-east-2.rds.amazonaws.com
the main username is admin.
the basic layout of our current main tables:
USERS:
+--------------------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| f_name | varchar(100) | YES | | NULL | |
| l_name | varchar(100) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| image | varchar(100) | YES | | NULL | |
| is_phone_verified | tinyint(1) | NO | | 0 | |
| email_verified_at | timestamp | YES | | NULL | |
| password | varchar(100) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| email_verification_token | varchar(255) | YES | | NULL | |
| phone | varchar(255) | YES | | NULL | |
| cm_firebase_token | varchar(255) | YES | | NULL | |
| point | decimal(8,2) | NO | | 0.00 | |
+--------------------------+-----------------+------+-----+---------+----------------+
PRODUCTS:
+-----------------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| description | text | YES | | NULL | |
| image | text | YES | | NULL | |
| price | decimal(8,2) | NO | | 0.00 | |
| variations | text | YES | | NULL | |
| add_ons | varchar(255) | YES | | NULL | |
| tax | decimal(8,2) | NO | | 0.00 | |
| available_time_starts | time | YES | | NULL | |
| available_time_ends | time | YES | | NULL | |
| status | tinyint(1) | NO | | 1 | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| attributes | varchar(255) | YES | | NULL | |
| category_ids | varchar(255) | YES | | NULL | |
| choice_options | text | YES | | NULL | |
| discount | decimal(8,2) | NO | | 0.00 | |
| discount_type | varchar(20) | NO | | percent | |
| tax_type | varchar(20) | NO | | percent | |
| set_menu | tinyint(1) | NO | | 0 | |
| branch_id | bigint | NO | | 1 | |
| colors | text | YES | | NULL | |
+-----------------------+-----------------+------+-----+---------+----------------+
ORDERS:
+------------------------+-----------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------+------+-----+----------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint | YES | | NULL | |
| order_amount | decimal(8,2) | NO | | 0.00 | |
| coupon_discount_amount | decimal(8,2) | NO | | 0.00 | |
| coupon_discount_title | varchar(255) | YES | | NULL | |
| payment_status | varchar(255) | NO | | unpaid | |
| order_status | varchar(255) | NO | | pending | |
| total_tax_amount | decimal(8,2) | NO | | 0.00 | |
| payment_method | varchar(30) | YES | | NULL | |
| transaction_reference | varchar(30) | YES | | NULL | |
| delivery_address_id | bigint | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
| checked | tinyint(1) | NO | | 0 | |
| delivery_man_id | bigint | YES | | NULL | |
| delivery_charge | decimal(8,2) | NO | | 0.00 | |
| order_note | text | YES | | NULL | |
| coupon_code | varchar(255) | YES | | NULL | |
| order_type | varchar(255) | NO | | delivery | |
| branch_id | bigint | NO | | 1 | |
| callback | varchar(255) | YES | | NULL | |
| delivery_date | date | YES | | NULL | |
| delivery_time | varchar(255) | YES | | NULL | |
+------------------------+-----------------+------+-----+----------+----------------+
ADMINS:
+----------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------+------+-----+---------+----------------+
| id | bigint unsigned | NO | PRI | NULL | auto_increment |
| f_name | varchar(100) | YES | | NULL | |
| l_name | varchar(100) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| email | varchar(100) | NO | UNI | NULL | |
| image | varchar(100) | YES | | NULL | |
| password | varchar(100) | NO | | NULL | |
| remember_token | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+----------------+-----------------+------+-----+---------+----------------+