How do I store custom values in place of data types for the column. I have a Table named as 'Orders' in which a column named as 'OrderStatus' and it's purpose will be to store the Status of the Order. For example R=Rejected, S=Sent, T=Returned.
如何将自定义值存储到列的数据类型中。我有一个名为“Orders”的表，其中一个名为“OrderStatus”的列，其目的是存储订单的状态。例如R= reject, S= send, T= return。
I am very new to databases and design...
我对数据库和设计非常陌生……
4 个解决方案
#1
1
In this case you don't really need a custom data type, you just need a foreign key to a table with all of your statuses in it.
在这种情况下，实际上不需要自定义数据类型，只需要一个具有所有状态的表的外键。
Your OrderStatus table would look like:
您的OrderStatus表将如下所示:
id int PK NOT NULL, IDENTITY
code char(1) NOT NULL
description varchar(100) NOT NULL
(edit: note, as Martin pointed out in a comment on another answer, the surrogate id key isn't entirely necessary, but it allows flexibility for easily changing the code without having to update the data that refers to it)
(编辑:请注意，正如Martin在另一个答案的注释中所指出的，代理id键并不是完全必需的，但它允许灵活地更改代码，而无需更新引用它的数据)
Your Order table would then have a foreign key to this table:
然后，您的订单表将具有此表的外键:
order_status_id int FK NOT NULL
#2
2
The 'correct' answer is to use Standard SQL-92's CREATE DOMAIN. Sadly, SQL Server doesn't support it yet. If you would like to see support you can vote for it here.
“正确”的答案是使用标准SQL-92的CREATE DOMAIN。遗憾的是，SQL Server还不支持它。如果你想获得支持，你可以在这里投票。
SQL Server has its own CREATE TYPE syntax but when I last looked I declared them not fit for purpose. If anyone disagrees, please post an answer to my question :)
SQL Server有自己的创建类型语法，但是当我最后一次查看时，我声明它们不适合使用。如果有人不同意，请回答我的问题:
This leaves two viable choices: a table with a foreign keys or CHECK constraints. The rule of thumb is that if the set of domain values is small and stable (e.g. the ISO 5218 sex codes) then use CHECK constraints, otherwise prefer a table with foreign keys.
这就留下了两个可行的选择:具有外键的表或检查约束。经验法则是，如果域值集很小且稳定(例如ISO 5218性别码)，则使用检查约束，否则更喜欢带有外键的表。
#3
1
You can do that.
你可以这样做。
OrderStatus OrderStatusCode
----------- ---------------
Rejected R
Sent S
Returned T
Later, when you've got more 'design' under your belt, you go with a lookup table.
之后，当你有更多的“设计”在你的腰带下时，你可以使用查找表。
#4
0
Best practice is to normalize, which means creating a table called ORDER_STATUS with the approved values in there, minimally with a schema like:
最佳实践是规范化，这意味着创建一个名为ORDER_STATUS的表，其中包含已批准的值，最低限度地使用以下模式:
ORDER_STATUS(id number auto increment, code character, meaning varchar)
and then relate ORDERS to that table in a column called order_status_id, which would be a foreign key that would not be null.
然后将订单关联到名为order_status_id的列中，该列将是不为空的外键。
#1
1
In this case you don't really need a custom data type, you just need a foreign key to a table with all of your statuses in it.
在这种情况下，实际上不需要自定义数据类型，只需要一个具有所有状态的表的外键。
Your OrderStatus table would look like:
您的OrderStatus表将如下所示:
id int PK NOT NULL, IDENTITY
code char(1) NOT NULL
description varchar(100) NOT NULL
(edit: note, as Martin pointed out in a comment on another answer, the surrogate id key isn't entirely necessary, but it allows flexibility for easily changing the code without having to update the data that refers to it)
(编辑:请注意，正如Martin在另一个答案的注释中所指出的，代理id键并不是完全必需的，但它允许灵活地更改代码，而无需更新引用它的数据)
Your Order table would then have a foreign key to this table:
然后，您的订单表将具有此表的外键:
order_status_id int FK NOT NULL
#2
2
The 'correct' answer is to use Standard SQL-92's CREATE DOMAIN. Sadly, SQL Server doesn't support it yet. If you would like to see support you can vote for it here.
“正确”的答案是使用标准SQL-92的CREATE DOMAIN。遗憾的是，SQL Server还不支持它。如果你想获得支持，你可以在这里投票。
SQL Server has its own CREATE TYPE syntax but when I last looked I declared them not fit for purpose. If anyone disagrees, please post an answer to my question :)
SQL Server有自己的创建类型语法，但是当我最后一次查看时，我声明它们不适合使用。如果有人不同意，请回答我的问题:
This leaves two viable choices: a table with a foreign keys or CHECK constraints. The rule of thumb is that if the set of domain values is small and stable (e.g. the ISO 5218 sex codes) then use CHECK constraints, otherwise prefer a table with foreign keys.
这就留下了两个可行的选择:具有外键的表或检查约束。经验法则是，如果域值集很小且稳定(例如ISO 5218性别码)，则使用检查约束，否则更喜欢带有外键的表。
#3
1
You can do that.
你可以这样做。
OrderStatus OrderStatusCode
----------- ---------------
Rejected R
Sent S
Returned T
Later, when you've got more 'design' under your belt, you go with a lookup table.
之后，当你有更多的“设计”在你的腰带下时，你可以使用查找表。
#4
0
Best practice is to normalize, which means creating a table called ORDER_STATUS with the approved values in there, minimally with a schema like:
最佳实践是规范化，这意味着创建一个名为ORDER_STATUS的表，其中包含已批准的值，最低限度地使用以下模式:
ORDER_STATUS(id number auto increment, code character, meaning varchar)
and then relate ORDERS to that table in a column called order_status_id, which would be a foreign key that would not be null.
然后将订单关联到名为order_status_id的列中，该列将是不为空的外键。