General relational database question
Hi. I'm the adviser for a high school newspaper, and I've spent some time this summer designing a database that will facilitate communication between students on the newspaper staff about the articles they're working on. I'm using Filemaker 12 Pro.
I've got three tables in the database: One is Staff Members, which holds various information about the students, incluing full name, position, and the account name that they use to log into the database. The second table is Articles, which holds information about each article including the section of the paper the article will run in, an abstract, possible sources, multimedia ideas, etc. And the third table is Tasks, which joins Staff Members and Articles in a many-to-many relationship, and also records fields like duedates and completion status. Each table automatically creates a unique record ID for every record in the table.
I've about finished implementing the database, and nearly everything works the way I want it to. But now I'm looking to add a few features that will make it more user-friendly, and in doing so I'm realizing that I've got some very basic questions about the way relational databases work, and I don't know where to find the answers.
Two examples of my confusion:
When creating a record in the Tasks table that is to be related to both a staff member and an article, it seems to me that I should be able to assign the task to a record in the Staff table by staff member name, and assign the task to a record in the Article table by article name. And since I've read that it's always a good idea to use unique ID fields as match keys when relating tables, it seems like I'll want Filemaker to look up that staff member's unique ID number from the Staff table, and use that field to relate the task to a particular article in the Article table. And all that should happen behind the scenes, because I'll go insane if I try to remember everyone's staff ID, and after all, isn't this exactly what relational databases were designed to do in the first place? But I can't figure out how to make Filemaker do that, and so I must be missing something.
Example two: I've been trying to write a script that runs when a new record in the Articles table is created. I want it to interrogate the current user's account name, then look up that user's position on staff to determine if that user is either the news editor or the sports editor. If they are, I want Filemaker to automatically fill in the "Section" field in the articles table to "News" or "Sports" as appropriate. Again, I'm completely stumped as to how to make this work.
These seem like very basic questions, and I'd assume that the fact I can't answer them means that I really don't understand the way relational databases work on a fundamental level. Would other people agree with that assessment? Does anyone know of any resources online that might help me out with the basics?