course_code的适当数据类型是什么？

Question

好吧，我被要求准备一个大学数据库，并要求我以某种方式存储某些数据。例如，我需要存储一个课程代码，该代码包含一个字母，后跟两个整数。例如：I45，D61等。所以它应该是VARCHAR(3)，我说得对吗？但我仍然不确定这是不是一条正确的道路。我也不确定如何在SQL脚本中强制执行这一点。我似乎在我的笔记中找不到任何答案，在我介入脚本之前，我目前正在为这个问题编写数据字典。

有什么建议吗？

Answer 1

尽量使用没有业务意义的主键。您可以轻松地更改数据库设计，而不会对应用程序层造成严重影响。使用哑主关键字，用户不会将含义与特定记录的标识符相关联。

您查询的内容称为智能密钥，通常是用户可见的。用户不可见的键被称为哑键或代理键，有时这个用户不可见的键变得可见，但这不是问题，因为大多数哑键不被用户解释。例如，无论您想如何更改此问题的标题，此问题的id将保持不变https://stackoverflow.com/questions/10412621/

对于智能主键，有时出于美学原因，用户希望指定键的格式和外观。而且这可以很容易地根据用户的感觉经常更新。在应用程序端，这将是一个问题，因为这需要级联相关表上的更改；数据库端也是如此，因为级联更新相关表上的键非常耗时

请在此处阅读详细信息：

http://www.bcarter.com/intsurr1.htm

代理键的优点：http://en.wikipedia.org/wiki/Surrogate_key

您可以在代理密钥(也称为哑键)旁边实现自然密钥(也称为智能密钥)

-- Postgresql has text type, it's a character type that doesn't need length, 
-- it can be upto 1 GB
-- On Sql Server use varchar(max), this is upto 2 GB

create table course
(
    course_id serial primary key, -- surrogate key, aka dumb key

    course_code text unique, -- natural key. what's seen by users e.g. 'D61'

    course_name text unique, -- e.g. 'Database Structure'
    date_offered date
);

这种方法的优点是，当学校在未来的某个时候扩展，然后他们决定提供一个西班牙语迎合的数据库结构，您的数据库与用户引入的用户解释的值是隔离的。

假设您的数据库开始使用智能密钥：

create table course
(
    course_code primary key, -- natural key. what's seen by users e.g. 'D61'

    course_name text unique, -- e.g. 'Database Structure'
    date_offered date
);

然后是面向西班牙语的数据库结构课程。如果用户将自己的规则引入到您的系统中，他们可能会尝试在course_code值：D61/ESP中输入以下内容，其他人会这样做：ESP-D61，ESP:D61。如果用户在主键上决定了他们自己的规则，那么事情可能会失控，然后他们会告诉你根据他们在主键格式上创建的任意规则来查询数据，例如“列出我们学校提供的所有西班牙语课程”，史诗般的要求，不是吗？那么，一个好的开发人员应该做些什么来适应数据库设计中的这些变化呢？他/她将正式化数据结构，其中一个人将重新设计表格以如下所示：

create table course
(
    course_code text, -- primary key
    course_language text, -- primary key

    course_name text unique,
    date_offered date,

    constraint pk_course primary key(course_code, course_language)
);

你看到这有什么问题了吗？这将导致停机，因为您需要将更改传播到依赖于该course表的表的外键。当然，您还需要首先使用它来调整这些依赖表。看看它可能给DBA和开发人员带来的麻烦。

如果您从一开始就使用愚蠢的主键，即使用户在您不知情的情况下向系统引入规则，这也不会导致对数据库设计进行任何大规模的数据更改或数据模式更改。这可以为你赢得时间来相应地调整你的应用程序。然而，如果您将智能放在您的主键中，如上所述的用户需求可以使您的主键自然地退化为复合主键。这不仅在数据库设计重构和大规模数据更新方面很困难，而且对你来说也很难让你的应用程序快速适应新的数据库设计。

create table course
(
    course_id serial primary key,
    course_code text unique, -- natural key. what's seen by users e.g. 'D61'
    course_name text unique, -- e.g. 'Database Structure'
    date_offered date
);

因此，使用代理键，即使用户将新规则或信息隐藏到course_code中，您也可以安全地将更改引入到表中，而不会强迫您快速使您的应用程序适应新设计。您的应用程序仍然可以继续运行，并且不需要停机。它可以为你赢得时间，让你随时随地相应地调整你的应用程序。这是对特定语言课程的更改：

create table course
(
    course_id serial primary key,

    course_code text, -- natural key. what's seen by users e.g. 'D61'
    course_language text, -- natural key. what's seen by users e.g. 'SPANISH'

    course_name text unique, -- e.g. 'Database Structure in Spanish'
    date_offered date,

    constraint uk_course unique key(course_code, course_language)
);

正如您所看到的，您仍然可以执行大量的UPDATE语句，将用户在course_code上强加的规则拆分为两个字段，而不需要对依赖表进行更改。如果您使用智能组合主键，那么重构数据将迫使您将组合主键上的更改级联到依赖表的组合外键。有了哑巴主键，你的应用程序仍然可以照常运行，你可以在以后的任何时间根据新的设计修改你的应用程序(例如，新的文本框，用于课程语言)。使用哑主键，依赖表不需要复合外键来指向课程表，它们仍然可以使用相同的旧哑/代理主键

此外，使用哑主键时，主键和外键的大小不会扩展

Answer 2

这就是域解决方案。仍然不完美，检查可以改进，等等。

set search_path='tmp';

DROP DOMAIN coursename CASCADE;
CREATE DOMAIN coursename AS varchar NOT NULL
    CHECK (length(value) > 0
    AND  SUBSTR(value,1) >= 'A' AND SUBSTR(value,1) <= 'Z'
    AND  SUBSTR(value,2) >= '0' AND SUBSTR(value,2) <= '9' )
    ;

DROP TABLE course CASCADE;
CREATE TABLE course
    ( cname coursename PRIMARY KEY
    , ztext varchar
    , UNIQUE (ztext)
    );
INSERT INTO course(cname,ztext) 
  VALUES ('A11', 'A 11' ), ('B12', 'B 12' ); -- Ok
INSERT INTO course(cname,ztext)
  VALUES ('3','Three' ), ('198', 'Butter' ); -- Will fail

顺便说一下:对于“实际的”PK，我可能会使用一个代理ID。但上面的域(具有唯一约束)可以作为“逻辑”候选键。

这基本上就是表的结果是领域范例。

Answer 3

我强烈建议您不要对数据类型过于具体，所以像VARCHAR(8)这样的数据类型就可以了。原因是：

• 明年的代码中可能有四个字符。业务需求一直在变化，所以不要限制太多的字段长度，让应用层来处理验证--毕竟，它必须将验证问题传达给用户
• 通过将其限制为3个字符
• mysql，虽然您可以在列上定义check约束(希望“验证”值)，但它们会被忽略，并且出于兼容性原因，只允许使用

在系统的所有组件中，数据库模式始终是最难更改的，因此在数据类型中要有一定的灵活性，以尽可能避免更改。