Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    How to link disparate groups of records in a table ?

    Hi All,

    My first port to dBforums. I am a newbie to db design, some experience of the relation notations 1:1, 1:N, N:N, as yet no experience of UML, but think it a good idea to draw a UML diagram of all my tables and relationships and get them right before applying to a particular RDBMS.

    I think have completed most of the 'Class' design including the relationships for my first project.

    I am stuck on just one problem. I have a table FILMS - which will hold records of all films - many thousands. Records uniquely defined by PK FilmID. I expect various groups of those films, about 5% of all the records, will be different versions of the same, and thus related, where each group of related films has a common thread, unique to each group.

    For each linked group of films, I want to reflect that link in the database, perhaps using some kind of 'thread_ID'.
    Seems like whenever I have a group of films that are just different versions of the same, I have a 1-to-many relationship - or is it a many-to-1 ?.

    I have made many 1:N links in other parts of my Class design - but they have all been simple cases where the 1 is in one table, the many in another table. But in this case the 1:N is linking to related records in the SAME table ,and somehow the simple 1:N concept doesnt seem to fit.

    And of course, when it comes to designing the Forms & underlying Queries, when viewing any one Film in a related group of Films, I need to be able to see highlights of all Films related to the one I am viewing the details on, and when using the Data Entry form, to be able to Add a link, when I have decided during an INSERT Film, that it is related to a similar film. That is the problem I am stuck on.

    Is this an example where I need a joining table to hold FilmID's for the aforemenationed 5% of Records that form various groups, each with their unique group thread ? If so, how do I define the pointer structure that links FilmID's in the FILM table, and GroupThreadID's ?

    I thank you all in advance for your help.
    Last edited by will66; 04-30-12 at 00:26. Reason: typo

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486

    linking disparate "groups"

    You might consider adding a "group id" column to the table (you would need to determine a way to populate this) which would be blank for 95% of the entries and non-blank entries would be part of a "group".

  3. #3
    Join Date
    Apr 2012
    Posts
    9
    Quote Originally Posted by papadi View Post
    You might consider adding a "group id" column to the table (you would need to determine a way to populate this) which would be blank for 95% of the entries and non-blank entries would be part of a "group".
    Many thanks for your reply. In the end, I came to the conclusion my question was just another case of n:m, and so should be solved by using a 2 column junction table - holding records for only those approx 5% FILM records that are members of a related group.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •