macOS Filemaker Strategy

[chadwick]

I run a company that publishes telephone directories for communities. We publish one directory per year for each community that we service and we service half dozen communities. Many advertisers are repeats from previous years and each year may advertise in several communities.

As I prepare to build my first database I do not know how to organize the records of my advertisers. Should I make a new file for each community for each year or just have a field within each record that designates which community and year that advertiser is for. Also, when I make a change to the record the next year I do not want to lose some of the info from the previous year such as what size their ad was.

 

[CanadaRAM]

You're not thinking relationally. That's the biggest challenge in database design.

Keep your entities separate: An advertiser record is an advertiser, not the intances of the ads they have bought.

A directory is a directory, not a record of ads bought.

There is lots of Filemaker tutorial material available on the web and for purchase - I recommend you spend a bit and invest some time before committing to a database architecture that you will have to scrap later.

In answer to your question, think of three databases -- an advertiser database, a directory database, and a related database that sits in-between them that links the two for a given issue - where > - < is a relation.

Advertiser > - < Link DB > - < Directory


The link file has one and only one record for each ad placement

LinkID#: 192394
Advertiser: 125
Directory: 3
Issue: 2006

optionally it can have salesperson links and invoice links and ad size/content links, but the essential thing is, you can search this database for Community 3 Issue 2006 and get all the ads that were placed in that issue. Alternatively, you can search for Advertiser 125 and see all the ads they ever placed in any community.

Now that you have the Link DB acting as a go-between from A to D, you can use Portals to put together lists of all types.