Hello all,

I'm working on a new database solution for the city's inspection department, and I've hit a bit of a snag.

Untill now, all of the departments data has been in numerous flat files, no relationships whatsoever. I've been reworking some of the files to include relationships, and have added many features that speed the work flow.

On this new solution, I'm trying to figure out how to build more functionality into the violation tracking and letter writing features. This is a completely new system for a new department, so I have the ability to start from scratch. I've hit a snag on how to structure a portion of the system.

Here's the basics:

I need to track violations found during yearly inspections, and I need to track letters sent to the owners, notifying them of these violations.

My entities thus far are:

Dwelling Unit

Inspections

Violations

Letters

I seem to have one-to-many figured out between Dewlling unit and inspections, and inspections and violations. The problem lies with violations and letters.

A single letter will have many violations. A violation may be referenced in many letters, if the owner does not comply on the first notice.

Seems like a join table is in order - but I cannot quite figure out how to make this work.

Any guidance/suggestions/thoughts would be greatly appreciated!

There are a coupe methods for making letters a bit more abstract.

having a library of letters that uses merge style fields to grab data from the other tables.

http://proofgroup.com/fmspark

http://www.nightwing.com.au/FileMaker/demos9/demo913.html

if the only functionality need at this point to reference all related violations in a letter a return separated list "multikey" of the ID for the violation could be stored in the letter.

a better method would be to have a simple join table between them but depends on the complexity and goals of the solution.