Storellet Master Data Dictionary

Complete SQL Database Schema Reference - All Markets (VN, HK, TW)

📊 Document Purpose:
This master data dictionary provides comprehensive documentation for ALL tables available in the Storellet SQL database views. It covers all three markets (Vietnam, Hong Kong, Taiwan) with complete field definitions, data types, and market-specific differences.

⚠️ Critical Information:
All tables are MySQL READ-ONLY VIEWS generated from MongoDB collections via aggregation pipelines. Use these views for reporting, analytics, and data integration. Do not attempt to write to these views.

1. Table Name Cross-Reference
2. Table Relationships & Foreign Keys
3. Market-Specific Field Comparison
4. Table: user (user)
5. Table: user_favour (userFavour)
6. Table: user_point (userPoint)
7. Table: user_activity (userActivity)
8. Table: favour (favour)
9. Value Code Reference (ENUMs)
10. Common Query Patterns

1. Table Name Cross-Reference

📋 Important: The tables you receive may have different names in your database. Use this cross-reference to map them correctly.

Client Reference Name	SQL View Name (Actual)	Description	Markets
user	`user`	Customer account information	ALL
userFavour	`user_favour`	User coupon wallet (issued coupons)	ALL
userPoint	`user_point`	User point balances and expiry	ALL
userActivity	`user_activity`	Transaction history and point activities	ALL
favour	`favour`	Coupon type definitions	ALL

2. Table Relationships & Foreign Keys

🔗 Entity Relationship Diagram (ERD):
Understanding table relationships is critical for JOIN operations and data integrity.

┌─────────────────┐
│     favour      │
│   (favourId)    │
└────────┬────────┘
         │ favourId
         │
         ▼
┌─────────────────┐     userId     ┌─────────────────┐
│  user_favour    │───────────────▶│     user        │
│                 │◄───────────────│                 │
│  (userId,       │   userId       │    (id)         │
│   favourId,     │                │                 │
│   brandId)      │                │                 │
└────────┬────────┘                └────────┬────────┘
         │                                  │
         │ brandId                          │ userId
         │                                  │
         ▼                                  ▼
┌─────────────────┐              ┌─────────────────┐
│     brand       │              │  user_point     │
│    (brandId)    │              │  (userId)       │
└─────────────────┘              └────────┬────────┘
                                           │
                                           │ groupId
                                           ▼
                                   ┌─────────────────┐
                                   │  brand_group    │
                                   │    (id)         │
                                   └─────────────────┘

┌─────────────────┐
│  user_activity  │
│  (userId,       │◄─────┐
│  transactionId) │      │
└─────────────────┘      │ userId
         │               │
         │ transactionId │
         ▼               │
┌─────────────────┐      │
│   transaction   │──────┘
│   (id)          │
└─────────────────┘

Primary Keys

Table	Primary Key Field	Data Type	Notes
`user`	`id`	BIGINT	MongoDB _id converted to integer
`user_favour`	`id`	STRING	MongoDB ObjectId as string (24-char hex)
`user_point`	`id`	STRING	MongoDB ObjectId as string (24-char hex)
`user_activity`	`id`	STRING	MongoDB ObjectId as string (24-char hex)
`favour`	`id`	INT	Coupon type identifier (integer)

Foreign Key Relationships

From Table	From Field	To Table	To Field	Relationship Type
`user_favour`	`userId`	`user`	`id`	Many-to-One
`user_favour`	`favourId`	`favour`	`id`	Many-to-One
`user_favour`	`brandId`	`brand`	`id`	Many-to-One
`user_point`	`userId`	`user`	`id`	Many-to-One
`user_point`	`groupId`	`brand_group`	`id`	Many-to-One
`user_activity`	`userId`	`user`	`id`	Many-to-One
`user_activity`	`transactionId`	`transaction`	`id`	Many-to-One

3. Market-Specific Field Comparison

⚠️ Critical: Some fields exist only in specific markets. Always check market availability before using fields in queries.

