2 Replies Latest reply on Apr 26, 2011 6:10 AM by Terri

    Related Records best practice or ideas

    Terri

      Title

      Related Records best practice or ideas

      Post

      I work for a catalog clothing company. We send many catalogs throughout the year. We also have other components related to that catalog. For example, we have a "core book" (the main release) and then there might be more than one mini "sale book" inside that book, and there might be a version of that book that we put in the box when we ship out with merchandise, and then we also post a version of this on our web site....it goes on and on. The production of all these pieces relate to the "core book."

      The main purpose of my database is to show a production schedule for all these pieces from photography to page creation to printing. So all the components have a schedule that intersects with the core book but may also branch off into their related scheduled (for example, all components will have dates for photography, but the internet book won't have a press date, but it will have a "go live" date). Each of these components has a code, that is generated by the marketing area, and although it would be wonderful if they were in numerical order, there is really no convincing them of that and I think they pick these codes out of a hat just to keep us on our toes. Here is a basic collection of components but it can get very lengthy.

      May Core (code 4311) - MAIN BOOK

      Mini Inner Sale Book 1 (code 3F11)

      Mini Inner Sale Book 2 (code P311)

      Box Insert (code 4D11)

      Remail Book (code 4811)

      Internet Book (code 4398)

      I'm looking for the best solution to show all these related components. In other words, May Core is the "main tree" with the other pieces being "branches" of that tree. But when I search for the May core book I want to make sure they all come up at the same time as separete records with their own schedule. I'm willing to listen to any ideas and hopefully they won't be too complex for me to understand.

      Things I've thought about and discarded:

      1. Having all related pieces have "May" in the name - can't because this database is much more than just for scheduling
      and other records may have May in the name that aren't related.
      2. Having all related records have a number (such as 1) - can't because this is an ongoing database that goes from year to
      year so there is a 4311 and there will be a 4312, 4313....etc. Plus, as of right now for just the spring/summer season of
      2011 we have 1,322 records. Remembering what number we are on would be almost impossible.

      A big plus would be able to put these in a certain order - the main book is first, then the others fall in place afterward, even though they may have the same schedule.

        • 1. Re: Related Records best practice or ideas
          philmodjunk

          I think you are confusing the externally supplied "label" data with the internaly produced primary keys you need to relate your records. Essentially, you can allow your users to supply you with all the codes, names, dates, etc needed, just don't use them to link records. Instead use an internally generated serial number field for that purpose and enter all their codes, etc. into data fields.

          Thus, you might have a Catalogs table with a CatalogID field that is an auto-entered serial number field, but with a Code field that stores 4311 and a Title field that stores "May Core". Link each of your related catalog records by this auto-entered Serial number field, not the Code or title field.

          • 2. Re: Related Records best practice or ideas
            Terri

            Thanks Phil,

            I'll work on this today and post any questions that come up.