CREATE TABLE `被照顧者` (
	`uid` int PRIMARY KEY,
	`User_id` int,
	`地址` varchar(255),
	`緊急聯絡人` varchar(255),
	`緊急聯絡人電話` int,
	`基本狀況描述` varchar(255),
	FOREIGN KEY(`User_id`) REFERENCES `User` (`id`)
);
CREATE TABLE `User` (
	`uid` int PRIMARY KEY,
	`name` varchar(255),
	`description` varchar(255),
	`email` varchar(255),
	`age` int,
	`category_id` int,
	`group_id` int,
	`create_at` datetime,
	`update_at` datetime,
	`fb_uid` varchar(255),
	`fb_token` varchar(255)
);
CREATE TABLE `照顧者` (
	`uid` int PRIMARY KEY,
	`User_id` int,
	`photo` varchar(255),
	`經歷` varchar(255),
	`專長` varchar(255),
	`服務地區` varchar(255),
	`證書字號` varchar(255),
	`證照期限` date,
	`受訓單位` varchar(255),
	FOREIGN KEY(`User_id`) REFERENCES `User` (`id`)
);
CREATE TABLE `被照顧者_照顧者_關聯` (
	`被照顧者_id` int,
	`照顧者_id` int,
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`),
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`)
);
CREATE TABLE `家屬` (
	`uid` int PRIMARY KEY,
	`User_id` int,
	`被照顧者_id` int,
	`關係` varchar(255),
	FOREIGN KEY(`User_id`) REFERENCES `User` (`id`),
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`)
);
CREATE TABLE `被照顧者_照顧者_Like` (
	`uid` int PRIMARY KEY,
	`被照顧者_id` int,
	`照顧者_id` int,
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`),
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`)
);
CREATE TABLE `家屬_照顧者_Like` (
	`uid` int PRIMARY KEY,
	`照顧者_id` int,
	`家屬_id` int,
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`),
	FOREIGN KEY(`家屬_id`) REFERENCES `家屬` (`id`)
);
CREATE TABLE `Comments` (
	`uid` int PRIMARY KEY,
	`被照顧者_id` int,
	`家屬_id` varchar(255),
	`照顧者_id` int,
	`description` varchar(255),
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (``),
	FOREIGN KEY(`家屬_id`) REFERENCES `家屬` (``),
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`)
);
CREATE TABLE `健康紀錄` (
	`uid` int PRIMARY KEY,
	`被照顧者_id` int,
	`舒張壓` varchar(255),
	`收縮壓` varchar(255),
	`血糖` varchar(255),
	`BMI` varchar(255),
	`其他狀況` varchar(255),
	`日期` date,
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`)
);
CREATE TABLE `身體狀況` (
	`uid` int PRIMARY KEY,
	`健康紀錄_id` int,
	`狀況(良好, 中等,稍差 ，請多留意觀察,差，需請醫生評估)` varchar(255),
	FOREIGN KEY(`健康紀錄_id`) REFERENCES `健康紀錄` (`id`)
);
CREATE TABLE `照顧者_技能_關係` (
	`uid` int PRIMARY KEY,
	`需求_技能_id` int,
	`照顧者_id` int,
	FOREIGN KEY(`需求_技能_id`) REFERENCES `需求_技能` (``),
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`)
);
CREATE TABLE `需求_技能` (
	`uid` int PRIMARY KEY,
	`關懷服務(□陪伴□情緒支持)` varchar(255),
	`個人健康照顧(□盥洗□入浴□如廁□更衣□餵食□肢體關節運動□翻身□拍背)` varchar(255),
	`家務服務(□洗衣□服務對象起居環境清潔□陪同或代理購物 □備餐（購買/加熱）□備餐（煮食）)` varchar(255),
	`文書服務(□協助申請社會福利服務□代寫書信及聯絡親友)` varchar(255),
	`醫務服務(□陪同就醫□代領藥品 □保健服務（□量血壓□量血糖＊相關器材乙方自備）)` varchar(255),
	`休閒服務(□陪同散步□閱讀書報)` varchar(255),
	`每週服務時間` datetime,
	`被照顧者_id` int,
	`照顧者_id` int,
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (``),
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (``)
);
CREATE TABLE `被照顧者_需求_關係` (
	`uid` int PRIMARY KEY,
	`被照顧者_id` int,
	`需求_技能_id` int,
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`),
	FOREIGN KEY(`需求_技能_id`) REFERENCES `需求_技能` (`id`)
);
CREATE TABLE `服用藥物` (
	`uid` int PRIMARY KEY,
	`被照顧者_id` int,
	`藥單photo` varchar(255),
	`單次服用photo` varchar(255),
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`)
);
CREATE TABLE `藥物服用時間` (
	`uid` int PRIMARY KEY,
	`服用藥物_id` int,
	`名稱(早餐飯前、早餐飯後、午餐飯前、午餐飯後、晚餐飯前、晚餐飯後、睡前)` varchar(255),
	FOREIGN KEY(`服用藥物_id`) REFERENCES `服用藥物` (`id`)
);
CREATE TABLE `訂單` (
	`uid` int PRIMARY KEY,
	`照顧者_id` int,
	`被照顧者_id` int,
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`),
	FOREIGN KEY(`被照顧者_id`) REFERENCES `被照顧者` (`id`)
);
CREATE TABLE `照顧者有空時間/被照顧者服務時間` (
	`uid` int PRIMARY KEY,
	`訂單_id` int,
	`照顧者_id` int,
	`日期` date,
	`開始時間(half hour a unit)` varchar(255),
	`結束時間(half hour a unit)` varchar(255),
	FOREIGN KEY(`訂單_id`) REFERENCES `訂單` (`id`),
	FOREIGN KEY(`照顧者_id`) REFERENCES `照顧者` (`id`)
);