Song database

[Speekless]

Hi all,

I'm trying to build a song database. I have 3 tables.

Songs, RelatedRecordings & Albums.

On the Song layout, I want to have a portal showing all RelatedRecordings to that one particular song.

The portal should display the following info: album, album artist, date of release, record label & catalog number.

I want these fields to be "pulled" from the Albums table. The Albums table is not only used for the Songs so I don't want to create a foreign key to songs in the Albums table.

What I want to happen is this:

When the use creates a related recording to a song, FM checks if the album name or catalog number entered already exists in the Albums table. If so, it looks up the rest of the data. If not, it creates a new record in the Albums table.

I'm not sure how to go about on doing something like this. I created lookup fields in the RelatedRecordings table to pull the Album info, but then I can't use those same fields to enter data and create a record in the Albums table...

I hope this makes sense (I have trouble understanding it myself -) ). It's kind of a "portal inside of a portal" situation.

Does anybody know what would be the "correct" way to do this?

Any help would be much appreciated! I've been breaking my head at this so much that I signed up for this forum... lol :

Pim

Edited April 24, 2009 by Guest

[jageo]

My sketch would consider the three tables as:

SongTitle (fields: ID_Song & Title)

Recording (fields: ID_Rec, fk_Song, fk_Album)

Album (fields: ID_Album, Title,Release Date, etc.)

Recording being the join table. (Where, if useful, you might consider putting a field for Guest Vocalist, since it relates to the Recording of the Song, not the entire Album, nor to the Song Title)

I would think a join table would be needed because a SongTitle could be recorded more than once, and appear on more than one album. And Albums have more than one song. So you have that Many-to-many relationship to resolve.

You wouldn't need to have any lookups, just portals. And you wouldn't need a foreign key the Album table.

For data entry your user enters Recordings, assigns a SongTitle (if it already exists or makes a new record if it doesn't), and assigns them to an Album (if it already exists or makes a new record if it doesn't)

Creative use of other portals could show other Albums that share that recording's SongTitle. Another could show other Recordings on that related Album.

Do you think that might be close to what you want to achieve?

JA

[Speekless]

Thanks for your reply!

I've actually gone for a slightly different approach because the solution you proposed was not exactly what I had in mind, but it was nevertheless of great help and pointed me in the right direction, it works now!

I also realized I didn't need the lookup field, because I can just place fields from other tables inside a portal , that's really cool!

The only slight problem I am having now is that when I want to create a record via the portal, I first have to enter data into a field from the join table, before I can add data into a field from the table invoked in this relationship (Albums).

I can understand why it would do that, but do you know if there is there a way around this?

Thanks!