Data Model For Customers
- Rohit Kumar
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 |
| |
|---|---|---|---|---|
| 1 | id | BIGINT(20) | Primary |
|
| 2 | brand_account_id | BIGINT(20) |
|
|
| 3 | name | VARCHAR(200) |
|
|
| 4 | phone | VARCHAR(20) |
|
|
| 5 | VARCHAR(255) |
|
| |
| 6 | status | ENUM('ACTIVE', ‘ARCHIVED’, 'SUPPRESSED') |
|
|
| 7 | whatsappStatus | ENUM('ACTIVE', ‘INACTIVE', 'UNSUBSCRIBED’) |
|
|
| 8 | customerAdditionalInfo | TEXT |
|
|
| 9 | orderDetails | TEXT |
|
|
| 10 | createdBy | VARCHAR(200) |
|
|
| 11 | updatedBy | VARCHAR(200) |
|
|
| 12 | origin | VARCHAR(255) |
|
|
| 13 | createdAt | timestamp |
|
|
| 14 | 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)
);