从表中创建嵌套数组的最佳方式:多个查询/循环VS单个查询/循环样式

Say I have 2 tables, which I can "merge" and represent in a single nested array.

假设有两个表，可以“合并”并在一个嵌套数组中表示。

I'm wandering what would be the best way to do that, considering:

我在想最好的办法是什么，考虑到:

• efficiency
• 效率
• best-practice
• 最佳实践
• DB/server-side usage trade-off
• DB /服务器端使用的权衡
• what you should do in real life
• 你在现实生活中应该做什么
• same case for 3, 4 or more tables that can be "merged" that way
• 对于3、4个或更多的表，也可以用这种方式“合并”

The question is about ANY server-side/relational-db.

2 simple ways I was thinking about (if you have others, please suggest! notice I'm asking for a simple SERVER-SIDE and RELATIONAL-DB, so please don't waste your time explaining why I shouldn't use this kind of DB, use MVC design, etc., etc. ...):

我在想的两种简单的方式(如果你有其他的，请建议!)注意，我想要一个简单的服务器端和关系数据库，所以请不要浪费时间解释为什么我不应该使用这种DB，使用MVC设计等等。

1. 2 loops, 5 simple "SELECT" queries
2. 2个循环，5个简单的“选择”查询
3. 1 loop, 1 "JOIN" query
4. 1个循环，1个“连接”查询

I've tried to give a simple and detailed example, in order to explain myself & understand better your answers (though how to write the code and/or finding possible mistakes is not the issue here, so try not to focus on that...)

我试图给出一个简单而详细的例子，以更好地解释我自己和理解你的答案(尽管如何编写代码和/或发现可能的错误不是这里的问题，所以尽量不要把重点放在这上面……)

SQL SCRIPTS FOR CREATING AND INSERTING DATA TO TABLES

CREATE TABLE persons
(
    id int NOT NULL AUTO_INCREMENT,
    fullName varchar(255),
    PRIMARY KEY (id)
);

INSERT INTO persons (fullName) VALUES ('Alice'), ('Bob'), ('Carl'), ('Dan');

CREATE TABLE phoneNumbers
(
    id int NOT NULL AUTO_INCREMENT,
    personId int,
    phoneNumber varchar(255),
    PRIMARY KEY (id)
);

INSERT INTO phoneNumbers (personId, phoneNumber) VALUES ( 1, '123-456'), ( 1, '234-567'), (1, '345-678'), (2, '456-789'), (2, '567-890'), (3, '678-901'), (4, '789-012');  

A JSON REPRESENTATION OF THE TABLES AFTER I "MERGED" THEM:

[
  {
    "id": 1,
    "fullName": "Alice",
    "phoneNumbers": [
      "123-456",
      "234-567",
      "345-678"
    ]
  },
  {
    "id": 2,
    "fullName": "Bob",
    "phoneNumbers": [
      "456-789",
      "567-890"
    ]
  },
  {
    "id": 3,
    "fullName": "Carl",
    "phoneNumbers": [
      "678-901"
    ]
  },
  {
    "id": 4,
    "fullName": "Dan",
    "phoneNumbers": [
      "789-012"
    ]
  }
]

PSEUDO CODE FOR 2 WAYS:

1.

1。

query: "SELECT id, fullName FROM persons"
personList = new List<Person>()
foreach row x in query result:
    current = new Person(x.fullName)
    "SELECT phoneNumber FROM phoneNumbers WHERE personId = x.id"
    foreach row y in query result:
        current.phoneNumbers.Push(y.phoneNumber)
    personList.Push(current)        
print personList         

2.

2。

query: "SELECT persons.id, fullName, phoneNumber FROM persons
            LEFT JOIN phoneNumbers ON persons.id = phoneNumbers.personId"
personList = new List<Person>()
current = null
previouseId = null
foreach row x in query result:
    if ( x.id !=  previouseId )
        if ( current != null )
            personList.Push(current)
            current = null
        current = new Person(x.fullName)
    current.phoneNumbers.Push(x.phoneNumber)
print personList            

CODE IMPLEMENTATION IN PHP/MYSQL:

1.

1。

/* get all persons */
$result = mysql_query("SELECT id, fullName FROM persons"); 
$personsArray = array(); //Create an array
//loop all persons
while ($row = mysql_fetch_assoc($result))
{
    //add new person
    $current = array();
    $current['id'] = $row['id'];
    $current['fullName'] = $row['fullName'];

    /* add all person phone-numbers */
    $id = $current['id'];
    $sub_result = mysql_query("SELECT phoneNumber FROM phoneNumbers WHERE personId = {$id}");
    $phoneNumbers = array();
    while ($sub_row = mysql_fetch_assoc($sub_result))
    {
        $phoneNumbers[] = $sub_row['phoneNumber']);
    }
    //add phoneNumbers array to person
    $current['phoneNumbers'] = $phoneNumbers;

    //add person to final result array
    $personsArray[] = $current;
}

echo json_encode($personsArray);

2.

2。

/* get all persons and their phone-numbers in a single query */
$sql = "SELECT persons.id, fullName, phoneNumber FROM persons
            LEFT JOIN phoneNumbers ON persons.id = phoneNumbers.personId";
$result = mysql_query($sql); 

$personsArray = array();
/* init temp vars to save current person's data */
$current = null;
$previouseId = null;
$phoneNumbers = array();
while ($row = mysql_fetch_assoc($result))
{
    /*
       if the current id is different from the previous id:
       you've got to a new person.
       save the previous person (if such exists),
       and create a new one
    */
    if ($row['id'] != $previouseId )
    {
        // in the first iteration,
        // current (previous person) is null,
        // don't add it
        if ( !is_null($current) )
        {
            $current['phoneNumbers'] = $phoneNumbers;
            $personsArray[] = $current;
            $current = null;
            $previouseId = null;
            $phoneNumbers = array();
        }

        // create a new person
        $current = array();
        $current['id'] = $row['id'];
        $current['fullName'] = $row['fullName'];
        // set current as previous id
        $previouseId = $current['id'];
    }

    // you always add the phone-number 
    // to the current phone-number list
    $phoneNumbers[] = $row['phoneNumber'];
    }
}

// don't forget to add the last person (saved in "current")
if (!is_null($current))
    $personsArray[] = $current);

echo json_encode($personsArray);

P.S. this link is an example of a different question here, where i tried to suggest the second way: tables to single json

注意，这个链接是另一个问题的例子，我在这里尝试建议第二种方法:表到单个json

3 个解决方案

#1

Preliminary

First, thank you for putting that much effort into explaining the problem, and for the formatting. It is great to see someone who is clear about what they are doing, and what they are asking.

首先，感谢您花了这么多精力来解释这个问题和格式化。很高兴看到有人清楚自己在做什么，以及他们在问什么。

But it must be noted that that, in itself, forms a limitation: you are fixed on the notion that this is the correct solution, and that with some small correction or guidance, this will work. That is incorrect. So I must ask you to give that notion up, to take a big step back, and to view (a) the whole problem and (b) my answer without that notion.

但必须指出的是，这本身就形成了一种限制:你坚持认为这是正确的解决方案，只要稍加修正或指导，就能奏效。这是不正确的。所以我必须要求你们放弃这个想法，退后一步，看看(a)整个问题，(b)我的答案没有这个概念。

The context of this answer is:

这个答案的背景是:

• all the explicit considerations you have given, which are very important, which I will not repeat

  你给出的所有明确的考虑，都是非常重要的，我不会重复

• the two most important of which is, what best practice and what I would do in real life

  其中最重要的两个是，什么是最佳实践，以及我在现实生活中会做什么

This answer is rooted in Standards, the higher order of, or frame of reference for, best practice. This is what I have done in real life since 1990, meaning that since 1990, I have never had the need to write code such as yours. This is what the commercial Client/Server world does, or should be doing.

这个答案植根于标准、最佳实践的更高阶或参考框架。这是我自1990年以来在现实生活中所做的事情，这意味着自1990年以来，我从未需要编写像您这样的代码。这是商业客户端/服务器世界所做的，或者应该做的。

This issue, this whole problem space, is becoming a common problem. I will give a full consideration here, and thus answer another SO question as well. Therefore it might contain a tiny bit more detail that you require. If it does, please forgive this.

这个问题，整个问题空间，正在成为一个普遍的问题。我将在此充分考虑，从而回答另一个同样的问题。因此，它可能包含您需要的更小的细节。如果有，请原谅。

Consideration

1. The database is a server-based resource, shared by many users. In an online system, the database is constantly changing. It contains that One Version of the Truth (as distinct from One Fact in One Place, which is a separate, Normalisation issue) of each Fact.

   数据库是一个基于服务器的资源，由许多用户共享。在在线系统中，数据库是不断变化的。它包含了每个事实的一个版本(不同于一个地方的一个事实，这是一个独立的、正常化的问题)。

   • the fact that mickey mouse NONsqls do not have a server architecture, and that therefore the notion of server in such software is false and misleading, are separate but noted points.
   • 事实上，米老鼠非sqls没有服务器架构，因此在此类软件中使用服务器的概念是错误的和具有误导性的，这是独立的但值得注意的一点。

2. As I understand it, JSON and JSON-like structures are required for "performance reasons", precisely because the "server" doesn't, cannot, perform as a server. The concept is to cache the data on each (every) client, such that you are not fetching it from the "server" all the time.

   在我看来，出于“性能原因”需要JSON和JSON之类的结构，正是因为“服务器”不能作为服务器执行。其概念是缓存每个(每个)客户机上的数据，这样就不会一直从“服务器”获取数据。

   • This opens up a stinking can of worms. If you do not design and implement this properly, the worms will overrun the app and the stench will kill you.

     这打开了一罐臭虫。如果你没有正确的设计和实现，蠕虫将会超出应用程序，恶臭会杀死你。

   • Such an implementation is a gross violation of the Client/Server Architecture, which allows simple code on both sides, and appropriate deployment of software and data components, such that implementation times are small, and efficiency is high.

     这样的实现严重违反了客户端/服务器体系结构，该体系结构允许两边都有简单的代码，并且适当地部署软件和数据组件，因此实现时间很短，效率也很高。

   • Further, such an implementation requires a substantial implementation effort, and it is complex, consisting of many parts. Each of those parts must be appropriately designed.

     此外，这种实现需要大量的实现工作，而且非常复杂，由许多部分组成。每个部件都必须适当地设计。

   • The web, and the many books written in this subject area, provide a cesspool of methods, marketed on the basis of supposed simplicity; ease; anyone-can-do-anything; freeware-can-do-anything; etc. There is not scientific basis for any of those proposals.

     网络，以及在这一主题领域所写的许多书，提供了一系列的方法，以假定的简单性为基础进行营销;缓解;anyone-can-do-anything;freeware-can-do-anything;这些建议没有科学依据。

Non-architecture & Sub-standard

