AI鸟类

数据范式设计

设定要求您是一名专业的数据库工程师，熟悉PostgreSQL和ParseServer 请注意表名用大驼峰，字段用小驼峰。有预留字段：objectId、updateAt、createAt 关于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应用，地区、路线信息、鸟类信息，请您根据徒步出游以及观鸟的行业经验，设计以上三张表，你也可以适当增加合理的表

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

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

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

UML类图

@startuml AIBirdDatabaseSchema

class Region {
  +String objectId
  +Date createdAt
  +Date updatedAt
  
  +String name
  +String description
  +String climate
  +String terrain
  +Number areaSize
  +String bestSeason
  +Array<String> featureTags
  +GeoPoint centerLocation
  +File mapImage
  +Number popularity
}

class Bird {
  +String objectId
  +Date createdAt
  +Date updatedAt
  
  +String name
  +String scientificName
  +String family
  +String description
  +Array<String> habitats
  +String conservationStatus
  +Array<String> seasons
  +File image
  +String size
  +String color
  +String diet
}

class BirdRoute {
  +String objectId
  +Date createdAt
  +Date updatedAt
  
  +String name
  +String description
  +Pointer<Region> region
  +Number length
  +Number difficulty
  +Number estimatedTime
  +Array<GeoPoint> pathPoints
  +Array<Pointer<Bird>> commonBirds
  +Array<String> bestMonths
  +String startPoint
  +String endPoint
  +Number rating
  +Array<String> facilities
  +File routeImage
  +Number popularity
}

class UserObservation {
  +String objectId
  +Date createdAt
  +Date updatedAt
  
  +Pointer<User> user
  +Pointer<Bird> bird
  +Pointer<BirdRoute> route
  +Date observationTime
  +GeoPoint location
  +String weather
  +String notes
  +File photo
  +Number confidence
}

class RouteReview {
  +String objectId
  +Date createdAt
  +Date updatedAt
  
  +Pointer<User> user
  +Pointer<BirdRoute> route
  +Number rating
  +String comment
  +Array<String> tags
  +Date visitDate
  +Array<Pointer<Bird>> birdsSeen
}

Region "1" -- "*" BirdRoute : contains
BirdRoute "1" -- "*" UserObservation : has
BirdRoute "1" -- "*" RouteReview : has
Bird "1" -- "*" UserObservation : observed
Bird "n" -- "m" BirdRoute : found_in

@enduml

Region（地区表）：存储观鸟地区的基本信息，包括地理特征、最佳季节等 Bird（鸟类表）：记录各种鸟类的详细信息，包括学名、栖息地、保护状态等 BirdRoute（观鸟路线表）：存储推荐的观鸟路线信息，与地区和鸟类关联 UserObservation（用户观察记录表）：记录用户在实际观鸟中的观察数据 RouteReview（路线评价表）：存储用户对路线的评价和反馈

SQL语句

建表语句

-- 地区表 CREATE TABLE "Region" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL,
"updatedAt" TIMESTAMP NOT NULL,
"name" VARCHAR(100) NOT NULL,
"description" TEXT,
"climate" VARCHAR(50),
"terrain" VARCHAR(50),
"areaSize" NUMERIC(10,2),
"bestSeason" VARCHAR(100),
"featureTags" JSONB,
"centerLocation" JSONB, -- {latitude: x, longitude: y}
"mapImage" VARCHAR(255), -- 存储图片路径
"popularity" NUMERIC(5,2) DEFAULT 0

);

-- 鸟类表 CREATE TABLE "Bird" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL,
"updatedAt" TIMESTAMP NOT NULL,
"name" VARCHAR(100) NOT NULL,
"scientificName" VARCHAR(100),
"family" VARCHAR(100),
"description" TEXT,
"habitats" JSONB,
"conservationStatus" VARCHAR(50),
"seasons" JSONB,
"image" VARCHAR(255), -- 存储图片路径
"size" VARCHAR(50),
"color" VARCHAR(100),
"diet" VARCHAR(100)

);

-- 观鸟路线表 CREATE TABLE "BirdRoute" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL,
"updatedAt" TIMESTAMP NOT NULL,
"name" VARCHAR(100) NOT NULL,
"description" TEXT,
"regionId" VARCHAR(36) REFERENCES "Region"("objectId"),
"length" NUMERIC(10,2),
"difficulty" NUMERIC(2,1),
"estimatedTime" NUMERIC(10,2),
"pathPoints" JSONB,
"commonBirds" JSONB, -- 存储鸟类ID数组
"bestMonths" JSONB,
"startPoint" VARCHAR(255),
"endPoint" VARCHAR(255),
"rating" NUMERIC(3,2) DEFAULT 0,
"facilities" JSONB,
"routeImage" VARCHAR(255), -- 存储图片路径
"popularity" NUMERIC(5,2) DEFAULT 0

);

-- 用户观察记录表 CREATE TABLE "UserObservation" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL,
"updatedAt" TIMESTAMP NOT NULL,
"userId" VARCHAR(36) NOT NULL, -- 假设有用户表
"birdId" VARCHAR(36) REFERENCES "Bird"("objectId"),
"routeId" VARCHAR(36) REFERENCES "BirdRoute"("objectId"),
"observationTime" TIMESTAMP,
"location" JSONB, -- {latitude: x, longitude: y}
"weather" VARCHAR(50),
"notes" TEXT,
"photo" VARCHAR(255), -- 存储图片路径
"confidence" NUMERIC(3,2)

);