Table	Field	VN	HK	TW	Notes
`user`	`countryCode`	✅	✅	✅	Always populated based on market
`user`	`area`	❌	✅	❌	HK=HK/MO segmentation by phone prefix
`user`	`memberId`	✅	✅	❌	Member card ID (PHHK uses this)
`user_favour`	`serialNo`	✅	✅	✅	Format differs: VC: vs DR: prefix
`user_activity`	`type`	✅	✅	✅	Activity types include: 13=Transaction Reverse, 14=Point Reverse
`user_point`	`point`	✅	✅	✅	Point amounts (currency differs by market)
`user_point`	`accTransactionAmount`	✅	✅	✅	VND/HKD/TWD amounts
`favour`	`brandId`	✅	✅	✅	Coupon brand association

4. Table: `user` (user)

Description: Contains customer account information, credentials, and profile data. This is the master customer table.
Source: MongoDB user collection via aggregation pipeline
Update Frequency: Real-time (as users register/update profiles)

Complete Schema

Field	Data Type	Nullable	Default	Description	Example (VN)	Example (HK)
`id`	BIGINT	No	-	Primary key (MongoDB _id as integer)	123456789	987654321
`email`	VARCHAR(255)	Yes	NULL	Email address (lowercase, trimmed, no newlines)	customer@gmail.com	user@example.com
`password`	VARCHAR(255)	Yes	NULL	Encrypted password (VN: HMAC-SHA1, HK/TW: BCrypt)	0x7c9e8f5a...	$2a$10$xyz...
`fbId`	VARCHAR(255)	Yes	NULL	Facebook user ID (social login)	10234567890123456	10234567890123456
`gmId`	VARCHAR(255)	Yes	NULL	Google user ID (social login)	123456789012345678901	123456789012345678901
`displayName`	VARCHAR(255)	Yes	NULL	Customer full name (cleaned, no newlines)	NGUYEN VAN A	CHAN TAI MAN
`gender`	INT	Yes	-1	Gender: -1=N/A, 0=Male, 1=Female	0	1
`birthday`	VARCHAR(8)	Yes	NULL	Birthday in YYYYMMDD format	19900115	19850320
`phone`	VARCHAR(50)	Yes	NULL	Phone number (market-specific format)	0912345678	91234567
`joinDate`	DATETIME	No	NOW()	Registration timestamp (market timezone)	2024-03-15 14:30:00	2024-03-15 15:30:00
`lastUpdateDate`	DATETIME	No	NOW()	Last profile update timestamp	2024-03-20 10:15:00	2024-03-20 11:15:00
`securityCode`	VARCHAR(50)	Yes	NULL	Security code for account verification	123456	789012
`receiveNews`	TINYINT	No	1	Email marketing subscription: 0=No, 1=Yes	1	0
`countryCode`	VARCHAR(10)	No	Market code	ISO country code (VN, HK, TW)	VN	HK
`lastLoginDate`	DATETIME	Yes	NULL	Last successful login timestamp	2024-03-20 09:00:00	2024-03-20 10:00:00
`shopCode`	VARCHAR(50)	Yes	NULL	Store where user registered	VN001	HK01
`status`	INT	No	1	Account status: 0=Inactive, 1=Active	1	1

Market-Specific Fields: user

Field	Markets	Details
`area`	HK only	Area code field (Hong Kong market specific)
`memberId`	VN HK	Member card ID (PHHK membership card number). Not used in TW.

5. Table: `user_favour` (userFavour)

Description: User coupon wallet. Contains all coupons issued to users (both used and unused).
Source: MongoDB user_favour collection via aggregation pipeline
Update Frequency: Real-time (as coupons are issued/redeemed)

Complete Schema