As evidenced, you have learned that that marketed mythology is fraudulent. You have encountered one problem, one instance that that advice is false. As soon as you solve this one problem, the next problem, which is not apparent to you right now, will be exposed. The notions are a never-ending set of problems.

正如你所了解到的，营销神话是骗人的。您遇到了一个问题，这个建议是错误的。一旦你解决了这一个问题，下一个问题就会暴露出来，这个问题现在对你来说并不明显。这些观念是一套永无止境的问题。

I will not enumerate all the false notions that these pretend-experts (in reality, circus freaks who are ignorant of technology) market. I trust that as you progress through my answer, you will notice that one after the other marketed notion is false.

我不会列举所有这些假装专家(实际上，是不懂技术的马戏团怪胎)的错误观点。我相信，当你在我的回答中取得进展时，你会注意到，在另一种市场营销观念是错误的。

The two bottom lines are:

两条底线是:

1. The notions violate Architecture and Design Standards, namely Client/Server Architecture; Open Architecture; Engineering Principles; and to a lesser in this particular problem, Database Design Principles.

   这些概念违反了架构和设计标准，即客户端/服务器架构;开放式体系结构;工程原则;对于这个特殊的问题，数据库设计的原则。

2. Which leads to people like you, who are trying to do an honest job, being defrauded, tricked, seduced, into implementing simple notions, which turn into massive implementations. Implementations that will never quite work, so they require substantial ongoing maintenance, and will eventually be replaced, wholesale.

   这就导致了像你这样的人，他们试图做一份诚实的工作，被欺骗，被欺骗，被引诱，去实现简单的想法，这些想法变成了大规模的实现。实现永远不会正常工作，因此它们需要大量的持续维护，并最终被批量替换。

Architecture

The central principle being violated is, never duplicate anything. The moment you have a location where data is duplicated (due to caching or replication or two separate monolithic apps, etc), you create a duplicate that will go out of synch in an online situation. So the principle is to avoid doing that.

被违反的中心原则是，永远不要重复任何东西。当您有一个数据被复制的位置(由于缓存或复制或两个单独的单块应用程序等)，您将创建一个在在线情况下不同步的副本。所以原则是避免这样做。

• Sure, for serious third-party software, such as a gruntly report tool, by design, they may well cache server-based data in the client. But note that they have put hundreds of man years into implementing it correctly, with due consideration to the above. Yours is not such a piece of software.
• 当然，对于严肃的第三方软件，比如一个蹩脚的报告工具，根据设计，它们可以很好地在客户端缓存基于服务器的数据。但要注意的是，他们已经将数百年的时间用于正确地实施它，并适当地考虑到上述情况。你的不是这样的软件。

Rather than providing a lecture on the principles that must be understood, or the evils and costs of each error, the rest of this answer provides the requested what would you do in real life, using the correct architectural method (a step above best practice).

与其提供一个关于必须理解的原则的讲座，或者每一个错误的坏处和代价，其余的答案提供了你在现实生活中要做什么，使用正确的架构方法(超越最佳实践的一步)。

Architecture 1

Do not confuse

不要混淆

• the data
  which must be Normalised
• 数据必须规范化

with

与

• the result set
  which, by definition, is the flattened ("de-normalised" is not quite correct) view of the data.
• 根据定义，结果集是数据的扁平化(“非规范化”不是非常正确)视图。

The data, given that it is Normalised, will not contain duplicate values; repeating groups. The result set will contain duplicate values; repeating groups. That is pedestrian.

考虑到数据是规范化的，因此不会包含重复的值;重复组。结果集将包含重复的值;重复组。这是行人。

• Note that the notion of Nested Sets (or Nested Relations), which is being heavily marketed by the schizophrenics, is based on precisely this confusion.

  注意，嵌套集(或嵌套关系)的概念是由精神分裂症患者大量宣传的，它正是基于这种混淆。

• For fortyfive years since the advent of the RM, they have been unable to differentiate base relations (for which Normalisation does apply) from derived relations (for which Normalisation does not apply).

  自RM出现以来的5年里，他们一直无法区分基础关系(即正常情况适用的)与衍生关系(对于这种关系，正常化并不适用)。

• Two of the freaks are currently mounting an assault on the definition of First Normal Form. This is an assault on the intellect. This would (if accepted) normalise insanity. 1NF is the foundation of the other NFs, if this insanity is accepted, all the NFs will damaged, demeaned, rendered value-less. The result would be that Normalisation itself (sparsely defined in mathematical terms, but clearly understood as a science by professionals) will be severely damaged, if not destroyed.

  两名怪胎目前正在攻击第一范式的定义。这是对智力的攻击。这将(如果被接受)使精神错乱正常化。1NF是其他NFs的基础，如果接受这种疯狂，那么所有的NFs都将受损、降级、失效。结果将是，正常化本身(用数学术语定义的很少，但专业人士清楚地将其理解为一门科学)即使没有被摧毁，也将受到严重损害。

Architecture 2

There is a centuries-old scientific or engineering principle, that content (data) must be separated from control (program elements). This is because the analysis; design; and implementation of the two are completely different. This principle is no less important in the software sciences, where it has specific articulation.

有一个古老的科学或工程原理，内容(数据)必须与控制(程序元素)分开。这是因为分析;设计;这两者的实现是完全不同的。在软件科学中，这一原则同样重要，因为它具有特定的清晰度。

In order to keep this brief (ha ha), instead of a discourse, I will assume that you understand:

