Ramón Vela Córdova

For the benefit of others who may come across this topic -- or in anyone wishes to correct me! -- I've been thinking about Comment's post above, plus reading a little, and think I know see my confusion. The relationship between some of the objects I need to track -- for example, applications and registrations -- is not one-to-many but rather one-to-one. Hence they belong on the same table (unless you might want to update information in the second table w/o changing the first). Other relationships here -- for example, registrations to renewals -- are one-to-many, so separate tables are needed. Doing it this way will involve a lot of layout work, because the main table will have many fields that will require different layout or tabs. But the relationship graph should be much simpler, and presumably so will any scripts and calculations.

You guys had requested a specific example. I wanted to avoid that because the example has to be a little involved, but here goes. You have a trademark for your products -- say, ACME for rocket launchers and explosives. On Jan. 1, 2016 you file an application to register ACME with a government office. The application contains your name and address, filing date, description of the trademark, the goods to which it applies (rocket launchers and explosives), when your business began, etc. On July 1, 2016, the government office notifies you that the application is flawed because you misspelled your name. On August 1, 2016, you file a response asking the government office to amend the application so that the name is corrected, and they say yes on October 1, 2016. On Jan. 1, 2017, the government office publishes the application in a newspaper, and everyone has 30 days to oppose it. On Jan. 20, 2017, someone files an opposition to your application, but the government office says that you prevail on June 1, 2017. On July 1, 2017, the government office gives you a registration number and certificate -- the trademark is now registered. In 2020, you change your address and stop selling explosives, so you ask the government office to amend the registration, which they allow. In 2027 and every 10 years thereafter, you have to file a petition to renew the registration, which is usually allowed, but could be subject to objections by the government office that will require response. Hopefully this example clarifies what I meant about a registration being simply a collection of events (applications, amendments, etc.). Almost all of the information for each registration comes from the events noted above. I've looked around for discussions of this type of data structure specific to Filemaker, but haven't seen any.

Comment: You mentioned the "current" attribute or value. I know that it's possible to have a field that says whether a record is current or not. Is that what you have in mind, or are there other ways to determine which records are current?

Thanks! That sounds very helpful as I've been thinking mostly of one-to-many relationships. I should be able to work on this during the weekend and report back.

Let me clarify something about the analogy in my previous post. I didn't mean to suggest that information can never be included in a parent table if it is subject to change. The issue I have in mind is where the events that provoke changes are things that the solution also needs to track in a child table (or tables).

Hi Matthew, Thanks for the response! Hadn't thought of having more than one record for each registration. I'll consider that. Let me try an analogy to explain the question I have. Suppose a solution's purpose is to track your houses's information -- location, size, value, property taxes, maintenance performed, maintenance due in the future, etc. Now each house's location probably won't change, so that may belong in the Houses table. But everything else is subject to change as a result of future events, such as an expansion, appreciation, new property taxes, maintenance work, etc. So it seems that this data should not be in the Houses table, but rather in an Events table (or tables). My question is whether that is correct and, if so, how would you generate a report that captures all the current information about each house at any point in time?

I’m relatively new to Filemaker and developing a solution to track trademarks. I’m deciding how to structure the data, and would really appreciate any pointers. The abstract data structure is this. There are owners. Each owner can have many trademarks. Each trademark can have many registrations. Each registration can have many events, many of which involve deadlines. Some events are initiated by the owner, others by a government entity, others by third parties. Some events may be superseded partly or entirely by later events, such as when an application is amended. The purpose of the solution is to track the registrations, their status, and their pending tasks or deadlines. Right now I have tables for owners, trademarks, registrations, and events in successive one-to-many relationships. What I have to decide is where to put the data and how to report it. In principle, it seems that the logical way is to have most of the data in the events table because a registration is simply a collection of events. But if that’s the way to go, how do you reconstruct the registration as it currently stands? That is, how do you identify and show the latest or current data from the transactions table? The other alternative is to have most of the data on the registrations table, but then this would involve a lot of duplication. I know this is all rather abstract, but if anyone can give me some pointers about what the best-practice approach to this sort of thing is, I would appreciate it!