| many to many relationship with unique values Options [message #458149] |
Thu, 27 May 2010 10:36  |
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 #459004 is a reply to message #458149] |
Wed, 02 June 2010 22:23  |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