Field	Data Type	Nullable	Default	Description	Example
`id`	STRING	No	-	Primary key (MongoDB ObjectId as 24-char hex string)	507f1f77bcf86cd799439011
`userId`	BIGINT	No	-	Foreign key to user.id	123456789
`favourId`	INT	No	-	Foreign key to favour.id (coupon type)	1001
`brandId`	INT	No	-	Brand identifier (99=PHVN, 1=KFC, 2=PHD, etc.)	99
`serialNo`	TEXT	Yes	NULL	QR code serial number (format: VC:encrypted_data or DR:encrypted_data for HK)	VC:a1b2c3d4...
`used`	TINYINT	No	0	Redemption status: 0=Available, 1=Redeemed	0
`createDate`	DATETIME	No	NOW()	Coupon issue timestamp	2024-03-15 14:30:00
`expiryDate`	DATETIME	No	Calculated	Coupon expiration timestamp	2024-12-31 23:59:59
`useDate`	DATETIME	Yes	NULL	Redemption timestamp (NULL if not used)	2024-03-20 12:00:00
`shopCode`	VARCHAR(50)	Yes	NULL	Store where coupon was redeemed	VN001
`invoiceNo`	VARCHAR(100)	Yes	NULL	Invoice number from redemption transaction	INV-2024-03-20001
`source`	VARCHAR(50)	Yes	NULL	Coupon source (admin, system, promotion, etc.)	admin
`batchId`	VARCHAR(100)	Yes	NULL	Batch identifier for bulk-issued coupons	BATCH-2024-03

Market-Specific Fields: user_favour

Feature	VN	HK	TW	Details
QR Code Prefix	`VC:`	`VC:` or `DR:`	`VC:`	HK has dual QR system: VC (coupon) + DR (digital receipt)

6. Table: `user_point` (userPoint)

Description: User point balances with expiry tracking. Users can have multiple point records with different expiry dates.
Source: MongoDB user_point collection via aggregation pipeline
Update Frequency: Real-time (points earned/expired)

⚠️ Important: A user may have MULTIPLE records in this table with different expiryDate values. Always SUM across all active records to get total balance.

Complete Schema

Field	Data Type	Nullable	Default	Description	Example (VN)	Example (HK)
`id`	STRING	No	-	Primary key (MongoDB ObjectId as 24-char hex string)	507f1f77bcf86cd799439011	507f1f77bcf86cd799439012
`userId`	BIGINT	No	-	Foreign key to user.id	123456789	987654321
`groupId`	INT	No	-	Foreign key to brand_group.id (market grouping)	1	8
`point`	INT	No	0	Point balance for this expiry batch	1500	500
`startDate`	DATETIME	No	NOW()	Points validity start date	2024-03-15 00:00:00	2024-03-15 00:00:00
`expiryDate`	DATETIME	No	Calculated	Points expiration date (varies by market)	2026-04-15 23:59:59	2026-04-15 23:59:59
`receiveNews`	TINYINT	No	1	Marketing subscription: 0=No, 1=Yes	1	1
`accTransactionAmount`	DECIMAL(15,2)	Yes	0.00	Accumulated spending amount (market currency)	5000000.00	5000.00
`lastTransactionAmount`	DECIMAL(15,2)	Yes	NULL	Last transaction amount (market currency)	150000.00	150.00
`lastTransactionDate`	DATETIME	No	NOW()	Last transaction timestamp	2024-03-20 14:30:00	2024-03-20 15:30:00
`lastUpdateDate`	DATETIME	No	NOW()	Last record update timestamp	2024-03-20 14:30:00	2024-03-20 15:30:00
`active`	TINYINT	No	1	Record status: 1=Active, 0=Inactive	1	1
`upcomingExpiries`	VARCHAR(20)	Yes	NULL	JSON array of upcoming expiry dates	["2024-04-30","2024-05-31"]	["2024-04-30"]

Market-Specific Point Expiry Rules

Market	Point Expiry Period	Currency
VN	25 months from earning date	VND
HK	25 months from earning date	HKD
TW	15 months from earning date	TWD

7. Table: `user_activity` (userActivity)

