hasOne WTF?

[Dutch Rapley]

Disclaimer: I'm not trying to provoke anyone here, I just want to offer some food for thought based upon past experience.

I haven't used Reactor yet. I've chosen to work with/on Reactor because I like the Active Record design pattern.

I've started going through the documentation, and found something I really don't like about hasOne. The foreign key is actually stored in the parent table.

http://livedocs.reactorframework.com/hasOne_tag.htm
http://livedocs.reactorframework.com/hasMany.htm

The hasMany documentation goes on to say "There are two ways that hasMany relationships can be configured.  The first is just like hasOne relationships except in the opposite direction.  Rather than the parent object having a foreign key to the child object, the child object has the foreign key to the parent."

Let's forget about Reactor for a moment. Let's forget about ColdFusion. Let's think only about database design.

A foreign key should only be added to a table if the record "belongs" to a record in another table. Again, a foreign key should only be added to a table if the record "belongs" to a record in another table.

The biggest argument for this is cascading deletes.

EXAMPLE (the proper way)

Employees

• id
• firstName
• lastName

Addresses

• id
• street
• city
• state
• zip
• employeeID

For this instance, let's just say that an employee has one address. The address clearly belongs to the employee. I can add a cascading delete (in the database) to the relationship, when an employee is deleted, then the address that belongs to that employee is also deleted. If I like, I can delete an address without affecting the employee record. The cascading delete prevents orphaned addresses. (granted, I'm going to also assume cascading deletes are built into reactor)

EXAMPLE (the way proposed in the docs)

Employees

• id
• firstName
• lastName
• addressID
  

Addresses

• id
• street
• city
• state
• zip

I can add a relationship here in the database, just like I did before. If I were to add a cascading delete (in the database), I have three problems: 1) if I delete an employee record, I now have an orphaned address record, 2) if I delete and address record, the employee record will also be deleted, and 3) you can create orphan address records - create an address record without having an employee record present.

This asserts my belief that good database design dictates that the foreign key should reside on the child record.

Back to Reactor.

My biggest beef with hasOne (other than what is already stated) is the statement in the hasMany doc: (paraphrased) "hasMany is just like hasOne, but in the opposite direction."

What the documentation should say is: "hasMany is just like hasOne, but it doesn't limit you to just one record." You might see where I'm going with this.

Let's say we build an application where there requirement changes, and multiple addresses need to be stored for each employee. We no longer have to reengineer our database, we can simply change our hasOne reference in the reactor config file to hasMany. This has nothing to do with how our application handles our relationships, but more to do with how we architected our database.

I think we really don't even need to do anything to reactor, but really just chagne the documentation to address conventions. Couldn't I then declare my hasOne relationship as such?

<object name="Employee">

   <hasOne name="Address">
      <relate from="id (parent PK)" to="employeeID (child FK)" />

   </hasOne>
</object>


After writing and thinking about his a little more, my beef isn't really how Reactor handles hasOne, but really how the documentation tells someone how they should design their databases.


From my understanding, Reactor should still work the same with the way I declared my relationship above.

personally, I'd like to see something like the following, but this is just me:

<object name="Employee" primaryKey="id">

   <hasOne name="Address" foreignKey="employeeID" />

</object>


Discuss!

-Dutch

[spills]

> I've started going through the documentation, and found something I really
> don't like about hasOne. The foreign key is actually stored in the parent
> table.

I actually don't think this is a problem for this type of relationship
at all, maybe the example could be better i.e.

Address

- id
- street
- city
- stateID
- zip

State

-id
-stateName
-stateAbrv
-stateTaxRate

In my example I don't see that a cascade delete would be an issue.

> EXAMPLE (the proper way)
>
> Employees
>

>    - id
>    - firstName
>    - lastName
>
> Addresses
>
>    - id
>    - street
>    - city
>    - state
>    - zip
>    - employeeID

I would argue that the "proper way" in this example would be to remove
employeeID from the address table and use a linking table to join
address to employee as most likely you will have multiple addresses as
employees will update addresses info or perhaps you have two employees
with the same address?

Just my 2cents

[Dutch Rapley]

> I've started going through the documentation, and found something I really
> don't like about hasOne. The foreign key is actually stored in the parent
> table.

I actually don't think this is a problem for this type of relationship
at all, maybe the example could be better i.e.

Address

  - id
  - street
  - city
  - stateID
  - zip