为了保持简短(哈哈)，我假设你能理解:

• That there is a scientifically demanded boundary between data and program elements. Mixing them up results in complex objects that are error-prone and hard to maintain.

  在数据和程序元素之间有一个科学要求的界限。将它们混合在一起会产生容易出错且难于维护的复杂对象。

  • The confusion of this principle has reached epidemic proportions in the OO/ORM world, the consequences reach far and wide.

    这一原则的混乱在OO/ORM世界中已经达到了流行的程度，其后果是深远的。

  • Only educated professionals avoid this insanity. For the rest, the great majority, they accept this insanity as "normal", and they spend their lives fixing problems that we simply do not have.

    只有受过教育的专业人士才能避免这种精神错乱。对于其他人，绝大多数人来说，他们接受这种疯狂是“正常的”，他们用一生去解决我们根本没有的问题。

• The architectural superiority, the great value, of data being both stored and presented in Tabular Form per Dr E F Codd's Relational Model. That there are specific rules for Normalisation of data.

  根据Dr ef Codd的关系模型，数据被存储和以表格形式呈现的架构优势、巨大价值。数据正常化有特定的规则。

• And importantly, you can determine when the people in the mad house, who write and market books, advise non-relational or anti-relational methods.

  更重要的是，你可以确定《疯人院》里写书和推销书的人何时会建议非关系型或反关系型方法。

Architecture 3

If you cache data on the client:

如果您在客户端缓存数据:

1. Cache the absolute minimum.

   缓存的绝对最小值。

   That means cache only the data that does not change in the online environment. That means Reference and Lookup tables only, the tables that populate the higher level classifiers, the drop-downs, etc.

   这意味着只缓存在线环境中不更改的数据。这意味着只有引用和查找表、填充高级分类器的表、下拉列表等。

2. Currency

   货币

   For every table that you do cache, you must have a method of (a) determining that the cached data has become stale, compared to the One Version of the Truth which exists on the server, and (b) refreshing it from the server, (c) on a table-by-table basis.

   对于每个缓存的表，必须有一个方法(a)确定缓存的数据已经过时，而与服务器上存在的一个版本的事实相比较，(b)从服务器上刷新它(c)。

   Typically, this involves a background process that executes every (e) five minutes, that queries the MAX updated DateTime for each cached table on the client vs the DateTime on the server, and if changed, refreshes the table, and all its child tables, those that dependent on the changed table.

   通常，这涉及一个每(e) 5分钟执行一次的后台进程，它查询客户端上每个缓存表的最大更新日期时间与服务器上的DateTime，如果更改，刷新表及其所有子表，这些子表依赖于更改的表。

   That, of course, requires that you have an UpdatedDateTime column on every table. That is not a burden, because you need that for OLTP ACID Transactions anyway (if you have a real database, instead of a bunch of sub-standard files).

   当然，这要求在每个表上都有一个UpdatedDateTime列。这并不是一个负担，因为无论如何，OLTP ACID事务都需要它(如果您有一个真正的数据库，而不是一堆子标准文件)。

Which really means, never replicate, the coding burden is prohibitive.

这实际上意味着，永远不要复制，编码负担是令人望而却步的。

Architecture 4

In the sub-commercial, non-server world, I understand that the freaks advise the reverse (insane people always contradict sanity), caching of "everything".

在次商业的、非服务器的世界中，我理解怪胎们的建议是相反的(疯狂的人总是反对理智)，缓存“一切”。

• That is the only way the programs like PusGresQl, produced by their cohorts in the same asylum, can to the used in a multi-user system, the only way that they can spread their cancer.

  这是像PusGresQl这样的程序的唯一方式，这些程序是由他们在同一所收容所的伙伴开发的，可以在多用户系统中使用，也是他们传播癌症的唯一方式。

• You always get what you pay for: you pay peanuts, you get monkeys; you pay zero, you get zero.

  你总是得到你所付出的:你付出花生，你得到猴子;你付零，你得到零。

The corollary to Architecture 3 is, if you do cache data on the client, do not cache tables that change frequently. These are the transaction and history tables. The notion of caching such tables, or all tables, on the client is completely bankrupt.

体系结构3的推论是，如果您在客户机上缓存数据，那么不要缓存频繁更改的表。这些是事务和历史表。在客户机上缓存此类表或所有表的概念已经完全破产。

In a genuine Client/Server deployment, due to use of applicable standards, for each data window, the app should query only the rows that are required, for that particular need, at that particular time, based on context or filter values, etc. The app should never load the entire table.

在真正的客户端/服务器部署中，由于使用了适用的标准，对于每个数据窗口，应用程序应该只查询所需的行，针对特定的需求，基于上下文或筛选值，等等。应用程序不应该加载整个表。

If the same user using the same window inspected its contents, 15 minutes after the first inspection, the data would be 15 mins out of date.

如果同一用户使用同一窗口检查内容，在第一次检查后15分钟，数据将过期15分钟。

• For freeware/shareware/vapourware platforms, which define themselves by the absence of a server architecture, and thus by the result, that performance is non-existent, sure, you have to cache more than the minimum tables on the client.

  对于免费软件/共享软件/vapourware平台来说，由于缺少服务器架构而定义了它们自己，因此，性能是不存在的，当然，您必须缓存客户机上的最小表。

• If you do that, you must take all the above into account, and implement it correctly, otherwise your app will be broken, and the stench will drive the users to seek your termination. If there is more than one user, they will have the same cause, and soon form an army.

  如果你这样做，你必须考虑到以上所有的因素，并正确地实现它，否则你的应用程序将被破坏，恶臭将驱使用户寻求你的终止。如果有超过一个用户，他们将会有相同的原因，并很快组成军队。

