如何使用SQL Server设计多国数据库表

I'm using ASP.NET Core MVC with Entity Framework Core code-first (for a SQL Server database) to develop an application as a learning exercise. From the offset I want the database & application design to be capable of handling multinational scenarios.

我用ASP。NET Core MVC与实体框架Core code-first(用于SQL Server数据库)一起开发应用程序。我希望数据库和应用程序设计能够处理多国场景。

To keep this simple, let's assume I have an Employee table & an EmployeeDetails table. How would you go about designing the database so that the EmployeeDetails are different depending on the country?

为了简单起见，假设我有一个Employee表和一个EmployeeDetails表。如何设计数据库，使雇员的详细信息根据国家的不同而有所不同?

For example, in the UK we have a National Insurance number, where as this data column wouldn't apply to an employee in a different country.

例如，在英国，我们有一个全国性的保险号码，因为这个数据列不适用于其他国家的雇员。

Would you have 1 table with all possible data columns, but disable them in the UI where appropriate? This however would prevent you from marking columns as being required (not nullable).

是否有一个表包含所有可能的数据列，但在UI中适当地禁用它们?但是，这将阻止您将列标记为必需的(不可为空)。
Would you have multiple EmployeeDetails tables, one for each country? This would mean the data retrieval code would have to branch. Furthermore, you would have multiple tables to maintain and probably incur column replication.

您是否有多个雇员详细资料表，每个国家一个?这意味着数据检索代码将不得不分支。此外，您将需要维护多个表，并可能导致列复制。
Would you have a common EmployeeDetails table and have related tables branching off of it containing data fields specific to a country? i.e. (EmployeeDetails > ED_UK) & (EmployeeDetails > ED_France) & (EmployeeDetails > ED_US)

您是否有一个通用的EmployeeDetails表，并将相关表分支到包含特定于某个国家的数据字段的表中?即(EmployeeDetails > ED_UK) & (EmployeeDetails > ED_France) & (EmployeeDetails > ED_US)

Having dynamic data columns will also mean having to build a dynamic HTML UI capable of rendering only the applicable columns. I can't imagine one would want to build multiple UI's, one for each country, to essentially achieve the same task.

拥有动态数据列也意味着必须构建一个动态的HTML UI，它只能够呈现适用的列。我无法想象一个人会想要建立多个UI，每个国家一个，来完成同样的任务。

I very much welcome all feedback and advice.

我非常欢迎所有的反馈和建议。

2 个解决方案

#1

This is a pretty broad question as I don't believe there is a definitive answer. However when you describe something like in this way..

这是一个相当宽泛的问题，因为我认为没有一个明确的答案。然而，当你这样描述某事时……

To keep this simple, let's assume I have an Employee table & an EmployeeDetails table. How would you go about designing the database so that the EmployeeDetails are different depending on the Country?

为了简单起见，假设我有一个Employee表和一个EmployeeDetails表。如何设计数据库，使雇员的详细信息根据国家的不同而有所不同?

This sounds like inheritance might be helpful solving the problem. So ignoring the data storage for a minute, you could have domain entities that look like:

这听起来像继承可能有助于解决问题。所以忽略数据存储一分钟，你可以有域实体看起来像:

// abstract maybe...
public class EmployeeBase
{
  public string FirstName { get; set; }
  // etc...
}

// UKEmployee or EmployeeUK whatever..
public class UKEmployee : EmployeeBase
{
  // Uk Specific Properties
}

