3 Replies Latest reply on Oct 16, 2008 6:17 AM by AndreasT

    Table or Tables??? Help with a relational question



      Table or Tables??? Help with a relational question




      I have been using FMP for several years.  I love it. I do not come from a database background but I was able to create a database that was very useful for us at the time.


      We use it for tracking our acquisitions for the Media Library.  As I added things to it, I realized that it was time to look at it in a different light.  It needs to be relational. I 've taken FileMaker's web seminars, read the white papers, and am in the process of subscribing to the FileMaker Tech Network...  My problem is I understand the examples in the data model/relationship tutorials. But when I try to apply the principals to my database, I'm stuck about how to determine whether we need one table or several for one part of the db.  


      We track requests form faculty for possible purchase of media for our library.  Here's the  catch.. A request comes in and is tracked by title.  But the request can then be either a PREVIEW or a PURCHASE. And... if it starts as a preview, it can become a purchase at some point  (if the faculty member likes it) OR if he doesn't, then request stops there.


      I'm stuck trying to figure out whether I should use one table for all Request info or do I need to use separate tables for the Requests/ Previews/ Purchases.  Strickly speaking, all previews and purchases ARE requests.  There are a lot of fields so I'm wondering if creating separate tables is a better way of looking at the information. If so, I am stuck on the relationships.


       I tried to attach/insert an image of the layout but there doesn't seem to be a way to do this.   Any suggestions would be greatly appreciated.  I feel like the door is about to open.. I just need the right key to get through it!






        • 1. Re: Table or Tables??? Help with a relational question

          For me it would depend on what is to happen after the request becomes a purchase etc.
          That said, there may be business reasons for you to keep these request types separate.

          In general, I think I would keep all the requests in the same table and use a ordinary value list to choose the type.

          You say you need to move to a relational model; it would be helpful to know a little about your relationship schema.

          If you can provide some additional details as well as your reason for considering using separate tables for requests, I may be able to give you a more specific advice.
          • 2. Re: Table or Tables??? Help with a relational question

            Thanks for your response.  I'm not sure why we decided it might be better to have separate tables except that under a single Request table, there would be a lot of fields and I think we thought it might make it easier to have separate tables. I don't know if this is a legitimate reason at all.  I'm working with a Grad Student who has a background in database development and he seemed to think that for "data integrity" separating the information into tables would be better.  He is learning FileMaker but definitely comes from the developer's world.. (no offense) Honestly, sometimes I have to ask him to translate into something I understand as he uses what I refer to as the DB jargon. 


            As far as what happens after the request becomes a purchase, we track the costs and payment, vendor information, delivery info.  When received, we also track quality and problems with discs and finally we include cataloging information. All of this relates to one request so again, I'm just not sure about the structure.


            Right now we  have the general info about a request such as title, date requested, who requested etc on the top of the layout and tabs for Preview, Purchase and what we call New Materials (the quality and cataloging data). 

            In the new database I want to have separate tables for tracking Vendors  Requestors and Budget info.





            • 3. Re: Table or Tables??? Help with a relational question

              Thanks for the additional details.


              If the fields used in requests and purchase are largely the same, I would probably keep it all in the same table and use filtering relationships to provide the views needed.


              However, if the fields are mostly different, a multi-table approach sounds like the way to go. You would then use scripts to move the relevant information from requests to purchases.