Architecture 5

Now we get to how you cache those carefully chosen tables on the client.

现在我们了解了如何在客户机上缓存那些精心选择的表。

Note that databases grow, they are extended.

请注意，数据库在增长，它们是扩展的。

• If the system is broken, a failure, it will grow in small increments, and require a lot of effort.

  如果系统崩溃了，这是一个失败，那么它将以很小的增量增长，并且需要大量的努力。

• If the system is even a small success, it will grow exponentially.

  如果这个系统是一个小小的成功，它将成倍增长。

• If the system (each of the database, and the app, separately) is designed and implemented well, the changes will be easy, the bugs will be few.

  如果系统(每个数据库和应用程序单独)被设计和实现的很好，变化就会很容易，bug也就很少了。

Therefore, all the components in the app must be designed properly, to comply with applicable standards, and the database must be fully Normalised. This in turn minimises the effect of changes in the database, on the app, and vice versa.

因此，应用程序中的所有组件都必须进行适当的设计，以符合适用的标准，并且数据库必须完全规范化。这反过来又能最小化数据库变化对应用程序的影响，反之亦然。

• The app will consist of simple, not complex, objects, which are easy to maintain and change.

  该应用程序将由简单而不复杂的对象组成，易于维护和更改。

• For the data that you do cache on the client, you will use arrays of some form: multiple instances of a class in an OO platform; DataWindows (TM, google for it) or similar in a 4GL; simple arrays in PHP.

  对于在客户机上进行缓存的数据，您将使用某种形式的数组:一个OO平台中的类的多个实例;DataWindows (TM，谷歌)或类似的4GL;简单的PHP数组。

(Aside. Note gravely, that what people in situations such as yours produce in one year, professional providers such as I produce in one week, using a commercial SQL platform, a commercial 4GL, and complying with Architecture and Standards.)

(一边。请注意，在你这样的情况下，人们在一年内生产的产品，像我这样的专业供应商在一周内生产的产品，使用的是商业SQL平台、商业4GL，并且符合体系结构和标准。

Architecture 6

So let's assume that you understand all the above, and appreciate its value, particularly Architecture 1 & 2.

因此，让我们假设您理解以上所有内容，并欣赏其价值，特别是体系结构1和2。

• If you don't, please stop here and ask questions, do not proceed to the below.
• 如果没有，请在这里停下来问问题，不要继续下面的内容。

Now that we have established the full context, we can address the crux of your problem.

既然我们已经建立了完整的上下文，我们就可以解决你问题的关键了。

• In those arrays in the app, why on Earth would you store flattened views of data ?

  在应用程序中的这些数组中，为什么要存储扁平的数据视图呢?

  • and consequently mess with, and agonise over, the problems
  • 结果，他们把问题搞得一塌糊涂，苦不堪言

• instead of storing copies of the Normalised tables ?

  而不是存储正常表格的副本?

Answer

1. Never duplicate anything that can be derived. That is an Architectural Principle, not limited to Normalisation in a database.

   不要重复任何可以派生的东西。这是一种体系结构原则，不仅限于数据库中的规范化。

2. Never merge anything.

   从来没有任何合并。

   If you do, you will be creating:

   如果你这样做，你将创造:

   • data duplication, and masses of it, on the client. The client will not only be fat and slow, it will be anchored to the floor with the ballast of duplicated data.

     客户端上的数据复制和大量数据复制。客户端不仅会变得又胖又慢，还会用重复数据的镇流器固定在地板上。

   • additional code, which is completely unnecessary

     额外的代码，完全没有必要

   • complexity in that code

     这段代码的复杂性

   • code that is fragile, that will constantly have to change.

     代码是脆弱的，它将不断地改变。

   That is the precise problem you are suffering, a consequence of the method, which you know intuitively is wrong, that there must be a better way. You know it is a generic and common problem.

   这就是你所遇到的问题，这个方法的结果，直觉上你知道这是错误的，必须有更好的方法。你知道这是一个普遍的问题。

   Note also that method (the poison that is marketed), that code, constitutes a mental anchor for you. Look at the way that you have formatted it and presented it so beautifully: it is of importance to you. I am reluctant to inform you of all this.

   还要注意的是，方法(市场上的毒药)，那个代码，是你的精神支柱。看看你把它格式化的方式，并把它呈现得如此漂亮:它对你来说很重要。我不愿把这一切告诉你。

   • Which reluctance is easily overcome, due to your earnest and forthright attitude, and the knowledge that you did not invent this method, that you followed "teachers" who are, as evidenced, totally ignorant of the relevant science, who market insanity; non-science; nonsense, as "science".
   • 这种不情愿是很容易克服的，因为你的认真和直率的态度，以及你知道你没有发明这种方法，你跟随了“老师”，他们完全不懂相关的科学，他们是市场疯子;非科学;胡说,是“科学”。

3. In each code segment, at presentation time, as and when required:

   在每个代码段中，在显示时，在需要时:

   a. In the commercial Client/Server context
   Execute a query that joins the simple, Normalised, unduplicated tables, and retrieves only the qualifying rows. Thereby obtaining current data values. The user never sees stale data. Here, Views (flattened views of Normalised data) are often used.

   a.在商业客户端/服务器上下文中执行一个查询，该查询连接简单、规范化、不可重复的表，并只检索符合条件的行。从而获得当前数据值。用户永远不会看到陈旧的数据。在这里，通常使用视图(标准化数据的扁平视图)。

   b. In the sub-commercial non-server context
   Create a temporary result-set array, and join the simple, unduplicated, arrays (copies of tables that are cached), and populate it with only the qualifying rows, from the source arrays. The currency of which is maintained by the background process.

   b.在子商业的非服务器上下文中，创建一个临时的结果集数组，并加入简单的、未重复的数组(缓存的表的副本)，并用源数组中的合格行填充它。由后台进程维护的货币。

   • Use the Keys to form the joins between the arrays, in exactly the same way that Keys are used to form the joins in the Relational tables in the database.

     使用键来形成数组之间的连接，其方式与使用键来形成数据库中的关系表中的连接完全相同。

   • Destroy those components when the user closes the window.

     当用户关闭窗口时，销毁这些组件。

   • A clever version would eliminate the result-set array, and join the source arrays via the Keys, and limit the result to the qualifying rows.

     一个聪明的版本将消除结果集数组，并通过键连接源数组，并将结果限制为符合条件的行。

Separate to being an architectural insanity, Nested Arrays or Nested Sets or JSON or JSON-like structures are simply not required. This is the consequence of confusing the Architecture 1 Principle.

独立于架构错乱，嵌套数组或嵌套的集或JSON或类似JSON的结构是不需要的。这是混淆体系结构1原则的结果。

• If you do choose to use such structures, then use them only for the temporary result-set arrays.
• 如果您选择使用此类结构，那么只对临时结果集数组使用它们。

Last, I trust this discourse demonstrates that n tables is a non-issue. More important, that m levels deep in the data hierarchy, the "nesting", is a non-issue.

最后，我相信这篇文章证明了n个表不是问题。更重要的是，在数据层次结构中m层的“嵌套”不是问题。

Answer 2

Now that I have given the full context (and not before), which removes the implications in your question, and makes it a generic, kernel one.

现在我已经给出了完整的上下文(而不是以前)，它消除了您的问题中的含义，并使它成为一个通用的内核。

The question is about ANY server-side/relational-db. [Which is better]:

问题是任何服务器端/关系数据库。(这是更好的):

2 loops, 5 simple "SELECT" queries

2个循环，5个简单的“选择”查询

1 loop, 1 "JOIN" query

1个循环，1个“连接”查询

The detailed examples you have given are not accurately described above. The accurate descriptions is:

你所给出的详细例子并没有在上面得到准确的描述。准确的描述是:

• Your Option 1 2 loops, each loop for loading each array 1 single-table SELECT query per loop (executed n x m times ... the outermost loop, only, is a single execution)

  您的选项1 2循环，每个循环装载每个数组1单表选择查询每个循环(执行n x m次……)最外层的循环，只有一个执行)

