Home » Other » General » many to many relationship with unique values Options
many to many relationship with unique values Options [message #458149] Thu, 27 May 2010 10:36 Go to next message
sandy_tp
Messages: 9
Registered: May 2010
Location: india
Junior Member
Hi,
I'm not sure how to create the following relationship:

I have a bunch of albums(of type: CD, LP, EP, SINGLES, etc...) and a
bunch of covers.


One album contains multiple covers but only one front cover and one
back cover. And one cover could be in more then one album (including
the front cover and back cover)


I've tried creating something simlar to this:


album: albumid, name


cover: coverid, filename


album_cover: albumid, coverid, displayorder


So far so good.. My problem is that i'm not sure how to create the
relationship that states that the album could contain only one front
cover and one back cover. Should i create a column in the album table
or in the album_cover to make sure that there is only one front cover
and one back cover?

Regards
Sandeep

Re: many to many relationship with unique values Options [message #458151 is a reply to message #458149] Thu, 27 May 2010 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Should i create a column in the album table or in the album_cover to make sure that there is only one front cover and one back cover?
yes

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: many to many relationship with unique values Options [message #458156 is a reply to message #458151] Thu, 27 May 2010 10:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Surely you just want a front_cover_id column and a back_cover_id column on the album table.
This isn't really a many to many relationship as such. It's two seperate 1 to many relationships.
Re: many to many relationship with unique values Options [message #459004 is a reply to message #458149] Wed, 02 June 2010 22:23 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
another alternative would be two function based indexes each using a flag column on the album_cover table. This is a bad alternative but a good one to show off ideas.

create unique index ai1 on album_cover(album_id,decode(front_cover_flag,'Y','Y',null))
/

create unique index ai2 on album_cover(album_id,decode(back_cover_flag,'Y','Y',null))
/

I am pretty sure these two unique indexes will enforce your rule of no more than one front cover and one back cover per album.

The advantage here is that all the album_cover data stays in one table. Thus may avoid certain unsychronized data. For example, I make the assumption that using the first suggestion, then when you update the front_cover/back_cover in the album table, you are required to put two rows into the album_cover table. This means you have the same data in two places. It thus becomes possible for your album table to tag a front/back cover that is not in the album_cover table because you forgot to keep these rows/FKs synchronized. You can get around that by saying the design says that an entry in album front/back cover implies the existence of the other two rows but that leads to more complex queries building the full list of covers in the album and prevents you from recording additional data about the front/back cover.

On the other hand, these two indexes only stop you from having more that one front/back cover. They do not prevent you having 0 of each. Of course you could likely solve this problem with a fancy materialized view refresh on commit. But now you are using advanced features that need explaining and documenting and that most people won't look for. You also need to work in a specific order of events when you change one or the other of front/back cover to a different cover.

Still, there are many who feel that constraints that need to be enforced are best enforced with declarative mechanisms rather than trigger code. Which would be another alternative.

It is fun to talk about it anyway. Good luck, Kevin

[Updated on: Wed, 02 June 2010 22:25]

Report message to a moderator

Previous Topic: fatal error U1073: don't know how to make
Next Topic: Verification of foreign keys
Goto Forum:
  


Current Time: Thu Mar 28 13:07:34 CDT 2024