AI智慧旅游项目

数据范式设计

设定要求您是一名专业的数据库工程师，熟悉PostgreSQL和ParseServer。请注意表名用大驼峰，字段用小驼峰。有预留字段：objectId、updatedAt、createdAt。关于ParseServer中数据类的描述，字段的主要类型有： String => String Number => Number Bool => bool Array => JSON Array Object => JSON Object Date => Date File => Parse.File Pointer => other Parse.Object Relation => Parse.Relation Null => null GeoPoint => {latitude: 40.0, longitude: -30.0}

项目需求智能旅游的辅助AI应用，游客/旅行者（_User）、旅游资源（景点、酒店等）、行程提醒与预警、旅行日志与回忆管理、实时客流/天气/交通数据监测、安全风险预警，请您根据旅游外出的行业经验，设计以上几张表，游客/旅行者直接用预留的_User表即可。

输出结果（UML类图）请您帮我用plantuml的类图描述设计好的几张表及关系

输出结果（信息结构图）请您帮我用markmap格式表示上面的信息结构图

输出结果（SQL语句）请您帮我用sql格式给我建表语句和测试数据插入语句，注意字段请使用小驼峰用""引起来。

UML类图

@startuml
' 设置类图方向
left to right direction

' 定义基本样式
skinparam groupInheritance 2
skinparam class {
    BackgroundColor White
    ArrowColor #0078d7
    BorderColor #2e2e2e
}

' 用户表（使用Parse内置_User类）
class _User << (P,#FF7700) Parse内置 >> {
    + objectId: String
    + username: String
    + email: String
    + createdAt: Date
    + updatedAt: Date
    + phone: String
    + profilePic: File
    + emergencyContact: String
}

' 旅游资源表
class TouristResource {
    + objectId: String
    + createdAt: Date
    + updatedAt: Date
    + name: String
    + type: String "景点/酒店/餐厅"
    + description: String
    + location: GeoPoint
    + openingHours: String
    + priceRange: String
    + images: File[]
    + rating: Number
    + capacity: Number "承载量"
}

' 行程提醒表
class Itinerary {
    + objectId: String
    + createdAt: Date
    + updatedAt: Date
    + title: String
    + startTime: Date
    + endTime: Date
    + reminderType: String "时间/位置"
    + location: GeoPoint "位置提醒"
    + notes: String
    + alertLevel: String "高/中/低"
    + isCompleted: Bool
}

' 旅行日志表
class TravelJournal {
    + objectId: String
    + createdAt: Date
    + updatedAt: Date
    + title: String
    + content: String
    + photos: File[]
    + location: GeoPoint
    + weatherData: Object "温度/湿度"
    + moodRating: Number
}

' 实时监测数据表
class RealTimeData {
    + objectId: String
    + createdAt: Date
    + updatedAt: Date
    + dataType: String "客流/天气/交通"
    + value: Object "动态JSON"
    + location: GeoPoint
    + source: String "官方/传感器"
    + validity: Date "有效期"
}

' 安全预警表
class SafetyWarning {
    + objectId: String
    + createdAt: Date
    + updatedAt: Date
    + title: String
    + description: String
    + severity: String "紧急/高/中"
    + affectedArea: GeoPoint
    + validUntil: Date
    + relatedHazards: String[] "自然灾害/事故"
}

' 定义关系
_User "1" -- "n" Itinerary : "创建"
_User "1" -- "n" TravelJournal : "记录"
Itinerary "n" -- "1" TouristResource : "关联"
TravelJournal "n" -- "1" TouristResource : "提及"
RealTimeData "n" -- "1" TouristResource : "监测"
SafetyWarning "n" -- "n" TouristResource : "影响"
RealTimeData "1" -- "1" SafetyWarning : "触发"
Itinerary "1" -- "1" SafetyWarning : "关联预警"

@enduml

SQL语句

-- 创建扩展（用于PostGIS空间函数） CREATE EXTENSION IF NOT EXISTS postgis;

