[Dbix-class] Relationship question/database structure

[Rob Kinyon]

On Mon, Jan 10, 2011 at 13:54, David Ihnen <davidihnen at gmail.com> wrote:
> Yes.  That was what i was typing basically when Mr Kinyon's message came
> through.  Quite right, sir, this is the sort of approach I would take as
> well.  Each column only has one type of data in it.  There are more tables
> for cross reference, but thats the hit you take for not putting multiple key
> columns in the notes table or as an alternative creating separate notes
> tables for each type.

Umm ... kinda. This isn't a "hit". It's the actual proper model of
your data. There is an "order" and there is a "line_item" and each of
them could have a note. In addition, a single note could be associated
with both an order AND a line_item or with 3 orders and 2 line_items.
This is considered a feature.

Furthermore, by abstracting out the notes concept, you allow notes to
have additional things, like attachments. Now, magically, all things
that can have notes can also have uploaded attachments. Or users
associated with them. Or dates. Or whatever else you have your notes
work with.

Many-to-many associations (they aren't relationships, properly) are an
excellent way of making your database dance, sing, and make perfect
julienne fries. I don't know why people freak out when they see them.

Rob