I always like to see if the presentation level has any issues with an architecture before looking at SQL. Since you mentioned MVC (I'd use automapper to map to view models) for now I'd just map them to similar models...

在查看SQL之前，我总是想看看表示级别对架构是否有任何问题。既然你提到了MVC(我用automapper来映射模型)，现在我就把它们映射到类似的模型……

public class EmployeeBaseVM { ... }      
public class UKEmployeeVM : EmployeeBaseVM { ... }

And I would imagine the easy way would be something like..

我想最简单的方法是。

// Encapsulating class for and index method on a controller
public class IndexVM
{
  public UKEmployeeVM { get; set; }
}

I would make two display templates (so far):

我会做两个显示模板(到目前为止):

UKEmployeeVM.cshtml

@model UKEmployeeVM

@* display shared properties *@
@Html.DisplayFor(m => m, "EmployeeBaseVM")

@* display specific properties *@
@Html.DisplayFor(m => m.SomeProperty)

EmployeeBaseVM.cshtml

@model EmployeeBaseVM

@* I hope you get where this is going *@

So the presentation level seems fairly easy, so lets work on the data storage. Since you're using EF, I'd suggest using Table per Type Inheritance. No need to go into the details, that page does a good job of explaining it.

因此，表示级别看起来相当简单，因此让我们处理数据存储。由于您使用的是EF，所以我建议使用表的类型继承。没有必要深入细节，那页很好地解释了它。

However, it doesn't go into detail about SQL structure. It appears you have pseudo-unlimited countries so instead of..

但是，它没有详细介绍SQL结构。看起来你有伪无限的国家所以而不是。

[dbo].[EmployeeBase]
[dbo].[UKEmployee]

which seems like it might get unwieldy, I'd suggest switching to Schemas for your segmentations.

这看起来可能会变得很笨拙，我建议您在分段时改用模式。

[base].[Employee]
[UK].[Employee]

The disadvantage to this is that any new countries or functionality require new schemas, EF, Domain, Viewmodels. I believe it's easier to write code that knows exactly the model then to try an write some dynamic code that is trying to do a catch-all for the future.

这样做的缺点是，任何新的国家或功能都需要新的模式、EF、域和视图模型。我认为，编写完全了解模型的代码，然后尝试编写一些动态代码，以便为未来实现一个“一网打尽”。

#2

It depends.

视情况而定。

In this specific example, most countries have some sort of identifier number that is used to identify the employee to the tax collector. The UK may call it the National Insurance number, the US may call it the Social Security Number, other countries will have other names for it, but it is conceptually the same thing across countries. In that case, I would have a single column to store the tax identification number but localize the label in the UI so that the American version says "Social Security Number", the UK version says "National Insurance number", etc.

在这个特定的示例中，大多数国家都有某种标识符编号，用于识别税务官的雇员。英国可能叫它国家保险号码，美国可能叫它社会保险号码，其他国家可能有其他名称，但在概念上，不同国家是一样的。在这种情况下，我将有一个列来存储税识别号，但是在UI中本地化标签，这样美国版本就会显示“社保号”，英国版本会显示“国民保险号”等等。

If you were setting up a full-blown payroll system that would need to have a bunch of country-specific tax and witholding attributes, then you're probably looking at separate tables. Each country is going to have a reasonably unique setup with very specific laws and regulations that need to be coded for so it would make sense to have a set of tables for the information that the IRS needs in the US, a separate set of tables for the information that the HMRC needs in the UK, etc. Individual employees would be set up in one or more tax regimes (presumably most would be in only one) and the code that interacts with those tables would likely be very specialized. You'd probably be writing the module for a particular country's taxing authority from scratch and using some sort of plug-in framework to the main application rather than trying to write a generic UI that would dynamically know how to reconfigure itself to ask for the information the IRS wants vs. the information the HMRC wants as well as to implement the rules specific to those taxing authorities.

如果你正在建立一个完整的工资系统，它需要有一些国家特有的税收和特征，那么你可能会看到不同的表格。每个国家有相当独特的设置非常具体的法律法规需要编码,所以意义有一组表的信息,美国国税局的需求在美国,另一组表的信息,HMRC需要在英国,等。个别员工将建立在一个或多个税制(很有可能会在只有一个),与那些表交互的代码可能会非常专业。你可能会写模块为特定国家的税收权从头和使用某种类型的插件框架,主应用程序而不是试图编写一个通用的用户界面,可以知道如何动态的,将它自己重新配置成要求国税局的信息要与HMRC的信息要实现规则以及针对这些征税机关。

#1