为什么在世界上我会have_many关系？ [英] Why in the world would I have_many relationships?

问题描述

我只是碰到了对关系和数据库的一个有趣的情况。我写一个Ruby应用程序，并为我的数据库，我使用PostgreSQL。我有一个父对象用户和相关对象一样的东西，其中用户可以有一个或多个一样的东西。什么是使用一个单独的表VS只是嵌入领域内的父表中的数据的优势在哪里？

I just ran into an interesting situation about relationships and databases. I am writing a ruby app and for my database I am using postgresql. I have a parent object "user" and a related object "thingies" where a user can have one or more thingies. What would be the advantage of using a separate table vs just embedding data within a field in the parent table?

这是ActiveRecord的例子：

Example from ActiveRecord:

使用相关表：

def change
    create_table :users do |i|
        i.text :name
    end
    create_table :thingies do |i|
        i.integer :thingie
        i.text :discription
    end
end
class User < ActiveRecord::Base
    has_many :thingies
end
class Thingie < ActiveRecord::Base
    belongs_to :user
end

使用嵌入式数据结构（多维数组）方法：

using an embedded data structure (multidimensional array) method:

def change
    create_table :users do |i|
        i.text :name
        i.text :thingies, array: true # example contents: [[thingie,discription],[thingie,discription]]
    end
end
class User < ActiveRecord::Base
end

相关信息

我使用Heroku和Heroku上，posgres为我的数据库。我使用他们的自由选择，这限制了我10,000行。这似乎让我想用多维数组的方式，但我真的不知道。

I am using heroku and heroku-posgres as my database. I am using their free option, which limits me to 10,000 rows. This seems to make me want to use the multidimensional array way, but I don't really know.

推荐答案

在一个字段中嵌入的数据结构可以简单的情况下工作，但它$采取的关系数据库优势p $ pvents你。关系数据库设计用于查找，更新，删除和保护您的数据。随着嵌入式领域包含了自己的WAD-O-数据（数组，JSON，XML等），你风写的所有code自己做。

Embedding a data structure in a field can work for simple cases but it prevents you from taking advantage of relational databases. Relational databases are designed to find, update, delete and protect your data. With an embedded field containing its own wad-o-data (array, JSON, xml etc), you wind up writing all the code to do this yourself.

有哪里嵌入式领域可能是更合适的情况下，但对于这个问题作为一个例子，我将使用突出了相关表approch优点的情况下。

There are cases where the embedded field might be more suitable, but for this question as an example I will use a case that highlights the advantages of a related table approch.

想象一下，一个用户和后例如一个博客。

Imagine a User and Post example for a blog.

对于一个嵌入式的解决方案后，你将有一个表是这样的（伪code - 这可能是无效的DDL）：

For an embedded post solution, you would have a table something like this (psuedocode - these are probably not valid ddl):

create table Users {
id int auto_increment,
name varchar(200)
post text[][],
}

通过相关的表，你会做这样的事情

With related tables, you would do something like

create table Users {
id int auto_increment,
name varchar(200)
}
create table Posts {
id auto_increment,
user_id int,
content text
}

对象关系映射（ORM）工具：随着嵌入式后，你会写code手动帖子添加到用户，浏览现有职位，对其进行验证，删除等有了单独的表的设计，您可以利用ActiveRecord的（或任何对象，你使用的是关系系统），这应该让你的code更简单的工具。

Object Relational Mapping (ORM) tools: With the embedded post, you will be writing the code manually to add posts to a user, navigate through existing posts, validate them, delete them etc. With the separate table design, you can leverage the ActiveRecord (or whatever object relational system you are using) tools for this which should keep your code much simpler.

灵活性：想象一下，你想一个日期字段添加到岗位。您可以与嵌入式领域做到这一点，但你必须写code解析阵列，验证等领域，更新现有的嵌入式职位等有了单独的表，这是非常简单的。另外，假设你要添加编辑到您的系统谁批准所有的职位。随着关系的例子，这是容易的。作为一个例子来寻找'鲍勃'与ActiveRecord的编辑的所有帖子，你只需要：

Flexibility: Imagine you want to add a date field to the post. You can do it with an embedded field, but you will have to write code to parse your array, validate the fields, update the existing embedded posts etc. With the separate table, this is much simpler. In addition, lets say you want to add an Editor to your system who approves all the posts. With the relational example this is easy. As an example to find all posts edited by 'Bob' with ActiveRecord, you would just need:

Editor.where(name: 'Bob').posts

对于嵌入式侧，你会写code穿行在数据库中的每个用户，分析每个人自己的岗位，并期待为鲍勃在编辑栏中。

For the embedded side, you would have to write code to walk through every user in the database, parse every one of their posts and look for 'Bob' in the editor field.

性能：假设您有10,000个用户，平均每100个职位。现在，你想找到一个特定日期所做的一切职务。随着嵌入式领域，你必须遍历每个记录，分析所有职位的整个数组，提取日期，并检查agains一个你想要的。这将咀嚼CPU和磁盘I / 0。对于数据库，你可以很容易地指数的日期字段，然后拉出你所需要的确切记载，而不从每个用户的解析每一个岗位。

Performance: Imagine that you have 10,000 users with an average of 100 posts each. Now you want to find all posts done on a certain date. With the embedded field, you must loop through every record, parse the entire array of all posts, extract the dates and check agains the one you want. This will chew up both cpu and disk i/0. For the database, you can easily index the date field and pull out the exact records you need without parsing every post from every user.

标准：使用供应商特定的数据结构，是指移动应用程序到另一个数据库可能是一种痛苦。 Postgres的似乎有一组丰富的数据类型，但它们是不一样的MySQL，甲骨文，SQL服务器等，如果你坚持使用标准的数据类型，你将有一个更容易的时间交换后端。

Standards: Using a vendor specific data structure means that moving your application to another database could be a pain. Postgres appears to have a rich set of data types, but they are not the same as MySQL, Oracle, SQL Server etc. If you stick with standard data types, you will have a much easier time swapping backends.

这是我看到过顶的主要问题。我已经犯了这个错误付出了代价，所以除非有超令人信服的理由不这样做，否则，我会使用单独的表。

These are the main issues I see off the top. I have made this mistake and paid the price for it, so unless there is a super-compelling reason do do otherwise, I would use the separate table.

这篇关于为什么在世界上我会have_many关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！