Note: This blog post also ran in the Society of Professional Journalism’s “Journalism and the World” blog. Click here to see the original post.
This is going to be a bit of a technical post, for those setting up a relational database for their own freelance writing workflow, or hiring someone to do it for them.
This is only for those freelancers who need to eke out every last dollar from the time they spend working.
I’ve been designing and re-designing this thing for a decade now, and this is what I’ve come up with so far:
MAJOR TABLE: Assignments
Here is where I put all the information that occurs once and only once for each assignment.
This includes, but is not limited to: Assignment Title, Editor Name (and publication), Date Assigned, Date Due, Date Finally Handed In, Words Due, Total Amount to be Paid, Assignment Summary, Outline and Notes, Story Text, and Invoice Number.
If you have a lot of assignments with the same title (say, you’re like me and many stories are titled “China Banking Industry Overview”) then you need to have an Assignment ID. Most relational databases will generate a new number for you in numerical order. Or you can make up your own numbers, or create funky slugs like 07-04-bank-over. Whatever helps you tell these babies apart.
Some of these items will be filled in right away when an editor gives you an assignment. Some fields, like Outline or Story Text will be filled in as you go along, and others, like Date Finally Handed In and Invoice Number, you’ll be able to fill in at the end.
MAJOR TABLE: Editors
Here is where I put all the information that occurs once for each editor.
This includes, but is not limited to: Editor Name, Telephone, Email, Address, Title, Magazine, Word Rate, and Website.
In a relational database, this table can be linked to the first table, so that every time you click on an editors name, you get a list of all the stories you’ve ever worked on for that editor. Handy.
MAJOR TABLE: Sources
Here is where I put all the information that occurs once and only once for each source.
This includes the usual contact information — Name, Telephone, Title, Company.
If you find that a lot of your sources have the same name (say, you’re in China, and they’re all called Chen Chang) then you need to create a Source ID# to separate them. Or you can have a Source ID that combines the name and the company. The main this is not to mix these guys up.
If your relational database allows this, it’s also nice to have a place to store the source’s photograph.
MAJOR TABLE: Companies
Here I put all the general information for each company — it’s website, main switchboard number, full legal name, city of incorporation — all those piddly little details that editors keep asking for. The reason to put it in a separate table is to avoid entering this information over and over again for each source who works at that company. In addition, you can click on a company name and all the people working there will pop right up. Very useful for key companies or agencies on your beat.
MAJOR TABLE: Invoices
The money-making table. You need an Invoice Number, Date Filed, Total Amount, and Date Paid. I’m putting Invoices in a different table from Assignments because sometimes several stories are lumped into one invoice.
SECONDARY TABLE: Interviews
This table connects your Sources to your Assignments. You can’t put the interviews in the same table as assignments because each story has (or should have!) more than one interview. Similarly, you probably speak to each source for more than one story. So, separate table.
In the Interview table, you need to track: Source, Assignment, Date of Interview, and Transcript of Interview.
SECONDARY TABLE: Research
This is where I put all the press releases, corporate backgrounders, white papers, research reports, and other stuff accumulated while researching a story. Oh, and other articles (for inspiration, of course, or fully proper attribution!)
This table starts with the Assignment Title (or Assignment ID), then has the Research Note Title, source URL (for web-based documents) and Key Text from the document. In the case of press releases, I just copy and paste the whole thing. In the case of white papers or big reports, I just copy the relevant paragraphs.
This way, I never forgot where I got a key piece of data and can always attribute it appropriately.
In the Research table, you can also save previous versions of the story you’re working on, or move them to a separate table called Versions.
If you have Access or Filemaker on your computer, you should be able to set this up in about half an hour to an hour. The tricky part is, first, you have to make sure you have a big enough box for the text fields — story outline and notes, story text, interview transcript, and research note Key Text. And, most importantly, you need to set up the links between all these tables.
Assignments is linked to Editors, Interviews, Research, and Invoices (and Versions, if you have it).
Sources are linked to Interviews and Companies.
After you get all this set up, you can get fancy and have the database automatically send out invoices by pulling assignment details from the Assignement Table and the Editor’s address from the Editor table. I used to do this.
You can also generate reports — such as a list of past-due invoices (very useful) or overdue articles (hopefully, this list will always be empty). You can also track you income month by month or publication by publication with the touch of a button.
The benefits of having a database like this, rather than nested folders of word documents, is that you can find any old article and any source instantly. You can call up all articles for a particular editor, or that a particular souce was quoted in and read transcripts of past interviews. I often get a head start on new assignments by pulling up similar old assignments and quickly glancing through old interviews and research notes.
In fact, for several years, I conducted all my interviews by directly typing in notes, while on the telephone with the source, right into the database. Having the fill-in blanks for “Name”, “Title”, “Email”, “Full Company Name” and so on right in front of me reminded me to get full identifying information every time.
As a side benefit, every single person I ever interviewed was always automatically entered into my database — I never had to worry about keeping my Rolodex up-to-date. If one of my colleagues needed to talk to someone at IBM, it would take me just a second to pull up everyone I had ever talked to at the company. Very handy.
Anyway, I have to head off.
If anyone wants to know more about how to do this, feel free to get in touch.
Signing off in Shanghai,
Maria
