Postgresql – table bloating in databases

In any case, when one thing hasMany of another how/where does it show up in application and database logic?

The only way I can think to answer this question is with another example of hasMany that may or may not answer your question.

err, except I should have called the table "comment" but I think the point stands.

I don't know that this really helps you understand anymore than what you already had.

So I'll also share a query that might demonstrate how this works:

declare @var char(5)
set @var = 'query'

select
   c.id
  ,c.text
  ,c.author
from comment c
inner join post p
   on p.id = c.parentid

which we expect to return many rows, because one post generally has many comments (in theory, of course not all posts will have comments)

Update:

So here's the thing about relations: They are EASY to model via diagram (see the very first tool I pulled out to answer this question) but they are HARD to diagram via text. So instead of trying to define whether we mean 1:M or 0:N, we just write it in "near English" and say what we intend. Here's some alternatives:

one to one           hasOne               post hasOne author (not true for CW ;])
one to many          hasMany              The one in question
many to one          belongsTo            parent child, comments belongTo post
many to many         hasAndBelongsToMany  This describes a complex relationship
many to many to many hasManyAndBelongsToMany

As you see they can get quite complicated. This would ... once again! ... indicate a need to refactor your business logic into simpler and smaller chunks.

These can also be called "association types" and may be indicated by verbage like "WrittenBy" (a many to one) or "IsOwnedBy" (one to one? - context is key)

Before I walk away from the "hasMany" concept for the last time (well this is an update, more will likely be written) I want to mention that while it's useful for "plain English descriptions", it's also used in ActiveRecord styled installations. Take, for instance, Ruby. http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html The rest of this post (so far) is taken from that page:

class Project < ActiveRecord::Base
  belongs_to              :portfolio
  has_one                 :project_manager
  has_many                :milestones
  has_and_belongs_to_many :categories
end

This allows the ActiveRecord ORM to create the data model and to maintain the relationships as specified by the business logic within the program. But I don't write Ruby or Rails so that's as far as I can tell you, I just know that it exists there.

A cursor is essentially a materialized result set from a query that maintains its own state (essentially remembers what row is "current") and in some cases allows you to manipulate the current row.

Cursor lifecycle looks more or less like this:

1. Open the cursor. At this point the query that defines the cursor is executed.
2. Position the cursor. After the cursor is opened, it is positioned before the first row of the result set, so one needs to move it to the first row. In the simplest case you can only move the cursor to the "next" row, but in some cases you could go to the "previous" or even to an arbitrary row of the result set, depending on the parameters used to open the cursor and whether the particular DBMS supports these kinds of cursors.
3. Fetch from the cursor. This operation assigns values from the columns of the "current" row to the application variables.
4. Update columns in the current row or delete the current row, if the particular cursor allows such manipulation.
5. Close the cursor. All resources used by the result set are freed at this point.

Typically steps 2-4 are performed in an application program (or stored procedure) loop, allowing you to read each record of the result set and do something with it if necessary.