Why in the world would I have_many relationships?

Question

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?

Example from ActiveRecord:

using a related table:

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

Relevant Information

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.

Answer 1

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.

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.

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
}

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.

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

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.

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.

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.

Answer 2

what if users John and Ann have the same thingies? the records will be duplicated and if you decide to change the name of thingie you will have to change two or more records. If thingie is stored in the separate table you have to change only one record. FYI https://en.wikipedia.org/wiki/Database_normalization