0

I am developing a social network for an intranet, and I came across a problem [?]. I have the entities User and Business as main entities of the network.

Note: A Business does not have, necessarily, a relationship with a User.

Following this idea, I have a group table, and a group can be created either by a user or by a business, there comes a question, how can I make the author field in this table?

I did the following, I created a table type, with the following data:

 id | name
  1   user
  2   business

And my table groups like this:

 id
 name
 description
 tid (FK -> type.id)
 author (INT)

Thus, if a group has a tid equals to 1, means that the author is referring to a User, if it have tid equals to 2, it is referring to a Business.

What do you think about this implementation? It is correct? What can I do to improve it?

I'm using PHP 5.3.6 (Zend Framework and Doctrine2) + MySQL 5.1.

jonathancardoso
  • 11,737
  • 7
  • 53
  • 72
  • The terminology for this is "polymorphic". Bill Karwin had a good answer that I can't find at the moment about it. From a database design perspective, I've been told the logical model term for this is an "arc" relationship. Either way, I see no issues with what you posted, other than supporting multiple authors. – OMG Ponies Jul 05 '11 at 22:05
  • 2
    @OMG Ponies, Jonathan: http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables/441111#441111 – webbiedave Jul 05 '11 at 22:09
  • Thansk @OMG Ponies and @webbiedave I did not know exactly what was the name given to this. Apparently most of my tables will break the first and third normal form. But it is the only way to get what I want. And from what I saw, is pretty much what I'm doing, I'm glad that Doctrine supports this natively. – jonathancardoso Jul 05 '11 at 23:01
  • also http://dba.stackexchange.com/q/13859/630 and http://dba.stackexchange.com/q/4278/630 – gbn Apr 02 '12 at 04:44

2 Answers2

2

Per request:

The terminology for this is "polymorphic". Bill Karwin had a good answer that I can't find at the moment about it. From a database design perspective, I've been told the logical model term for this is an "arc" relationship. Either way, I see no issues with what you posted, other than supporting multiple authors.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
1

The bad thing about this is that you cannot use the foreign key constraints to check and verify your data if you do this.

I would recommend using 2 tables instead. one to store the relationship between user and group, and one to store the relationship between business and group.

This will allow you to use all the fk benefits like cascading deleted when the group or business or user is deleted.

iWantSimpleLife
  • 1,944
  • 14
  • 22