-- 路线评价表 CREATE TABLE "RouteReview" (

"objectId" VARCHAR(36) PRIMARY KEY,
"createdAt" TIMESTAMP NOT NULL,
"updatedAt" TIMESTAMP NOT NULL,
"userId" VARCHAR(36) NOT NULL, -- 假设有用户表
"routeId" VARCHAR(36) REFERENCES "BirdRoute"("objectId"),
"rating" NUMERIC(2,1),
"comment" TEXT,
"tags" JSONB,
"visitDate" DATE,
"birdsSeen" JSONB -- 存储鸟类ID数组

);

测试数据插入语句

-- 插入地区数据 INSERT INTO "Region" ("objectId", "createdAt", "updatedAt", "name", "description", "climate", "terrain", "areaSize", "bestSeason", "featureTags", "centerLocation", "mapImage", "popularity") VALUES ('reg001', NOW(), NOW(), '洞庭湖湿地', '中国最大的淡水湖之一，重要的候鸟栖息地', '亚热带季风气候', '湿地', 2625.00, '10月-次年3月', '["湿地","候鸟","自然保护区"]', '{"latitude": 29.33, "longitude": 112.8}', 'maps/dongting.jpg', 4.5), ('reg002', NOW(), NOW(), '青海湖', '中国最大的咸水湖，高原鸟类天堂', '高原气候', '湖泊草原', 4583.00, '5月-9月', '["高原湖泊","草原","珍稀鸟类"]', '{"latitude": 36.9, "longitude": 100.2}', 'maps/qinghai.jpg', 4.8);

-- 插入鸟类数据 INSERT INTO "Bird" ("objectId", "createdAt", "updatedAt", "name", "scientificName", "family", "description", "habitats", "conservationStatus", "seasons", "image", "size", "color", "diet") VALUES ('bird001', NOW(), NOW(), '白鹤', 'Grus leucogeranus', '鹤科', '大型涉禽，体羽几乎纯白色', '["湿地","沼泽"]', '极危', '["冬季"]', 'birds/white_crane.jpg', '大型', '白色', '杂食'), ('bird002', NOW(), NOW(), '黑颈鹤', 'Grus nigricollis', '鹤科', '世界上唯一生长繁殖在高原的鹤类', '["高原湿地","沼泽"]', '易危', '["春季","夏季"]', 'birds/blackneck_crane.jpg', '大型', '灰白色', '杂食'), ('bird003', NOW(), NOW(), '红嘴鸥', 'Larus ridibundus', '鸥科', '常见水鸟，嘴红色，头部深褐色', '["湖泊","河流"]', '无危', '["全年"]', 'birds/blackheaded_gull.jpg', '中型', '白色与灰色', '杂食');

-- 插入观鸟路线数据 INSERT INTO "BirdRoute" ("objectId", "createdAt", "updatedAt", "name", "description", "regionId", "length", "difficulty", "estimatedTime", "pathPoints", "commonBirds", "bestMonths", "startPoint", "endPoint", "rating", "facilities", "routeImage", "popularity") VALUES ('route001', NOW(), NOW(), '洞庭湖冬季观鹤线', '观赏白鹤等珍稀候鸟的最佳路线', 'reg001', 8.5, 2.5, 4.0, '[{"latitude": 29.331, "longitude": 112.801}, {"latitude": 29.335, "longitude": 112.805}]', '["bird001","bird003"]', '["11","12","1","2"]', '东洞庭湖保护区入口', '观鸟塔', 4.7, '["观鸟塔","厕所","停车场"]', 'routes/dongting_winter.jpg', 4.8), ('route002', NOW(), NOW(), '青海湖夏季观鸟环线', '环青海湖观鸟自驾路线', 'reg002', 360.0, 3.0, 48.0, '[{"latitude": 36.91, "longitude": 100.21}, {"latitude": 36.92, "longitude": 100.25}]', '["bird002","bird003"]', '["6","7","8"]', '青海湖景区东门', '青海湖景区西门', 4.9, '["观景台","露营区","餐厅"]', 'routes/qinghai_summer.jpg', 4.9);

-- 插入用户观察记录 INSERT INTO "UserObservation" ("objectId", "createdAt", "updatedAt", "userId", "birdId", "routeId", "observationTime", "location", "weather", "notes", "photo", "confidence") VALUES ('obs001', NOW(), NOW(), 'user001', 'bird001', 'route001', '2023-01-15 08:30:00', '{"latitude": 29.332, "longitude": 112.802}', '晴天', '观察到3只成年白鹤觅食', 'photos/obs001.jpg', 0.95), ('obs002', NOW(), NOW(), 'user002', 'bird002', 'route002', '2023-07-20 10:15:00', '{"latitude": 36.915, "longitude": 100.22}', '多云', '一群黑颈鹤在湖边栖息', 'photos/obs002.jpg', 0.98);

-- 插入路线评价 INSERT INTO "RouteReview" ("objectId", "createdAt", "updatedAt", "userId", "routeId", "rating", "comment", "tags", "visitDate", "birdsSeen") VALUES ('rev001', NOW(), NOW(), 'user001', 'route001', 5.0, '非常棒的观鹤体验，看到了珍稀的白鹤', '["专业向导","设施完善"]', '2023-01-15', '["bird001","bird003"]'), ('rev002', NOW(), NOW(), 'user002', 'route002', 4.5, '景色壮丽，鸟类丰富，但部分路段较难走', '["风景优美","挑战性"]', '2023-07-20', '["bird002","bird003"]');

schema.md 9.7 KB 文件歷史 原始文件