Design Problem

[Vin]

Please help,

I am having a very basic design problem which I just cannot get my head around, I have a sytem off about 10 files - Contacts,Invoices,Projects,Images,etc.

Problem: I need to be able to set up a mailing database related to the 'contacts file' by 'contact Id', I need to store details of the mailing ie. mailing history .What if I make each record in Mailings equall a seperate mailling and show all the contacts in a portal does this sound sensible or am I missing something, I would also need to "mark" the records in the contacts file.

Assistance greatly Appreciated

Vin

[LiveOak]

The reason you are having trouble is that the relationship between contacts and mailings is "many-to-many". Except for a few tricks, FM and other database programs cannot directly implement many-to-many relationships, they must be decomposed into two one-to many relationships. The classic example is students and classes. A student can be in many classes and a class can have many students how to relate the two entities?

The solution is to add two additional files. In the first file a record is a mailing. All the information about the mailing is stored here, the data, what the purpose was, what material it contained, the postage per piece, etc. The "marking" of contacts in the mailing is done by creating a "join" file between contacts and mailings, in which a record is a contact/mailing. You have now decomposed the many to many relationship between contacts and mailings into two one-to-many relationships, one between contacts and the join file and the other between mailings and the join file.

You can think of each entry in the the join file as a single piece of mail. The size of each entry is minimal as most of the interesting informtion about contacts and mailings is in the other files. The join file just associates contacts and mailings.

-bd

[Vin]

Thanks Live Oak looks like you've pointed me in the right direction. I"m going to do it now,keep you posted thanks for the prompt reply

Vin