职业分类/国际标准职业分类SQL表设计

Question

我正试图找出在MySQL中插入国际标准职业分类的最佳方法。

下面是有关类别的详细信息：172572.pdf

此外，我还发现了一个github项目：https://github.com/patriciomacadden/isco/blob/master/db/schema.rb，它似乎根据不同级别的组使用单独的表。

我目前的观点是创建一个表并存储一些重复的数据，因为数据不会经常变化，而且数据量小于一千行。例如：

'l1','l2','l3','l4' are 'TINYINT' and 'level','name' are VARCHAR. So 'level' is the primary key

l1  |l2  |l3  |l4  |level|name
----|----|----|----|-----|--------
5   |null|null|null|5    |Services and Sales Workers
5   |1   |null|null|51   |Personal Services Workers
5   |1   |1   |null|511  |Travel Attendants, Conductors Guides
5   |1   |1   |1   |5111 |Travel Attendants and Travel Stewards
5   |1   |1   |2   |5112 |Transport Conductors
5   |1   |1   |3   |5113 |Travel Guides

“level”字段是varchar，因为我可能需要获得所有行，包括顶级类别。

像511%这样的水平

我不确定使用'level‘作为int是否更好，但也许varchar在对这些特定数据进行排序时也具有更好的特性。

我不确定是否需要单独使用l1、l2、l3、l4，但由于行数太少，也许有一些冗余并不会真正伤人。

所以，问题是，你认为我的设计有什么明显的错误吗？你能改进一下吗？

我不确定我是否需要小心更多的字段，因为我还没有读完那份ISCO。

谢谢

Answer 1

您不需要l1/l2/l3/l4和级别:这些都是完全多余的。以两种不同的方式存储相同的数据只会造成这样一种可能性:在这条线的某个地方，一个错误会使它们不一致，然后你就会得到奇怪的结果。使用l1/2/3/4的查询会发现与使用级别的查询不同的记录，用户很困惑为什么他们的结果没有意义。类似于，如果数据输入屏幕使用level，并且有代码将其分解为l1/2/3/4，则用户将运行表下使用l1/2/3/4的查询，并找到零匹配记录。然后他看着屏幕，唱片就在那儿！或者更糟的是，总数不加起来，等等。

很难说这两个人喜欢哪一个。大多数查询可能更容易用单个字段编写:选择blah，其中level='512'，或者选择blah blah，类似于'51%'，而不是选择l1=5和l2=1以及l3=2和l4为null的blah，等等。哦，在没有引用更高级别的情况下测试较低级别可能毫无意义。也就是说，你什么时候会说选择l2=4，而不是测试l1呢？

级别肯定应该是字符串，而不是整数。你想让'51‘在'512’之前排序，而不是在‘512’之后排序。你永远不会对这些做算术，对吧？说chemist.level + teacher.level或clerk.level * 3意味着什么？

Answer 2

我会做

• level VARCHAR(4) CHARACTER SET ascii。(只需看一眼PDF就可以看出，4就足够了，但如果有必要，可以增加"4“。) VARCHAR将对‘正确’排序，INT则不会。
• 如果您需要显示SUBSTR(level, 3, 1)，则可以获得与l3相当的值。
• SUBSTR将为您提供一个空字符串，而不是NULL；您可以使用IF(,)或CASE...来呈现它。