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.

Not knowing your business except for what you've provided, I'd start with your primary focus--your advertisers. They're who you're serving, so they're the first table. Then, I'd probably have a table to describe the advertisements (size, price, image file ... ?), a table for Books (title, year, community, ...?) and a join table between Books and Advertisements (Ads_In_Book).

The input workflow I envision would be:

0) Create entries for the different Books

1) Identify the Client in the db (search or add)

2) Identify the Ad (search or add)

3) Assign the Ad to one or more Books

Does that help?

David

what you are telling me seems to be exactly what others are telling me as well. my only issue is that i am very limited for time and having a perfect database is not my top priority. i want to have the basic structure set up and work on perfecting my database over the next year as my business developes. do i really need it to be perfect at the very beginning?

I'm not sure what you're saying. I never said anything about perfection. You definitely need to separate the different objects in your DB to manage them effectively.

I started with the basic structure, and you'll see that putting a little time into that at the start will save you IMMENSE headaches later. I added a bit about how I pictured the workflow because in many ways the structure of a DB depends on what your workflow is.

I find that interface design is the real time-eater, and that's the area that can develop over time.

David

what i mean is that all i have time for now is setting up fields to record information about advertisers. no strategy, tables or relationships. later, when i have more time to plan all that out i would like to import the advertiser information into my new correctly designed database. i'm basically going to use filemaker as a simple contact management program for now.

how's that sound?

First off, you can't set up fields without tables; fields go inside of tables. The sort of design I'm describing could be handled at a rudimentary level in just a few hours.

I strongly urge you to take a bit of time to learn this. Inevitably, the choices you make now will persist for far longer than you initially intend, and by the time you revisit the structure, you'll have a whole lot of data that's crammed into a design that makes it extremely difficult to extract your data reliably.

What you're proposing is really not much different from putting the info into a spreadsheet--which might be a better tool for the approach you want to use.