确保表中列之间的引用完整性
目前我有一个具有以下格式的表模型：
criteria (criteria_id, criteria_name)
criteria_data(criteria_id, value)
我打算将与日期相关的信息存储到表中感觉一个条件（日期条件）可以仅包含 criteria_data 表中的单个日期，而其他 criteria_data 可以是单独行中该日期的股票价格。 （另一个复杂之处是：股票名称也是一个条件）
我的问题：
我如何才能确保在特定日期和股票名称（ 2 个其他单独的标准和行）。
我真的不想在应用程序层强制执行此操作，因此我主要寻找数据库层解决方案（如果可用）。
如果建议更合适的数据模型，我也愿意被告知废弃我的整个表模型。
编辑 在得知我的愚蠢行为后（请参阅下面的 dPortas 帖子），我承认这不是明智的做法。我想到了一个新模型：
criteria_data(stockName,price, high,low,price,change)
虽然这就是它的样子，但我认为实际的列名称将是包含 criteria_id 的标识符。例如，stockname 字段可以是 col_1，high 可以是 col_3，但这将确保我可以在各个列上强制执行完整性。
人们对此有何看法？
Currently I have a table model with the following format:
criteria (criteria_id, criteria_name)
criteria_data(criteria_id, value)
I intend on storing Date-related information into the table in the sense that one criteria ( the Date criteria) could just contain the single date in the criteria_data table while other criteria_data could be the price of the stock for the date in a separate row. ( Another complication is that: the name of the stock is also a criteria)
My problem:
How is it possible for me to ensure that only 1 price ( single row criteria) can be entered into the table for a particular date and stock name ( 2 other separate criteria and rows).
I really don't want to enforce this in the App layer so I am mainly looking for DB Layer solutions , if available.
I am also open to being told to scrap my entire table model, if a more suitable Data model is suggested.
EDIT
After being informed of my folly ( see dPortas post below), I accept that this is not the smart way to go. I thought of a new model:
criteria_data(stockName,price, high,low,price,change)
While this is what it looks like, I am thinking the actual column names would be an identifier containing the criteria_id . For example, the stockname field could be col_1 and high could be col_3 but this would ensure that I could enforce integrity on the various columns.
What are people thoughts on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的表格设计看起来可疑地像 的情况EAV。该反模式的缺点之一是您无法准确存储正确的数据类型或对其应用约束。我建议你重新考虑一下设计。
建议重新设计：标准（criteria_id，criteria_name，日期，stock_name，价格）键：（stock_name，date）
Your table design looks suspiciously like a case of EAV. Among the disadvantages of that anti-pattern are that you can't accurately store the right datatypes or apply constraints to it. I suggest you reconsider the design.
Suggested redesign: criteria (criteria_id, criteria_name, date, stock_name, price) key: (stock_name, date)