Jean,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.
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.
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.