0 Replies Latest reply on Sep 23, 2012 10:59 PM by AlastairMcInnes

    Printing records from multiple related tables



      Printing records from multiple related tables



           I have a database of book information. There are two main tables - one containing information related to all editions of a book (Titles) and one containing informtion to a specific edition (Editions). There are related:

           Editions::fkTitleID = Titles::TitleID

           In addition, the author(s) or other contributors and publisher are held in related tables. The author relationship is many-to-many so there is a link table:

           Contributions::fkTitleID = Titles::TitlesID
           Contributions::fkContributorID = Contributors::ContributorID


           Titles::fkPublisherID = Publishers::PublishersID

           Then each book is related to a number of categories for the purposes of our website. This is also a many-to-many relationship so there is another link table.

           TitleCategoryLink::_fkTItle = Titles::TitleID
           TitleCategoryLink::_fkCatID = Categories::CatID

           And, lastly, we store reviews for some of our books and these, too, are stored in a related table:

           Reviews::_fkTitle = Titles::TitleID

           What I have now been asked to do is to produce an information sheet for a subset of the books - these are identified by a keyword in a field in the Titles table (Titles::KeywordsUS).

           The information sheet must contain informtion from all the related tables. This is fine for the Publisher (there is only one publisher) but I'm struggling with the others.

           All the searching I've done on this forum and elsewhere on the internet suggests that a layout for printing a portal should be based on the child table, which is fine except that I have 5 child tables and can, obviously, only base the layout on one.

           I have considered creating unstored calculation fields which are a concatenation of all the related data from the child tables but, firstly, I'm not sure how to define and populate such a field and, secondly, I'm concerned about the response time - the database is remotely hosted and we're already borerline with response times. I've read that unstored calculation fields are a no-no for remote hosting. I suppose a stored calculation is a possibility but that seems inefficient and I still wouldn't be sure how to define or populate it.

           These calculated fields would, I think, have to live in the Editions table to avoid having to have all the Edition information somehow concatenated into a field in the Titles table.

           To be honest, I'm seriously wondering if I've been too "pure" about my design. When I was asked to create this database, I immediately envisaged more or less the table structure I now have but as time has gone on, I've increasingly come to wonder if it's worth it. The company has another FM system which contains information on a different set of books (previously two companies) and that db basically uses one big table with 4 sets of author fields, a big text field for the reviews, the imprint stored directly and different records for the different editions. This wouldn't have gained me any credit years ago when database design was included in a course I did at university, but it does mean that they can import and export records to their hearts' content whereas all but the simplest import/export requirements for my database require at least some input from me in either a FM script or an external VB program I've written. This information sheet may be the final straw. After all, we're not really all that bothered now about the amount of storage space required so why go to such lengths to save it?

           As always, thanks in advance for any help,