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