Schema: two-way relationships? which table/entity should own 'preference'?

I'm not sure how to ask this question, so I'll be as clear as I can with an example.

In an app like facebook, a Profile can have multiple ProfilePictures. At any given开发者_开发技巧 time, one of these is the "selected" ProfilePicture (assuming a ProfilePicture has been uploaded).

Instinctively, I would model this like:

Table: Profile
--------------
ProfileID
SelectedProfilePictureId //fk to ProfilePicture
Name, Age, Etc

Table: ProfilePicture
---------------------
ProfilePictureId
ProfileId //fk to Profile, indicating which Profile this picture belongs to
Url, DateTaken, Etc

At this point, these tables point to each other and it just seems "wrong" to me. It makes it easy to query for Profiles without SelectedProfilePictures or to get a Profile's SelectedPicture, but inserts and updates are a little wonky.

Is this bad form? Should the Profile table be completely independant of the ProfilePicture table? Is there a "correct" way to model this according to database design theory, or is it up to the descretion of the programmer?

---

You are correct, this is called a circular reference and you actually cannot make the database do it.

One simple solution is a "preferred" flag on the child table of pictures. But you have a biz logic issue here: only one row for a given person can be preferred. So when anybody marks a new one as preferred, you must clear any others.

A safer solution is to add a third table "preferredPictures" with foreign keys to Profiles and ProfilePictures. Make it unique on profileId, so it can never have more than one entry. This forces a deletion of a prior entry before insertion of a new one. Or of course you can just do an update of an existing.

EDIT: in response to comment.

1) No circular reference because Profiles is parent of both child tables, it does not loop around to Profiles

Profiles
   |  |
   |  +-----> PreferredPicture
   |  +----->
   |  |
  \|/ |
ProfilePictures

2) Primary key of Preferred Picture is ProfileId, it is technically a 1:1 child table of Profiles.

---

why would you need to keep the ProfileID in the ProfilePicture Table ??

if you need this you may need a 3rd Table in which you build the association:

ProfileID<->ProfilePictureID

---

I've worked with kind of "circular reference" on other projects before.

if Profile.SelectedProfilePictureId is Nullable and ProfilePicture.ProfileId is Mandatory this is fine. only if you make both fk mandatory you get a real circular reference which is not possible.