Design Problem

[habitude]

Need help with a design problem..

3 Tables: Accounts, Contacts, Work Orders

Accounts contains names of Companies, addresses, etc. A "company type" field has choices (Real Estate, Escrow, Construction, Church, or Other.) via Value List.

Contacts contains people and contact info.

A "contact type" field has choices (Realtor, Escrow Officer, Manager, etc) via Value List.

Accounts & Contacts are linked by "AccountID"

Work Orders table is my problem... I need to show this information:

1. A Company & Contact (in this example a Real Estate company & Realtor with their Contact Info)

2. An Escrow Company (and Escrow Officer, Info, etc).

Is it possible to show both types of info on the Work Orders layout with this design or does each company type or contact type need to be in their separate tables and then somehow pulled in? Is there another method not shown here?

Whichever way works, can someone show me how to set it up?

Please and thank you.

[bcooney]

First of all, definitely keep all company types and contact types in the same table.

The quick answer is to have fields in Work Orders that store the IDs you need. For example:

_kF_CompanyID (value list of Companies)

_kF_ContactID (condtl value list of contacts by companyID)

_kF_EscrowID (value list of Companies of type Escrow)

_kF_EscrowContactID (condtl value list of contacts by EscrowID)

I also would go the extra step and put IDs on the company types and the Contact types (you'll need new tables: CO_Type and ContactType).

Furthermore, I'd separate out Addresses into their own table and Comm (email, phones into its own) and relate them to Company or/+ Contact by ID (since each can have more than one address/email/phone, right?)

And, our standard is to combine Companies and Contacts in one table, using what's referred to as Super/Sub type structure and the Graham Method technique (threads on FMForums about this). Also, can a company be of more than one type? or a Contact?