Member Avatar for emkayiii

Hello DB gurus,

Does anyone know how to approach the situation where a record's status (in this case "is_in_recording" for example) depends on multiple records in another table?

I have a "files" table and it has a boolean "is_in_recording" which if set to TRUE means the file is used in a recording somewhere and should not be deleted. (see screenshot below)

The file could be used in 0 to many recordings though, and I don't know how to represent that best in the model...

Any tips?

ERD screenshot

Thanks!

Mat

  • 2 Contributors
  • 4 Replies
  • 94 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by pclfw
Member Avatar for pclfw

Many different ways to solve this one.

ASSUMPTIONS
1) There are two tables, FILES and RECORDINGS

2) FILES has a column called ID and a column called IS_IN_RECORDING

3) RECORDING has a foreign key column pointing to FILES.ID.

4) RECORDING has one (or more) boolean columns defining the status of that row, i.e. IS_IN_THIS_ALBUM, IS_ON_THIS_PLAYLIST

Now the situation is simple SQL

SELECT F.*
FROM FILES F,
RECORDING R
WHERE F.ID = R.FILE_ID
AND IS_IN_THIS_ALBUM = 0,
AND IS_ON_THIS_PLAYLIST = 0
etc.

Hope that this helps.

Member Avatar for emkayiii

hi pclfw,

thanks for the response.

I'm more concerned about tracking changes, than about doing the select...

consider this:

- fileA is used in recording1 and in recording2.
- therefore fileA will have is_in_recording set to TRUE.
- fileA is deleted from recording1.

Is there an efficient query for checking and updating is_in_recording in this scenario?

Right now I can only see the following:

1. I'd have to first query the "recordings" table for any recordings that contain fileA
2. then I'd query the "files" table again to see what the is_in_recording value is for that file, and
3. then update this value (set to FALSE if no occurences in the "files" query, or set to TRUE if there was...)

It seems inefficient, and I wondered what the best practice was for this scenario in terms of table structure...

Thanks!

Mat

Edited by emkayiii because: n/a
Member Avatar for emkayiii

any ideas anyone??

Member Avatar for pclfw

Emkayiii,

Sometimes inefficient is actually a little safer.

I assume that you're using MySQL and don't have the option of DELETE TRIGGERS.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.