cDima (cdima) wrote in databases,

table design issue

Hi everybody,

I have a table design issue. There are some sets of files that need to be handled as one file:
  1. File1.avi
  2. File2.avi
  3. File3.part1.avi
  4. File3.part1.avi

File3 is logically one file, but is split up into two parts.

I need these files to map to films, in a many-to-one relationship (many files to one film).

I've created the following tables:
  • a FILES table, holds all files
  • a FILMS table, holds all film descriptions
  • and a relationship table FILES_FILMS {id, file_id, film_id}.

And I can't get the following (logical) select result:
film1_id, file1.avi
film2_id, file2.avi
film3_id, { file3.part1.avi, file3.part2.avi } <-- that's an array, a "set" of files.

So: how do I gather the the files file3.part1.avi, file3.part2.avi into one group?

Should I loop through the results in of a query with Group By clause?

The best solution I could come up with is having a fourth table GROUPS to which the Files table maps to, in a many-files-to-one-group relationship. Then I can map the a group row to to the Films table, one-to-one, and when doing a select on the Films table, do a join with the groups and the files tables. But I believe I'll need to do on-client looping through the results anyway.
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

You only need the films and files tables, if you add a film_id foreign key to the files table.

Then you can do:

select * from films, files where files.film_id = films.film_id order by films.film_id, files.file_id
(all films, with all files for each film, sorted)

select * from files where files.film_id = "Kentucky Fried Movie" order by file_id
(all files for Kentucky Fried Movie, sorted)
Oh, that simple. Thanks.

I realized that I don't need to store a "group" anywhere, until it's mapped to a film.