首先，我不确定如何搜索这个，所以如果它是重复的，请原谅。 我甚至不确定它是否更适合其他StackExchange站点之一; 如果是的话，请告诉我，我会在那里问。 无论如何...
项目快速概述
我正在做一个爱好项目 - 作家的笔记本 - 练习编程和数据库设计。 基本结构非常简单：用户可以创建笔记本，在每个笔记本下，他们可以创建与该笔记本相关的项目。 也许笔记本是一系列短篇小说，每个项目都是针对个人故事。
然后，他们可以将项目（场景，角色等）添加到笔记本中的特定项目或笔记本本身，以便它不与特定项目相关联。 这样，他们可以拥有跨越多个项目的场景或位置，以及具有特定于特定项目的一些场景或位置。
问题
我试图在数据库中保留大量的逻辑 - 特别是在表结构和约束中，如果可能的话。 我对很多项目的基本结构基本上都是这样的（我使用的是MySql，但这是一个非常普遍的问题 - 只是提到它的语法）：
CREATE TABLE SCENES( ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, NOTEBOOK BIGINT UNSIGNED NULL, PROJECT BIGINT UNSIGNED NULL, .... );Quick Overview of the Project
I'm working on a hobby project -- a writer's notebook of sorts -- to practice programming and database design. The basic structure is fairly simple: the user can create notebooks, and under each notebook they can create projects associated with that notebook. Maybe the notebook is for a series of short stories, and each project is for an individual story.
They can then add items (scenes, characters, etc.) to either a specific project within the notebook, or to the notebook itself so that it's not associated with a particular project. This way, they can have scenes or locations that span multiple projects, as well as having some that are specific to a particular project.
The Problem
I'm trying to keep a good amount of the logic within the database -- especially within the table structure and constraints if at all possible. The basic structure I have for a lot of the items is basically like this (I'm using MySql, but this is a pretty generic problem -- just mentioning it for the syntax):
CREATE TABLE SCENES( ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, NOTEBOOK BIGINT UNSIGNED NULL, PROJECT BIGINT UNSIGNED NULL, .... );The problem is that I need to ensure that at least one of the two references, NOTEBOOK and/or PROJECT, are set. They don't have to both be set -- PROJECT has a reference to the NOTEBOOK it's in. I know I could just have a generic "Parent Id" field, but I don't believe it'd be possible to have a foreign key to two tables, right? There's also the possibility of adding additional cross-reference tables -- i.e. SCENES_X_NOTEBOOKS and SCENES_X_PROJECTS -- but that'd get out of hand pretty quickly, since I'd have to add similar tables for each of the different item types I'm working with. That would also introduce the problem of ensuring each item has an entry in the cross reference tables.
It'd be easy to put this kind of logic in a stored procedure or the application logic, but I'd really like to keep it in a constraint of some kind if at all possible, just to eliminate any possibility that the logic got bypassed some how.
Any thoughts? I'm interested in pretty much anything -- even if it involves a redesign of the tables or something.
最满意答案
我不清楚你的要求是什么，但至少让我试着回答你的一些问题......
问题是我需要确保设置两个引用中的至少一个，NOTEBOOK和/或PROJECT。
检查（笔记本不是空或项目不是空的）
我不相信有两张桌子的外键，对吗？
从理论上讲，您可以从同一个字段引用两个表，但这意味着这两个表都必须包含匹配的行。 这可能不是你想要的。
你在这里是正确的轨道 - 让NOTEBOOK成为FK朝向一个桌子的子终点，而PROJECT朝向另一个桌面。 不会强制使用NULL外键，因此您不必同时设置它们。
还有可能添加额外的交叉引用表 - 即SCENES_X_NOTEBOOKS和SCENES_X_PROJECTS - 但是这很快就会失控，因为我必须为我正在工作的每种不同的项目类型添加类似的表用。
如果您正在讨论模拟多对多关系的联结（又称链接）表，那么是 - 您必须为参与此类关系的每对表添加它们。
但是，您可以通过使用继承 （也就是类别，子类，子类型，泛化层次结构......）来最小化此类表对的数量。 想象一下，你有一组M表必须连接到第二组N表。 通常，您已经创建了M * N联结表。 但是，如果从公共父表继承第一组中的所有表，并对第二组执行相同操作，则现在可以通过这两个父表之间的一个联结表将它们全部连接起来。
关于继承的完整讨论超出了这里的范围，但您可能希望查看“ERwin方法指南” ，“子类型关系”一章以及本文 。
将这种逻辑放在存储过程或应用程序逻辑中很容易，但是如果可能的话，我真的希望将它保持在某种约束下，以消除逻辑被绕过的任何可能性不知何故。
你的直觉是正确的 - 让数据库尽可能地从坏数据中“保护”自己。 以下是确保数据正确性的首选顺序 ：
表格本身的结构。 声明性数据库约束（域的完整性，密钥的完整性和参照完整性）。 触发器和存储过程。 中间层。 客户。例如，如果您可以确保必须仅使用声明性数据库约束来遵循某个逻辑，请不要将其放在触发器中。
I'm unclear as to what exactly are you requirements, but let me at least try to answer some of your individual questions...
The problem is that I need to ensure that at least one of the two references, NOTEBOOK and/or PROJECT, are set.
CHECK (NOTEBOOK IS NOT NULL OR PROJECT IS NOT NULL)
I don't believe it'd be possible to have a foreign key to two tables, right?
Theoretically, you can reference two tables from the same field, but this would mean both of these tables would have to contain the matching row. This is probably not what you want.
You are on the right track here - let the NOTEBOOK be the child endpoint of a FK towards one table and the PROJECT towards the other. A NULL foreign key will not be enforced, so you don't have to set both of them.
There's also the possibility of adding additional cross-reference tables -- i.e. SCENES_X_NOTEBOOKS and SCENES_X_PROJECTS -- but that'd get out of hand pretty quickly, since I'd have to add similar tables for each of the different item types I'm working with.
If you are talking about junction (aka. link) tables that model many-to-many relationships, then yes - you'd have to add them for each pair of tables engaged in such a relationship.
You could, however, minimize the number of such table pairs by using inheritance (aka. category, subclassing, subtype, generalization hierarchy...). Imagine you have a set of M tables that have to be connected to a second set of N tables. Normally, you'd have create M*N junction tables. But if you inherit all tables in the first set from a common parent table, and do the same for the second set, you can now connect them all through just one junction table between these two parent tables.
The full discussion on inheritance is beyond the scope here, but you might want to look at "ERwin Methods Guide", chapter "Subtype Relationships", as well as this post.
It'd be easy to put this kind of logic in a stored procedure or the application logic, but I'd really like to keep it in a constraint of some kind if at all possible, just to eliminate any possibility that the logic got bypassed some how.
Your instincts are correct - make database "defend" itself from the bad data as much as possible. Here is the order of preference for ensuring the correctness of your data:
The structure of tables themselves. The declarative database constraints (integrity of domain, integrity of key and referential integrity). Triggers and stored procedures. Middle tier. Client.For example, if you can ensure a certain logic must be followed just by using declarative database constraints, don't put it in triggers.
更多推荐
发布评论