-- 1. 用户表扩展 (Parse内置)

ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "phone" VARCHAR(20); ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "profilePic" TEXT; ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "emergencyContact" VARCHAR(100);

-- 2. 旅游资源表

CREATE TABLE IF NOT EXISTS "TouristResource" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"name" VARCHAR(255) NOT NULL,
"type" VARCHAR(50) NOT NULL CHECK ("type" IN ('景点', '酒店', '餐厅')),
"description" TEXT,
"latitude" DOUBLE PRECISION NOT NULL,
"longitude" DOUBLE PRECISION NOT NULL,
"openingHours" VARCHAR(100),
"priceRange" VARCHAR(50),
"images" JSONB,
"rating" NUMERIC(3,1),
"capacity" INTEGER

);

-- 空间索引 CREATE INDEX IF NOT EXISTS "idx_tourist_resource_location" ON "TouristResource" USING GIST (ST_MakePoint("longitude", "latitude"));

-- 3. 行程提醒表

CREATE TABLE IF NOT EXISTS "Itinerary" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"title" VARCHAR(255) NOT NULL,
"startTime" TIMESTAMP NOT NULL,
"endTime" TIMESTAMP NOT NULL,
"reminderType" VARCHAR(20) NOT NULL CHECK ("reminderType" IN ('时间', '位置')),
"latitude" DOUBLE PRECISION,
"longitude" DOUBLE PRECISION,
"notes" TEXT,
"alertLevel" VARCHAR(10) CHECK ("alertLevel" IN ('高', '中', '低')),
"isCompleted" BOOLEAN DEFAULT false,
"userId" VARCHAR(36) REFERENCES "_User"("objectId") ON DELETE CASCADE,
"resourceId" VARCHAR(36) REFERENCES "TouristResource"("objectId") ON DELETE SET NULL

);

-- 4. 旅行日志表

CREATE TABLE IF NOT EXISTS "TravelJournal" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"title" VARCHAR(255) NOT NULL,
"content" TEXT NOT NULL,
"photos" JSONB,
"latitude" DOUBLE PRECISION,
"longitude" DOUBLE PRECISION,
"weatherData" JSONB,
"moodRating" INTEGER CHECK ("moodRating" BETWEEN 1 AND 5),
"userId" VARCHAR(36) REFERENCES "_User"("objectId") ON DELETE CASCADE,
"resourceId" VARCHAR(36) REFERENCES "TouristResource"("objectId") ON DELETE SET NULL

);

-- 5. 实时监测数据表

CREATE TABLE IF NOT EXISTS "RealTimeData" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"dataType" VARCHAR(20) NOT NULL CHECK ("dataType" IN ('客流', '天气', '交通')),
"value" JSONB NOT NULL,
"latitude" DOUBLE PRECISION NOT NULL,
"longitude" DOUBLE PRECISION NOT NULL,
"source" VARCHAR(50) NOT NULL,
"validity" TIMESTAMP NOT NULL,
"resourceId" VARCHAR(36) REFERENCES "TouristResource"("objectId") ON DELETE CASCADE

);

-- 6. 安全预警表

CREATE TABLE IF NOT EXISTS "SafetyWarning" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
"title" VARCHAR(255) NOT NULL,
"description" TEXT NOT NULL,
"severity" VARCHAR(10) NOT NULL CHECK ("severity" IN ('紧急', '高', '中')),
"latitude" DOUBLE PRECISION NOT NULL,
"longitude" DOUBLE PRECISION NOT NULL,
"validUntil" TIMESTAMP NOT NULL,
"relatedHazards" JSONB

);

-- 安全预警与旅游资源关联表 CREATE TABLE IF NOT EXISTS "SafetyWarningResource" (

"warningId" VARCHAR(36) REFERENCES "SafetyWarning"("objectId") ON DELETE CASCADE,
"resourceId" VARCHAR(36) REFERENCES "TouristResource"("objectId") ON DELETE CASCADE,
PRIMARY KEY ("warningId", "resourceId")

);

-- 测试数据插入

-- 用户数据 UPDATE "_User" SET

