mysql - 在这种情况下如何最好地设计数据库

Question

乍一看，数据库模式如下所示:

模式必须是第三范式(我知道 hotels.average_rating 另有建议，请尝试监督它，因为数据库尚未完全设计)。这是一个旅游推荐系统。

SQL:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE `activities` (
  `activity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `activity_name` varchar(277) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `bookings` (
  `from_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `to_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `belong_user` int(10) unsigned NOT NULL,
  `belong_hotel` int(10) unsigned NOT NULL,
  `rating` int(3) unsigned NOT NULL,
  KEY `belong_user` (`belong_user`),
  KEY `belong_hotel` (`belong_hotel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `countries` (
  `cuntry_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country_name` varchar(20) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`cuntry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `hotels` (
  `hotel_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_name` varchar(128) COLLATE utf8_bin NOT NULL,
  `hotel_stars` int(3) NOT NULL,
  `hotel_description` text COLLATE utf8_bin NOT NULL,
  `average_price` float unsigned NOT NULL,
  `average_rating` float unsigned NOT NULL,
  `total_rooms` int(10) unsigned NOT NULL,
  `free_rooms` int(10) unsigned NOT NULL,
  `belong_region` int(10) unsigned NOT NULL,
  PRIMARY KEY (`hotel_id`),
  KEY `belong_region` (`belong_region`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `hotels_activity_offers` (
  `belong_hotel` int(10) unsigned NOT NULL,
  `belong_activity` int(10) unsigned NOT NULL,
  UNIQUE KEY `belong_hotel_2` (`belong_hotel`,`belong_activity`),
  KEY `belong_hotel` (`belong_hotel`),
  KEY `belong_activity` (`belong_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `regions` (
  `region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `belong_country` int(10) unsigned NOT NULL,
  `region_name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`region_id`),
  KEY `belong_country` (`belong_country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `regions_activity_offers` (
  `belong_region` int(10) unsigned NOT NULL,
  `belong_activity` int(10) unsigned NOT NULL,
  KEY `belong_region` (`belong_region`),
  KEY `belong_activity` (`belong_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) COLLATE utf8_bin NOT NULL,
  `password` varchar(40) COLLATE utf8_bin NOT NULL COMMENT 'MD5',
  `first_name` varchar(20) COLLATE utf8_bin NOT NULL,
  `last_name` varchar(20) COLLATE utf8_bin NOT NULL,
  `email` varchar(255) COLLATE utf8_bin NOT NULL,
  `is_admin` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `users_favourite_activities` (
  `belong_user` int(10) unsigned NOT NULL,
  `belong_activity` int(10) unsigned NOT NULL,
  UNIQUE KEY `belong_user_2` (`belong_user`,`belong_activity`),
  KEY `belong_user` (`belong_user`),
  KEY `belong_activity` (`belong_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


ALTER TABLE `bookings`
  ADD CONSTRAINT `bookings_ibfk_3` FOREIGN KEY (`belong_hotel`) REFERENCES `hotels` (`hotel_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `bookings_ibfk_2` FOREIGN KEY (`belong_user`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

ALTER TABLE `hotels`
  ADD CONSTRAINT `hotels_ibfk_1` FOREIGN KEY (`belong_region`) REFERENCES `regions` (`region_id`) ON DELETE CASCADE;

ALTER TABLE `hotels_activity_offers`
  ADD CONSTRAINT `hotels_activity_offers_ibfk_2` FOREIGN KEY (`belong_activity`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `hotels_activity_offers_ibfk_1` FOREIGN KEY (`belong_hotel`) REFERENCES `hotels` (`hotel_id`) ON DELETE CASCADE;

ALTER TABLE `regions`
  ADD CONSTRAINT `regions_ibfk_1` FOREIGN KEY (`belong_country`) REFERENCES `countries` (`cuntry_id`) ON DELETE CASCADE;

ALTER TABLE `regions_activity_offers`
  ADD CONSTRAINT `regions_activity_offers_ibfk_2` FOREIGN KEY (`belong_activity`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `regions_activity_offers_ibfk_1` FOREIGN KEY (`belong_region`) REFERENCES `regions` (`region_id`) ON DELETE CASCADE;

ALTER TABLE `users_favourite_activities`
  ADD CONSTRAINT `users_favourite_activities_ibfk_1` FOREIGN KEY (`belong_user`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `users_favourite_activities_ibfk_2` FOREIGN KEY (`belong_activity`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

1. 问题是:如何最好地添加“用户事件日志”功能来存储用户参与的事件？请注意，地区和酒店都可以有事件，并且我需要能够判断该事件是发生在某个地区还是某个酒店。参照完整性应得到保证。

2. 提出一个查询(它应该使用 JOIN，不是吗？)其中列出了所有用户及其事件以及酒店 ID 或地区 id。(不适用的可以为 NULL，如果需要)。

简单的解决方案更好 - 因此最好不要使用存储过程或任何在 mysql 特定功能中挖掘太多的东西。

Answer 1

您的数据库未规范化 - 您完成它的方式看起来像是规范化为何是个好主意的典范。

hotels.average_rating?

什么鬼？

虽然对您的数据进行非规范化是有意义的，但这不是正确的做法。想一想当用户提交酒店评分时您需要做什么——您需要根据所有提交的评分重新计算值(value)。相反，如果您持有 sum_of_ratings(或什至保留当前平均值)和一些评级，那么您可以根据酒店记录和新评级计算新值，而无需查看其他评级.