• Your Option 2 1 Joined SELECT query executed once followed by 2 loops, each loop for loading each array

  选项2 1加入SELECT查询，执行一次，然后执行2个循环，每个循环用于加载每个数组

For the commercial SQL platforms, neither, because it does not apply.

对于商业SQL平台来说也不是，因为它不适用。

• The commercial SQL server is a set-processing engine. Use one query with whatever joins are required, that returns a result set. Never step through the rows using a loop, that reduces the set-processing engine to a pre-1970's ISAM system. Use a View, in the server, since it affords the highest performance and the code is in one place.
• 商业SQL服务器是一个集处理引擎。使用任何连接都可以使用一个查询，它返回一个结果集。在服务器中使用视图，因为它提供最高的性能，并且代码在一个地方。

However, for the non-commercial, non-server platforms, where:

但对于非商业、非服务器平台，其中:

• your "server" is not a set-processing engine ie. it returns single rows, therefore you have to fetch each row and fill the array, manually or

  您的“服务器”不是一个集处理引擎。它返回单个行，因此您必须获取每一行并手动填充数组

• your "server" does not provide Client/Server binding, ie. it does not provide facilities on the client to bind the incoming result set to a receiving array, and therefore you have to step through the returned result set, row by row, and fill the array, manually,

  您的“服务器”不提供客户端/服务器绑定。它不提供客户端上的设施来将传入的结果集绑定到接收数组，因此您必须通过返回的结果集，按行逐行，并手动填充数组，

as per your example then, the answer is, by a large margin, your option 2.

根据你的例子，答案是，你的选择余地很大。

Please consider carefully, and comment or ask questions.

请仔细考虑，并提出意见或问题。

Response to Comment

Say i need to print this json (or other html page) to some STOUT (example: an http response to: GET /allUsersPhoneNumbers. it's just an example to clarify what i'm expecting to get), should return this json. i have a php function that got this 2 result sets (1). now it should print this json - how should i do that?. this report could be an employee month salary for a whole year, and so one. one way or anther, i need to gather this information and represent it in a "JOIN"ed representation

比方说，我需要将这个json(或其他html页面)打印到一些粗体(示例:http响应:GET /allUsersPhoneNumbers)。这只是一个例子来说明我希望得到什么)，应该返回这个json。我有一个php函数，它有2个结果集(1)现在它应该打印这个json -我该怎么做?这份报告可以是一个员工一个月的薪水，一年的薪水。无论如何，我需要收集这些信息并将其表示为“JOIN”ed表示

Perhaps I was not clear enough.

也许我还不够清楚。

