1

How would i go about creating a relationship for photo albums that belong to either a user, event, group, network, page (I want to support x number of possible owners)? If possible even support multiple owners lateron.

One way is to have columns in the album table for these with one of them have a FK ID and rest be null. But problem is as i add more owner types i have to keep altering the table to add more columns

The other way is to have separate photo tables for each of these owner types.

None of these methods scale well. Any other methods to own photo albums for different owner types from one table that will scale (meaning have less joins and be easy to query as read performance is my #1 requirement?

Platform is MySQL/PHP.

Cory
  • 13
  • 4

2 Answers2

2

Create a table with three columns, album FK, owner FK, and owner type with owner type being a enumerable type for user, even, group, etc.

Kyle Heironimus
  • 7,741
  • 7
  • 39
  • 51
  • That is the question "owner FK" - where will it FK to? The user table, event table, page table, etc? As far as i know we can only FK to one colunm in a table or can we FK to multiple tables from 1 colunm? – Cory Jan 14 '11 at 20:47
  • It's one FK per table. The code will determine by the type which one should be set. – JOTN Jan 14 '11 at 20:56
  • What JOTN said. For example, if one album is owned by user 1 and another is owned by group 1, both records would have an owner FK of 1. However, the type field for the first would correspond with a user and the second to a group. – Kyle Heironimus Jan 14 '11 at 21:12
  • I get that but I am still lost on how to put the FK constrain. What goes in the "?": KEY `owner` (`owner`), CONSTRAINT `OwnerAlbums` FOREIGN KEY (`owner`) REFERENCES `?` (`?`) ON UPDATE CASCADE – Cory Jan 14 '11 at 22:31
  • You can't use constraints with this method. See http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables for a more detailed answer, and possible alternatives to my solution above. – Kyle Heironimus Jan 14 '11 at 22:51
2

Think of your problem in a Conceptual Data Model instead of purely physical. If you do OO work, that's fine too. CDM

The semicircle is an inheritance operator. So Event, User, Network, Page all inherit from Owner.

When you generate this to a Physical model you'll use these settings.

alt text

This way you get one table of everything which can own a photo and all of the common attributes. Then each child table will inherit the PI from the parent, and have attributes specific to it. The FK of the photo table will go to the Owner table.

Your future goal of many owners for a photo changes from a Owner ID as FK on the Photo table to a many:to:many mapping table between owner and photo. Really easy if you use these constructs.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22