Description: Transaction history and point activity log. Records all point earning and redemption events.
Source: MongoDB user_activity collection via aggregation pipeline
Update Frequency: Real-time (as transactions occur)

⚠️ Important: This is the PRIMARY table for transaction analytics. Links to transaction table via transactionId.

Complete Schema

Field	Data Type	Nullable	Default	Description	Example
`id`	STRING	No	-	Primary key (MongoDB ObjectId as 24-char hex string)	507f1f77bcf86cd799439011
`userId`	BIGINT	No	-	Foreign key to user.id	123456789
`transactionId`	STRING	Yes	NULL	Foreign key to transaction.id	507f1f77bcf86cd799439012
`brandId`	INT	Yes	NULL	Brand identifier	99
`groupId`	INT	Yes	NULL	Brand group identifier	1
`shopCode`	VARCHAR(50)	Yes	NULL	Store code	VN001
`activityType`	INT	No	-	Activity type: 1=Earn, 2=Redeem, 3=Expire, 4=Adjust, 5=Reverse	1
`point`	INT	No	0	Point amount (positive for earn, negative for redeem)	150
`balance`	INT	Yes	NULL	Running balance after this activity	1650
`transactionDate`	DATETIME	No	NOW()	Activity timestamp	2024-03-15 14:30:00
`createDate`	DATETIME	No	NOW()	Record creation timestamp	2024-03-15 14:30:00
`invoiceNo`	VARCHAR(100)	Yes	NULL	Invoice number (if applicable)	INV-2024-03-15001
`amount`	DECIMAL(15,2)	Yes	NULL	Transaction amount (market currency)	150000.00
`favourId`	INT	Yes	NULL	Coupon ID (if activity is coupon redemption)	1001
`description`	VARCHAR(500)	Yes	NULL	Activity description (human-readable)	Point earning from transaction
`expiryDate`	DATETIME	Yes	NULL	Point expiry date (for earning activities)	2026-04-15 23:59:59

Market-Specific Fields: user_activity

Field	Markets	Details

Activity Type Reference

activityType	Name	Description	Point Sign
`1`	Earn	Points earned from transaction	Positive (+)
`2`	Redeem	Points redeemed for coupon/reward	Negative (-)
`3`	Expire	Points expired	Negative (-)
`4`	Adjust	Manual adjustment by admin	±
`5`	Reverse	Transaction reversed/refunded	Negative (-)
`13`	Transaction Reverse	Transaction reversal (generic)	Negative (-)
`14`	Point Reverse	Point reversal only	Negative (-)

8. Table: `favour` (favour)

Description: Coupon type definitions (master coupon catalog). Defines coupon templates before they're issued to users.
Source: MongoDB favour collection via aggregation pipeline
Update Frequency: As new coupon types are created (by CMS/admin)

⚠️ Important: This table defines coupon TYPES. Individual coupons issued to users are in user_favour. Join these tables to get complete coupon information.

Complete Schema

