这是 NULL 外键的有效使用吗?
考虑我的架构的这个非常小的人为子集:
SensorType1
ID : PK
SensorType2
ID : PK
Reading
Timestamp
Value
SensorType1_ID FK -> SensorType1
SensorType2_ID FK -> SensorType2
一些读数针对 SensorType1,一些读数针对 SensorType2。我可能会添加一个约束来确保这些 FK 中的一个始终指向某个地方。
我过去读过很多关于 NULL FK 的设计非常糟糕的文章,但我已经花了好几天时间研究我的模式(请参阅以前的帖子),无论我以哪种方式扭转它,我最终都会得到一个某个地方可以为 NULL FK,或者我必须为我拥有的每个传感器类型复制我的读取表(及其依赖项)(3)。
上述似乎很好地解决了问题,但出于某种原因,它在我嘴里留下了不太好的味道。这是我的整个架构中允许 NULL 字段的一处。
我认为一些同行评审会帮助我在继续之前接受它。
谢谢!
Consider this very small contrived subset of my schema:
SensorType1
ID : PK
SensorType2
ID : PK
Reading
Timestamp
Value
SensorType1_ID FK -> SensorType1
SensorType2_ID FK -> SensorType2
Some readings are for SensorType1, some are for SensorType2. I would probably add a constraint to ensure exclusively one of those FK's is always pointing somewhere.
I've read a lot in the past about NULL FK's being very bad design, but I've been wrestling with my schema for days (see previous posts) and no matter which way I twist and turn it, I either end up with a NULL-able FK somewhere, or I have to duplicate my reading table (and it's dependants) for every sensor type I have (3).
The above just seems to solve the problem nicely, but it leaves a not-so-nice taste in my mouth, for some reason. It is the ONE place in my entire schema where I allow NULL fields.
I thought a bit of peer review would help me accept it before I move on.
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这样做有什么问题:
What is wrong with doing it like:
第一个 PK 只是“ID”,这意味着他们必须在整个模型中不断更改名称。这使得遵循 RI 变得困难。我认识一些这样的人。我讨厌它,因为它阻止了自动查找列的方法。
我做这样的事情
如果你需要“角色扮演”在一个表中两次有相同的FK,那么
但是你会改变列名称,即使不是被迫的。 ID 变为 Location_ID,然后变为 LoggingLocation_ID,没有技术原因,
我假设这不是物理模型。如果是,为什么要垂直划分 LiveMonitoringLocation 和 HandProbingLocation?仅仅是为了避免可以为空的列吗?如果是这样,你的效用函数就一团糟了。可空列很好...添加一个新表以避免可空列就像从纽约开车到克利夫兰再到波士顿以避免任何红灯一样。
First PK's as just "ID" mean they have to change names constantly throughout the model. It makes following the RI difficult. I know some people like that. I hate it because it prevents an automated approach to finding columns.
I do things like this
If you need to "role play" have the same FK twice in a table then
But you're changing col names even when not forced to. ID becomes Location_ID and then becomes LoggingLocation_ID for no technical reason
I'm assuming this isn't a physical model. If it is, why are you vertically partitioning LiveMonitoringLocation and HandProbingLocation? Is it just to avoid a nullable column? If so you're utility function is all messed up. Nullable columns are fine... adding a new table to avoid a nullable column is like driving from NYC to Cleveland to Boston in order to avoid any red lights.