What is the purpose of this article?
In this article contains all the data models required to achieve customer page requirement
Database: eshopbox_wms_dev
Table Name: customers
Field | Datatype | Key | |
|---|---|---|---|
id | BIGINT(20) | Primary | |
brand_account_id | BIGINT(20) | ||
name | VARCHAR(200) |
| |
phone | VARCHAR(20) |
| |
VARCHAR(255) |
| ||
status | ENUM('ACTIVE', ‘ARCHIVED’, 'SUPPRESSED') |
| |
whatsappStatus | ENUM('ACTIVE', ‘INACTIVE', 'UNSUBSCRIBED’) |
| |
customerAdditionalInfo | TEXT |
| |
orderDetails | TEXT | ||
createdBy | VARCHAR(200) | ||
updatedBy | VARCHAR(200) | ||
origin | VARCHAR(255) | ||
createdAt | timestamp |
| |
updateAt | timestamp |
|
SQL Query to create 'customers' table
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
brand_account_id BIGINT(20),
name VARCHAR(255),
phone VARCHAR(20),
email VARCHAR(255),
status ENUM('ACTIVE', 'SUPPRESSED', 'ARCHIVED'),
whatsappStatus ENUM('ACTIVE', 'INACTIVE', 'UNSUBSCRIBED'),
customerAdditionalInfo TEXT,
orderDetails TEXT,
createdBy VARCHAR(255),
updatedBy VARCHAR(255),
origin VARCHAR(255),
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (brand_account_id) REFERENCES accounts(id)
);
Database: eshopbox_wms_dev
Table Name: addresses
Field | Data Type | Key |
|---|---|---|
id | BIGINT(20) | PRIMARY |
customer_id | BIGINT(20) | FOREING KEY |
address | TEXT |
|
city | VARCHAR(100) |
|
pincode | VARCHAR(10) |
|
state | VARCHAR(100) |
|
country | VARCHAR(100) |
|
landmark | TEXT |
|
createdAt | timestamp |
|
updatedAt | timestamp |
|
SQL Query to create ‘addresses’ table
CREATE TABLE addresses (
id BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
customer_id BIGINT(20),
address TEXT,
city VARCHAR(100),
pincode VARCHAR(10),
state VARCHAR(100),
country VARCHAR(100),
landmark TEXT,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);