Field	Data Type	Nullable	Default	Description	Example
`id`	INT	No	-	Primary key (coupon type identifier)	1001
`brandId`	INT	No	-	Brand identifier (99=PHVN, 1=KFC, 2=PHD, etc.)	99
`name`	VARCHAR(255)	No	-	Coupon name/title	50% Off Pizza
`nameTc`	VARCHAR(255)	Yes	NULL	Coupon name in Traditional Chinese (HK)	半價批薩
`description`	TEXT	Yes	NULL	Coupon description	Get 50% off on any large pizza
`pointCost`	INT	Yes	NULL	Points required to redeem (NULL = not point-based)	500
`cashValue`	DECIMAL(15,2)	Yes	NULL	Cash equivalent value (market currency)	50.00
`validDays`	INT	Yes	NULL	Validity period in days after issue	30
`startDate`	DATETIME	Yes	NULL	Coupon available from date	2024-03-01 00:00:00
`endDate`	DATETIME	Yes	NULL	Coupon available until date	2024-12-31 23:59:59
`status`	INT	No	1	Status: 0=Inactive, 1=Active	1
`type`	INT	Yes	NULL	Coupon type category	1
`priority`	INT	Yes	0	Display priority (higher = shown first)	100
`image`	VARCHAR(500)	Yes	NULL	Coupon image URL	https://cdn.storellet.com/coupons/1001.png
`tnc`	TEXT	Yes	NULL	Terms and conditions	Valid for dine-in only. Not combinable...
`createDate`	DATETIME	No	NOW()	Record creation timestamp	2024-02-01 10:00:00
`lastUpdateDate`	DATETIME	No	NOW()	Last update timestamp	2024-02-15 14:30:00
`maxIssue`	INT	Yes	NULL	Maximum times this coupon can be issued (NULL = unlimited)	10000
`maxIssuePerUser`	INT	Yes	NULL	Maximum times one user can receive this coupon	1
`requiredMemberTier`	VARCHAR(50)	Yes	NULL	Required member tier (e.g., "GOLD")	SILVER

9. Value Code Reference (ENUMs)

Reference: Common value codes used across multiple tables

User Status Codes

Value	Status	Description
`0`	Inactive	Account deactivated or not verified
`1`	Active	Account in good standing

Gender Codes

Value	Gender
`-1`	N/A (Not specified)
`0`	Male
`1`	Female

Coupon Usage Status (userFavour.used)

Value	Status	Description
`0`	Available	Coupon issued but not yet redeemed
`1`	Redeemed	Coupon has been used

Brand Identifiers

⚠️ Important: Brand IDs vary by market deployment. The mappings below are examples from specific markets. Always verify brand IDs from the actual deployment configuration.

Market	Brand	brandId (Example)
Hong Kong	KFC	`1`
Hong Kong	Pizza Hut Delivery (PHD)	`2`
Hong Kong	Pizza Hut (PH)	`Varies by deployment`
Vietnam	Pizza Hut Vietnam	`See VN deployment configs`
Taiwan	Pizza Hut (PH)	`See TW deployment configs`
Taiwan	KFC	`See TW deployment configs`

Country Codes

Code	Country	Timezone	Currency
`VN`	Vietnam	GMT+7	VND
`HK`	Hong Kong	GMT+8	HKD
`TW`	Taiwan	GMT+8	TWD

10. Common Query Patterns

User with Points (Complete Balance)

SELECT
  u.id,
  u.displayName,
  u.email,
  u.phone,
  u.countryCode,
  SUM(up.point) as totalPoints,
  SUM(up.accTransactionAmount) as totalSpent,
  COUNT(DISTINCT ua.id) as totalTransactions
FROM user u
INNER JOIN user_point up ON u.id = up.userId
LEFT JOIN user_activity ua ON u.id = ua.userId
WHERE u.status = 1
  AND up.active = 1
  AND up.expiryDate > NOW()
GROUP BY u.id, u.displayName, u.email, u.phone, u.countryCode
ORDER BY totalPoints DESC;

User with Available Coupons

SELECT
  u.id,
  u.displayName,
  u.phone,
  u.countryCode,
  COUNT(CASE WHEN uf.used = 0 AND uf.expiryDate > NOW() THEN 1 END) as activeCoupons,
  SUM(CASE WHEN uf.used = 0 AND uf.expiryDate > NOW() THEN f.cashValue ELSE 0 END) as totalCouponValue
FROM user u
INNER JOIN user_favour uf ON u.id = uf.userId
INNER JOIN favour f ON uf.favourId = f.id
WHERE u.status = 1
GROUP BY u.id, u.displayName, u.phone, u.countryCode
HAVING activeCoupons > 0
ORDER BY activeCoupons DESC;

Coupon Redemption Report