1. Basically, do not use JSON. Unless you absolutely have to. Which means sending to some system that requires it, which means that receiving system, and that demand, is very, very, stupid.

   基本上，不要使用JSON。除非你必须这么做。这意味着发送到需要它的系统，这意味着接收系统，和需求，是非常非常非常愚蠢的。

2. Make sure that your system doesn't make such demands on others.

   确保你的系统不会对其他人提出这样的要求。

3. Keep your data Normalised. Both in the database, and in whatever program elements that you write. That means (in this example) use one SELECT per table or array. That is for loading purposes, so that you can refer to and inspect them at any point in the program.

   保持你的数据正常。无论是在数据库中，还是在您编写的任何程序元素中。这意味着(在本例中)每个表或数组使用一个SELECT。这是为了加载目的，以便您可以在程序的任何位置引用和检查它们。

4. When you need a join, understand that it is:

   当您需要加入时，请理解它是:

   • a result-set; a derived relation; a view
   • 一个结果集;派生的关系;一个视图
   • therefore temporary, it exists for the duration of the execution of that element, only
   • 因此，它是临时的，仅在该元素执行期间存在

   a. For tables, join them in the usual manner, via Keys. One query, joining two (or more) tables.

   a.对于表，按照通常的方式通过键连接它们。一个查询，连接两个(或多个)表。

   b. For arrays, join arrays in the program, the same way you join tables in the database, via Keys.

   b.对于数组，在程序中加入数组，就像在数据库中通过键连接表一样。

5. For the example you have given, which is a response to some request, first understand that it is the category [4], and then fulfil it.

   对于您给出的示例，它是对某个请求的响应，首先要理解它是[4]类别，然后完成它。

Why even consider JSON ???
What has JSON got to do with this ???

为什么还要考虑JSON ?JSON和这个有什么关系???

JSON is misunderstood and people are interested in the wow factor. It is a solution looking for a problem. Unless you have that problem it has no value. Check these two links:
Copter - What is JSON
* - What is JSON

JSON被误解了，人们对wow因素很感兴趣。这是一个寻找问题的解决方案。除非你有那个问题，否则它没有价值。检查这两个链接:Copter -什么是JSON * -什么是JSON

Now if you understand that, it is mostly for incoming feeds. Never for outgoing. Further, it requires parsing, deconstructing, etc, before the can be used.

如果您理解了这一点，那么它主要用于传入的提要。从来没有为外向。此外，它还需要解析、解构等，然后才能使用。

Recall:

回忆:

i need to gather this information and represent it in a "JOIN"ed representation

我需要收集此信息并将其表示为“JOIN”ed表示

Yes. That is pedestrian. Joined does not mean JSONed.

是的。这是行人。联合并不意味着孤立。

In your example, the receiver is expecting a flattened view (eg. spreadsheet), with all the cells filled, and yes, for Users with more than one PhoneNumber, their User details will be repeated on the second nad subsequent result-set row. For any kind of print, eg. for debugging, I want a flattened view. It is just a:

在您的示例中，接收方希望看到一个扁平的视图(例如。电子表格)，填充了所有单元格，是的，对于拥有多个PhoneNumber的用户，他们的用户详细信息将在第二个nad后续的结果集行中重复。任何一种印刷品。对于调试，我需要一个平面化的视图。它只是一个:

    SELECT ... FROM Person JOIN PhoneNumber

And return that. Or if you fulfil the request from arrays, join the Person and PhoneNumber Arrays, which may require a temporary result-set array, and return that.

并返回它。或者，如果您完成了来自数组的请求，请加入Person和PhoneNumber数组(可能需要临时的结果集数组)，并返回该数组。

please don't tell me you should get only 1 user at a time, etc. etc.

请不要告诉我你一次只能获得一个用户，等等。

Correct. If someone tells you to regress to procedural processing (ie. row by row, in a WHILE loop), where the engine or your program has set processing (ie. processes an entire set in one command), that marks them as an idiot. Stop listening to them completely.

正确的。如果有人让你退回到程序处理(即。逐行，在WHILE循环中)，引擎或程序已经设置了处理(即。在一个命令中处理整个集合)，把它们标记为傻瓜。不要完全听他们的。

I have already stated, your Option 2 is correct, Option 1 is incorrect. That is as far as the GET or SELECT is concerned.

我已经说过，你的选项2是正确的，选项1是错误的。这是关于GET或SELECT的内容。

On the other hand, for programming languages that do not have set-processing capability (ie. cannot print/set/inspect an array in a single command), or "servers" that do not provide client-side array binding, you do have to write loops, one loop per depth of the data hierarchy (in your example, two loops, one for Person, and one for PhoneNumber per User).

另一方面，对于没有集处理能力的编程语言(即。不能在单个命令中打印/设置/检查数组)或不提供客户端数组绑定的“服务器”，您必须编写循环，每个数据层次结构深度一个循环(在您的示例中，两个循环，一个为Person，一个为每个用户的PhoneNumber)。

• You have to do that to parse an incoming JSON object.
• 必须这样做才能解析传入的JSON对象。
• You have to do that to load each array from the result set that is returned in your Option 2.
• 必须这样做才能从选项2中返回的结果集中加载每个数组。
• You have to do that to print each array from the result set that is returned in your Option 2.
• 必须这样做才能从选项2中返回的结果集中打印每个数组。

Response to Comment 2

iv'e ment i have to return a result represented in a nested version (lets say i'm printing the report to the page), json was just an example for such representation.

我必须返回一个嵌套版本中表示的结果(假设我将报告打印到页面中)，json只是这种表示的一个例子。