"phone" = '13800138000',
"profilePic" = 'https://example.com/users/1.jpg',
"emergencyContact" = '张女士 13900139000'

WHERE "objectId" = 'user001';

-- 旅游资源数据 INSERT INTO "TouristResource" (

"objectId", "name", "type", "description", "latitude", "longitude", 
"openingHours", "priceRange", "images", "rating", "capacity"

) VALUES ('res001', '故宫博物院', '景点', '中国明清两代的皇家宫殿', 39.916345, 116.397155, '08:30-17:00', '60元', '["https://example.com/res/res001_1.jpg", "https://example.com/res/res001_2.jpg"]', 4.8, 80000), ('res002', '长城酒店', '酒店', '五星级豪华酒店', 40.002345, 116.487155, '全天', '800-2000元', '["https://example.com/res/res002_1.jpg"]', 4.5, 300), ('res003', '老北京烤鸭店', '餐厅', '传统北京烤鸭', 39.906345, 116.407155, '10:00-22:00', '100-300元', '["https://example.com/res/res003_1.jpg", "https://example.com/res/res003_2.jpg"]', 4.7, 200);

-- 行程数据 INSERT INTO "Itinerary" (

"objectId", "title", "startTime", "endTime", "reminderType", 
"latitude", "longitude", "notes", "alertLevel", "isCompleted", 
"userId", "resourceId"

) VALUES ('itn001', '参观故宫', '2023-10-01 09:00:00', '2023-10-01 12:00:00', '时间', 39.916345, 116.397155, '提前预约门票', '中', false, 'user001', 'res001'), ('itn002', '长城酒店入住', '2023-10-01 15:00:00', '2023-10-01 16:00:00', '位置', 40.002345, 116.487155, '带身份证办理', '低', true, 'user001', 'res002');

-- 旅行日志数据 INSERT INTO "TravelJournal" (

"objectId", "title", "content", "photos", 
"latitude", "longitude", "weatherData", "moodRating", 
"userId", "resourceId"

) VALUES ('log001', '故宫初体验', '宏伟的古代建筑群令人震撼', '["https://example.com/logs/log001_1.jpg"]', 39.916345, 116.397155, '{"temperature": 25, "humidity": 60}', 5, 'user001', 'res001'), ('log002', '美味烤鸭', '皮脆肉嫩，正宗北京味', '["https://example.com/logs/log002_1.jpg", "https://example.com/logs/log002_2.jpg"]', 39.906345, 116.407155, '{"temperature": 22, "humidity": 65}', 4, 'user001', 'res003');

-- 实时监测数据 INSERT INTO "RealTimeData" (

"objectId", "dataType", "value", 
"latitude", "longitude", "source", "validity", "resourceId"

) VALUES ('rtd001', '客流', '{"count": 3500, "density": "中等"}', 39.916345, 116.397155, '官方', '2023-10-01 14:00:00', 'res001'), ('rtd002', '天气', '{"temperature": 24, "condition": "晴朗", "aqi": 45}', 39.916345, 116.397155, '传感器', '2023-10-01 13:00:00', 'res001'), ('rtd003', '交通', '{"status": "拥堵", "speed": 15}', 40.002345, 116.487155, '官方', '2023-10-01 14:30:00', 'res002');

-- 安全预警数据 INSERT INTO "SafetyWarning" (

"objectId", "title", "description", "severity", 
"latitude", "longitude", "validUntil", "relatedHazards"

) VALUES ('warn001', '大风黄色预警', '预计未来3小时有8级以上大风', '高', 39.916345, 116.397155, '2023-10-02 18:00:00', '["自然灾害"]'), ('warn002', '道路施工通知', '东华门周边道路施工，请绕行', '中', 39.916345, 116.397155, '2023-10-05 23:00:00', '["事故"]');

-- 预警资源关联 INSERT INTO "SafetyWarningResource" ("warningId", "resourceId") VALUES ('warn001', 'res001'), ('warn001', 'res003'), ('warn002', 'res001');

schema.md 11 KB 永久链接 文件历史 原始文件