SELECT
  f.name as couponName,
  f.brandId,
  u.countryCode,
  COUNT(uf.id) as totalIssued,
  SUM(CASE WHEN uf.used = 1 THEN 1 ELSE 0 END) as totalRedeemed,
  SUM(CASE WHEN uf.used = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(uf.id) as redemptionRate,
  SUM(CASE WHEN uf.used = 0 AND uf.expiryDate > NOW() THEN 1 ELSE 0 END) as activeCoupons,
  SUM(CASE WHEN uf.used = 0 AND uf.expiryDate <= NOW() THEN 1 ELSE 0 END) as expiredCoupons
FROM favour f
INNER JOIN user_favour uf ON f.id = uf.favourId
INNER JOIN user u ON uf.userId = u.id
WHERE f.createDate >= '2024-01-01'
GROUP BY f.name, f.brandId, u.countryCode
ORDER BY totalIssued DESC;

Point Expiry Report (Next 30 Days)

SELECT
  u.id,
  u.displayName,
  u.email,
  u.phone,
  u.countryCode,
  up.point,
  up.expiryDate,
  DATEDIFF(up.expiryDate, NOW()) as daysUntilExpiry
FROM user u
INNER JOIN user_point up ON u.id = up.userId
WHERE u.status = 1
  AND up.active = 1
  AND up.expiryDate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)
ORDER BY up.expiryDate ASC, up.point DESC;

Transaction History by User

SELECT
  u.id,
  u.displayName,
  ua.transactionDate,
  ua.activityType,
  ua.point,
  ua.balance,
  ua.description,
  ua.invoiceNo
FROM user u
INNER JOIN user_activity ua ON u.id = ua.userId
WHERE u.id = 123456789
ORDER BY ua.transactionDate DESC;

Market Comparison (Active Users by Country)

SELECT
  u.countryCode,
  COUNT(DISTINCT u.id) as totalUsers,
  COUNT(DISTINCT CASE WHEN ua.activityType = 1 THEN u.id END) as activeEarners,
  COUNT(DISTINCT CASE WHEN ua.activityType = 2 THEN u.id END) as activeRedeemers,
  SUM(COALESCE(up.point, 0)) as totalPointsIssued
FROM user u
LEFT JOIN user_activity ua ON u.id = ua.userId
  AND ua.transactionDate >= DATE_SUB(NOW(), INTERVAL 30 DAY)
LEFT JOIN user_point up ON u.id = up.userId
  AND up.active = 1
  AND up.expiryDate > NOW()
WHERE u.status = 1
GROUP BY u.countryCode
ORDER BY totalUsers DESC;

Document Version: 1.0 | Last Updated: March 2025
Markets Covered: Vietnam (VN), Hong Kong (HK), Taiwan (TW)
Database: MySQL 8.0+ (Read-Only Views from MongoDB)
Tables Documented: user, userFavour, userPoint, userActivity, favour

Storellet Master Data Dictionary

Table of Contents

1. Table Name Cross-Reference

2. Table Relationships & Foreign Keys

Primary Keys

Foreign Key Relationships

3. Market-Specific Field Comparison

4. Table: user (user)

Complete Schema

Market-Specific Fields: user

5. Table: user_favour (userFavour)

Complete Schema

Market-Specific Fields: user_favour

6. Table: user_point (userPoint)

Complete Schema

Market-Specific Point Expiry Rules

7. Table: user_activity (userActivity)

Complete Schema

Market-Specific Fields: user_activity

Activity Type Reference

8. Table: favour (favour)

Complete Schema

9. Value Code Reference (ENUMs)

User Status Codes

Gender Codes

Coupon Usage Status (userFavour.used)

Brand Identifiers

Country Codes

10. Common Query Patterns

User with Points (Complete Balance)

User with Available Coupons

Coupon Redemption Report

Point Expiry Report (Next 30 Days)

Transaction History by User

Market Comparison (Active Users by Country)

4. Table: `user` (user)

5. Table: `user_favour` (userFavour)

6. Table: `user_point` (userPoint)

7. Table: `user_activity` (userActivity)

8. Table: `favour` (favour)