使用SQL模拟松散的“基于文档的”模式

Question

我正在研究如何使用SQL软件来存储和查询松散模式的对象。

我们知道对象对应于SQL表的一行，它的属性对应于列。所谓“松散模式”，我的意思是对象可以具有不硬连接到表(而不是表)结构的属性。可以将任意(属性、值)附加到对象。

让我用一个具体的例子来说明：

对于给定的对象：

<subject>
name:data structures
tag:CS 
description:easy
desired_seniority:5.0

<subject>
name:microprocessors
tag:CS,electronics        <multi-valued
description:easy
desired_seniority:5.5     <numeric

和

<teacher>
name:John Doe
tag:electronics
seniority:5.8

查询将是

For each teacher, 
return all subjects whose tags match and 
whose desired seniority is less than or equal to the teacher's

教师的“无名氏”将与主语“微处理器”相匹配，因为他们共用“电子”标签，而且他的资历5.8比该学科预期的资历5.5要高。

请注意，我在字符串中使用了数字(查询做比较)和多值 (queries‘in’match)字符串。

到底是什么：，我正在寻找合适的数据模型和相应的查询，这些查询允许我在一个SQL软件上模拟一个松散的模式。

我接受这些限制：

1. 教师和学科可以用不同的表格来描述。
2. 属性之间存在一对一的对应关系(如果查询使用属性“x”，则所有行都具有属性“x1”和“x2”的相同类型)

我看到了http://www.igvita.com/2010/03/01/schema-free-mysql-vs-nosql/和https://github.com/jamesgolick/friendly。

是的，有NoSQL数据库和特殊的ORM。我们使用索尔 (太棒了！)。但我会避免增加项目的复杂性，包括他们。模拟noSQL存储的性能开销不是问题。

Answer 1

在MySql中，我会使用这样的东西：

CREATE TABLE IF NOT EXISTS `subject` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) NOT NULL,
  `desired_seniority` decimal(5,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


INSERT INTO `subject` (`id`, `name`, `description`, `desired_seniority`) VALUES
(1, 'data structures', 'easy', 5.00),
(2, 'microprocesors', 'easy', 5.50);

CREATE TABLE IF NOT EXISTS `subject_tag` (
  `subject_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`subject_id`,`tag_id`),
  KEY `fk_subject_tag_tags` (`tag_id`),
  KEY `fk_subject_tag_subject` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `subject_tag` (`subject_id`, `tag_id`) VALUES
(1, 1),
(2, 1),
(2, 2);

CREATE TABLE IF NOT EXISTS `tag` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


INSERT INTO `tag` (`id`, `name`) VALUES
(1, 'CS'),
(2, 'Electronics');


CREATE TABLE IF NOT EXISTS `teacher` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `surname` varchar(50) NOT NULL,
  `seniority` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;


INSERT INTO `teacher` (`id`, `name`, `surname`, `seniority`) VALUES
(1, 'John', 'Doe', 5.80),
(2, 'John', 'Brown', 5.30);



CREATE TABLE IF NOT EXISTS `teacher_tag` (
  `teacher_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`teacher_id`,`tag_id`),
  KEY `fk_teacher_tag_tag1` (`tag_id`),
  KEY `fk_teacher_tag_teacher` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher_tag` (`teacher_id`, `tag_id`) VALUES
(1, 2);


ALTER TABLE `subject_tag`
  ADD CONSTRAINT `fk_subject_tag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_subject_tag_subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `teacher_tag`
  ADD CONSTRAINT `fk_teacher_tag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_teacher_tag_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

并让教师的主修科目相匹配：

SELECT t.surname, t.name, GROUP_CONCAT( s.name SEPARATOR ' | ' ) AS subject_name
FROM teacher t
JOIN teacher_tag tt ON tt.teacher_id = t.id
JOIN subject_tag st ON st.tag_id = tt.tag_id
JOIN subject s ON st.subject_id = s.id
AND s.desired_seniority <= t.seniority
GROUP BY t.id