State

   -id
   -stateName
   -stateAbrv
   -stateTaxRate

In my example I don't see that a cascade delete would be an issue.

I'm not talking about a cascade delete from a reactor perspective, but from a database perspective.

I do like the example you provided. An address has one state. Your list of states will most likely be static and will hardly ever change. But, while the address "has one" state, it doesn't really own a state, it "shares" it with other addresses. Granted, I wouldn't place the address id in the State table in this example. I would tend to think the ownership logic would be like the following: "The Address has one State through Address_State." Also, you wouldn't add a cascading delete on the State table. You see, while an address may have only one state, a state doesn't own the address, the address still belongs to another entity. The state is simply a table that contains a list of items.

Address

  - id
  - street
  - city

  - zip

Address_State
  - addressID
  - stateID

State

   -id
   -stateName
   -stateAbrv
   -stateTaxRate

I realize that this really comes down to preference and everybody has their own way of doing things.

Most importantly, why we all have our preferred ways of doing things (which is never right or wrong), but it's important to open up to other ways to accomplish a common task. We can always learn new tricks.

Mark made a good point a today on his blog post, "Since Ray seems to be getting into Groovy I thought to myself that I might be good with CF and ok with Java, I really needed to learn some other technologies. This helps me get my thinking hat on even when I am coding in CF." It reminded me of what I learned by working with Rails some last year.

As CF developers, it doesn't hurt to look into and work with other programming languages. You may be surprised by what you learn. Rails made me a better CF programmer. By working with Ruby, I learned better approaches to solving problems. Rails helped me better organize the apps I was writing with Fusebox at the time, and even inspired me to write a plugin to automagically call my display templates. My experience with Ruby also came in handy as I ventured to working with Flex and ActionScript earlier this year.

I also realize no one on this list knows who I am, other than this random CF dude who has a hard-on to work on the Reactor documentation. I'm not trying to rub anyone the wrong way by the comments I make. If I come off as brash, I apologize. Just think of it as me playing the devil's advocate.

If I call out a specific approach on this list, it isn't to say "that's wrong!" but to say, "Hey, here's a way I'd aprroach it."

Carry on.

-Dutch

[spills]

> I'm not talking about a cascade delete from a reactor perspective, but from
> a database perspective.

That is what I was trying illustrate with a "static" table like states
- I have a hard time finding very many "has one" examples in my DB's
that aren't static in nature and db cascade updates/deletes etc. are a
non-issue.

> I do like the example you provided.

Thanks

> I realize that this really comes down to preference and everybody has their
> own way of doing things.

Only like a million ways of doing stuff

> Most importantly, why we all have our preferred ways of doing things (which
> is never right or wrong), but it's important to open up to other ways to
> accomplish a common task. We can always learn new tricks.

I am constantly amazed at how much there is to learn and how I seem to
know less the older I get!

> As CF developers, it doesn't hurt to look into and work with other
> programming languages. You may be surprised by what you learn. Rails made me
> a better CF programmer. By working with Ruby, I learned better approaches to
> solving problems. Rails helped me better organize the apps I was writing
> with Fusebox at the time, and even inspired me to write a plugin to
> automagically call my display templates. My experience with Ruby also came
> in handy as I ventured to working with Flex and ActionScript earlier this
> year.

I've never really liked the term "CF Developers", it makes it sound
like we are designing Coldfusion itself, maybe in a indirect way that
is true but I prefer to call myself a web developer that happens to
use CF because it is a good business choice for the way I do things. I
am not tied to any one technology and like you want to expand my
horizons.

> I'm not trying
> to rub anyone the wrong way by the comments I make. If I come off as brash,
> I apologize. Just think of it as me playing the devil's advocate.

I don't think you have anything to "apologize" for and certainly
didn't mean to imply anything with my response. We are merely having a
conversation that hopefully has some value. IMHO a lot CF lists are
way too "quiet"!

[Dutch Rapley]

I am constantly amazed at how much there is to learn and how I seem to

know less the older I get!

Ditto. The more I learn, the more I learn what I don't know. 

 

When I first started building web applications, I used to think it would be hard to learn client app development. Now looking at the plethora of options we can choose from in building a web application, I imagine that it all looks daunting to anyone new to the industry. It's really grown up over the last decade and I'm glad I started when I did, and not now. I'm still learning new things daily which keeps it fun. Being a programmer, at least a good one, requires dedication to a lifetime of learning.