Just a matter of taste or the need of a formal approach?


thanks for reading this post. At the moment, I am thinking about the
following problem:

Let's assume the following situation:

I've got two entity types called "User" and "Group". Each entity type
got some attributes, let's think about "name" and "password" for
"User" and "name" as well as "type" for "Group". Those attributes are
not really essential for my problem description.

So now we have:

+ name
+ password

+ name
+ type

We can also create relations between those entities, for example:
membership(Gx, Uy), which tells us that user y is a member of group x.
As all users can join multiple groups, we could just create an
intermediate join table to model this for our relational database, so
far, that's all right, there are no other reasonable solutions, as far
as I know.

Now, let's assume you could dynamically add an arbitrary number of
attributes, which can of course also be just foreign keys, to each
entity and that this is also supported by your database system. This
is the case, I am dealing with and it brings in some new thoughts
about modeling the relation between "User" and "Group".

If we look at the relation "membership" again, you could also think:
Well, somehow, "member" could be an attribute of "Group", so we would

+ name
+ type
+ member_fk_1 (or user_fk, that's not essential here)
+ member_fk_2 (also the naming is not essential)

And you could say, well: the fact, that a user is a member of a group,
could also be represented by further "User" attributes:

+ name
+ password
+ group_fk_1 (the naming again is not essential here)
+ group_fk_2

The fact, that you can add as many fields as you want, makes both
solutions possible. In fact, only one entity should save the
membership information, as we are following the dry principle.

What to do now? How would you argue? Should I use a join model anyhow
or should I really decide to model the membership in the second way?
Maybe: Is there a formal approach to decide this?

There are other examples, where I have got to model some kind of
composition: in this case I have got entities which are components of
other entities, which only can exist through out these associations.
In this case, it is somehow more intuitive to model the foreign keys
directly as attributes not using a join model.

Thanks for you suggestions!


Investigate polymorphic join relationships... you can join arbitrary
numbers of other 'things' without adding foreign_keys to your base table
(User). A new 'thing' just has to participate in the poly join

Requirement (these can be related to any number of other entities,
depending on how that project team manages their data)
  has_many :reqlinks, :dependent => :destroy
  has_many :unittests, :through => :reqlinks, :source => :unittest,
:conditions => "reqlinks.reqlinkable_type = 'unittest'"
  has_many :scenarios, :through => :reqlinks, :source => :scenario,
:conditions => "reqlinks.reqlinkable_type = 'scenario'"
  has_many :projects, :through => :reqlinks, :source => :project,
:conditions => "reqlinks.reqlinkable_type = 'project'"

Reqlink (the polymorphic join table)
  belongs_to :requirement
  belongs_to :reqlinkable, :polymorphic => true
  belongs_to :unittest, :class_name => 'Unittest', :foreign_key =>
  belongs_to :scenario, :class_name => 'Scenario', :foreign_key =>
  belongs_to :project, :class_name => 'Project', :foreign_key =>

  has_many :reqlinks, :as => :reqlinkable, :dependent => :destroy
  has_many :requirements, :through => :reqlinks

  has_many :reqlinks, :as => :reqlinkable, :dependent => :destroy
  has_many :requirements, :through => :reqlinks

  has_many :reqlinks, :as => :reqlinkable, :dependent => :destroy
  has_many :requirements, :through => :reqlinks