Business Context and Challenges

As the company expanded its overseas operations, an e-commerce platform was initially developed to serve international customers. Over time, this system accumulated significant user data. Later, with the launch of smart robot products, a mobile application was introduced to allow users to control their devices, also building up a separate user base. The organization now seeks to unify both platforms by integrating the web store functionality into the mobile app, requiring a consolidated user management system. A critical requirement is performing this integration without service interruption or downtime.

Unified User Database Schema Design

Web Platform User Table Structure (Key Fields)

Index	Field Name	Type	Description
1	id	BigInt	Primary identifier
2	email	String	Optional login credential
3	user_login	String	Required login name
4	...	...	Additional attributes

Mobile App User Table Structure (Key Fields)

Index	Field Name	Type	Description
1	id	BigInt	Unique identifier
2	email	String	Contact email address
3	app_version	String	Application version info
4	os	String	Operating system type
5	last_login_time	Time	Last access timestamp
6	......	...	Other relveant fields

Unified User Center Table Design (Key Fields)

Index	Field Name	Type	Description
1	id	BigInt	Internal primary key
2	gw_user_id	BigInt	Reference to website user ID
3	app_user_id	BigInt	Reference to mobile app user ID
4	user_login	String	Login username
5	email	String	Email address
6	app_version	String	App version from mobile client
7	os	String	Device operating system
8	last_login_time	Time	Most recent activity timestamp
9	row_key	String	Unique hash-based identifier
10	......	...	Additional unified properties

Continuous Data Replication via Flink CDC

Streaming Job for Website Users

CREATE TABLE source_web_users (
  user_id BIGINT,
  account_name STRING,
  encrypted_password STRING,
  contact_email STRING,
  PRIMARY KEY (user_id) NOT ENFORCED
) COMMENT 'Web Portal User Source'
WITH (
  'connector' = 'mysql-cdc',
  'hostname' = 'xxx.xx.xxx.xx',
  'port' = 'xx',
  'username' = 'xx',
  'password' = 'xxx',
  'scan.startup.mode' = 'initial',
  'database-name' = 'web_portal',
  'table-name' = 'users'
);

CREATE TABLE target_unified_users (
  login_name STRING,
  email_address STRING,
  pwd_hash STRING,
  unique_row_id STRING,
  PRIMARY KEY (unique_row_id) NOT ENFORCED
) COMMENT 'Centralized User Sink'
WITH (
  'connector' = 'jdbc',
  'url' = 'jdbc:mysql://target.db.server:3306/unified_db?useUnicode=true&characterEncoding=UTF-8',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'username' = 'db_user',
  'password' = 'secure_password',
  'table-name' = 'central_users'
);

INSERT INTO target_unified_users
SELECT 
  account_name AS login_name,
  contact_email AS email_address,
  encrypted_password AS pwd_hash,
  MD5(CONCAT(account_name, '|', contact_email, '|WEB')) AS unique_row_id
FROM source_web_users;

Streaming Job for Mobile Application Users

CREATE TABLE source_mobile_users (
  mobile_user_id BIGINT,
  registered_email STRING,
  hashed_pass STRING,
  client_os STRING,
  app_build STRING,
  last_seen TIMESTAMP(3),
  PRIMARY KEY (mobile_user_id) NOT ENFORCED
) COMMENT 'Mobile Client User Source'
WITH (
  'connector' = 'mysql-cdc',
  'hostname' = 'yyy.yy.yyy.yy',
  'port' = 'yy',
  'username' = 'yy',
  'password' = 'yyy',
  'scan.startup.mode' = 'initial',
  'database-name' = 'robot_app',
  'table-name' = 'app_users'
);

-- Reusing same sink table as above
INSERT INTO target_unified_users
SELECT 
  NULL AS login_name,
  registered_email AS email_address,
  hashed_pass AS pwd_hash,
  MD5(CONCAT(mobile_user_id, '|', registered_email, '|MOBILE')) AS unique_row_id
FROM source_mobile_users;

The described approach ensures continuous synchronization between legacy systems and the new centralized repository using real-time change capture mechanisms. While these SQL definitions establish foundational data pipelines, further business logic adjustments will be necessary within each individual subsystem.