I need to store scale question (rate item on a scale of ...) data in SQL Server. The current database structure is:
我需要在SQL Server中存储比例问题（比例项目......）。当前的数据库结构是：
questions
问题
QuestionID
QuestionID
Question
题
question options
问题选择
QuestionID
QuestionID
OptionID
OptionID
OptionDescription
OptionDescription
user responses
用户回复
UserID
用户名
QuestionID
QuestionID
OptionID
OptionID
The scale questions have a range from -100 to 100. I can generate the sequence of numbers in the range in order to enforce referential integrity. Is it the best way of doing it in order to ensure the data accuracy? Is there a better way of doing it?
比例问题的范围是-100到100.我可以生成范围内的数字序列，以强制引用完整性。这是否是确保数据准确性的最佳方式？有没有更好的方法呢？
Thank you.
谢谢。
1 个解决方案
#1
0
Another option would be to use a before insert/update trigger to check the values. This way you wouldn't necessarily be bound to a constraint (other questions might need a different type of response).
另一种选择是使用before insert / update触发器来检查值。这样您就不一定会受到约束（其他问题可能需要不同类型的响应）。
Another solution would be to write a stored proc to do inserts/updates. That way you could check the values within the stored proc before updating the table. But I think it depends on your uses, and whether or not we need to be able to store other types of responses as well.
另一种解决方案是编写存储过程来执行插入/更新。这样，您可以在更新表之前检查存储过程中的值。但我认为这取决于您的用途，以及我们是否还需要能够存储其他类型的回复。
Another option would be to get a little creative with you option table and include MIN and MAX value, and then do a CONNECT BY LEVEL statement to get the different values, something like this (Oracle):
另一种选择是使用选项表获得一点创意并包含MIN和MAX值，然后执行CONNECT BY LEVEL语句以获取不同的值，如下所示（Oracle）：
WITH RECS AS
(
SELECT 'OPTION A' AS ANSWER_OPTION
, 1 AS MIN_VALUE
, 100 AS MAX_VALUE
FROM DUAL
)
SELECT ANSWER_OPTION
, ROWNUM AS OPTION_VALUE
FROM RECS
CONNECT BY LEVEL BETWEEN MIN_VALUE AND MAX_VALUE
#1
0
Another option would be to use a before insert/update trigger to check the values. This way you wouldn't necessarily be bound to a constraint (other questions might need a different type of response).
另一种选择是使用before insert / update触发器来检查值。这样您就不一定会受到约束（其他问题可能需要不同类型的响应）。
Another solution would be to write a stored proc to do inserts/updates. That way you could check the values within the stored proc before updating the table. But I think it depends on your uses, and whether or not we need to be able to store other types of responses as well.
另一种解决方案是编写存储过程来执行插入/更新。这样，您可以在更新表之前检查存储过程中的值。但我认为这取决于您的用途，以及我们是否还需要能够存储其他类型的回复。
Another option would be to get a little creative with you option table and include MIN and MAX value, and then do a CONNECT BY LEVEL statement to get the different values, something like this (Oracle):
另一种选择是使用选项表获得一点创意并包含MIN和MAX值，然后执行CONNECT BY LEVEL语句以获取不同的值，如下所示（Oracle）：
WITH RECS AS
(
SELECT 'OPTION A' AS ANSWER_OPTION
, 1 AS MIN_VALUE
, 100 AS MAX_VALUE
FROM DUAL
)
SELECT ANSWER_OPTION
, ROWNUM AS OPTION_VALUE
FROM RECS
CONNECT BY LEVEL BETWEEN MIN_VALUE AND MAX_VALUE