I don't think you understand the reasoning and the conclusions I have provided in this answer.

我认为你不理解我在这个答案中给出的推理和结论。

• For printing and displaying, never nest. Print a flattened view, the rows returned from the SELECT per Option 2. That is what we have been doing, when printing or displaying data Relationally, for 31 years. It is easier to read, debug, search, find, fold, staple, mutilate. You cannot do anything with a nested array, except look at it, and say gee that is interesting.
• 对于打印和显示，不要嵌套。打印一个平面视图，从SELECT per选项2返回的行。这就是我们在打印或显示数据关系的31年里一直在做的事情。它更容易阅读、调试、搜索、查找、折叠、装订、毁损。你不能对嵌套数组做任何事情，除了看看它，然后说，这很有趣。

I was giving you guidance, such that you can write the required code, but that is not working. It is looking like I have to write the code for you.

我给了你指导，这样你就可以编写所需的代码，但这是行不通的。看来我得为你写代码了。

Code

Caveat

I would prefer to take your code and modify it, but actually, looking at your code, it is not well written or structured, it cannot be reasonably modified. Second, if I use that, it would be a bad teaching tool. So I will have to give you fresh, clean code, otherwise you will not learn the correct methods.

我更愿意使用您的代码并对其进行修改，但是实际上，看看您的代码，它不是很好地编写或结构化的，它不能合理地进行修改。第二，如果我使用它，那将是一个糟糕的教学工具。所以我将不得不给你新鲜，干净的代码，否则你将不会学到正确的方法。

This code examples follow my advice, so I am not going to repeat. And this is way beyond the original question.

这些代码示例遵循我的建议，所以我不会重复。这远远超出了最初的问题。

• Query & Print

  查询和打印

  Your request, using your Option 2. One SELECT executed once. Followed by one loop. Which you can "pretty up" if you like.

  您的请求，使用您的选项2。一个选择执行一次。紧随其后的是一个循环。如果你喜欢的话，你可以“漂亮”。

#2

In general it is a best practice to grab the data you need in as few trips to the database as possible then map the data into the appropriate objects. (Option 2)

一般来说，在尽可能少的访问数据库的情况下获取所需的数据是最佳实践，然后将数据映射到适当的对象中。(选择2)

But, to answer your question I would ask yourself what the use case for your data is. If you know for sure that you will be needing your person and your phone number data then I would say the second method is your best option.

但是，为了回答你的问题，我会问你自己数据的用例是什么。如果你确定你需要你的人和你的电话号码数据，那么我认为第二种方法是你最好的选择。

However, option one can also have its use case when the joined data is optional.One example of this could be that on the UI you have a table of all your people and if a user wants to see the phone number for a particular person they have to click on that person. Then it would be acceptable to "lazy-load" all of the phone numbers.

但是，当加入的数据是可选的时，选项1也可以有它的用例。其中一个例子是，在UI上你有一张你所有人的表格如果用户想要看到某个人的电话号码他们必须点击那个人。然后，可以接受“延迟加载”所有电话号码。

#3

This is the common problem, especially if you are creating a WebAPIs, converting those table sets to nested arrays is a big deal..

这是一个常见的问题，尤其是在创建webapi时，将这些表集转换为嵌套数组是很重要的。

I always go for you the second option(in slightly different method though), because the first is worst possible way to do it... One thing I learned with my experience is never query inside a loop, that is a waste of DB calls, well you know what I trying to say.

我总是给你第二种选择(虽然方法略有不同)，因为第一种方法是最糟糕的。根据我的经验，我学到的一件事是绝不在循环中查询，这是对DB调用的浪费，你知道我想说什么。

Although I don't accept all the things PerformanceDBA said, there are two major things I need the address, 1. Don't have duplicate data 2. Fetch only data you want

尽管我不接受PerformanceDBA说的所有东西，但我需要两个主要的东西:1.地址。不要有重复的数据2。只获取您想要的数据

The Only problem I see in Joining the table is, we end up duplicating data lots of them, take you data for example, Joining Person ans phoneNumber tables we end up duplicating Every person for each of his phone number, for two table with few hundred rows its fine, imagine we need to merge 5 tables with thousands of rows its huge... So here's my solution:
Query:

加入表中唯一的问题我明白了,我们最终复制数据很多,以你的数据为例,加入人ans phoneNumber表我们最终复制每个人对于每一个他的电话号码,两个表有几百行精美,想象我们需要合并5表数千行其庞大……我的解决方案是:查询:

SELECT id, fullName From Person;
SELECT personId, phoneNumber FROM phoneNumbers 
WHERE personId IN (SELECT id From Person);

So I get to tables in my result set, now I assign Table[0] to my Person list, and use a 2 loops to place right phoneNumbers in to right person...

因此，我在结果集中找到了表，现在我将表[0]分配给我的Person列表，并使用两个循环将正确的phoneNumbers放在正确的Person中……

Code:

代码:

personList = ConvertToEntity<List<Person>>(dataset.Table[0]);    
pnoList = ConvertToEntity<List<PhoneNumber>>(dataset.Table[1]);

    foreach (person in personList) {
        foreach (pno in pnoList) {
            if(pno.PersonId = person.Id)
                person.PhoneNumer.Add(pno)
        }
    }

I think above method reduce lots of duplication and only get me what I wanted, if there is any downside to the above method please let me know... and thanks for asking these kind of questions...

我认为上面的方法减少了很多重复，只得到我想要的，如果上面的方法有任何缺点，请让我知道……谢谢你提出这些问题……