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!