Hey all,

I have been working really late on a project, and I'm totally having a brain fart. I just need some advice from anyone as to what is the best method to develop my relational DB. Let me briefly explain my situation and that should help demonstrate my problem.

I am designing a Contacts DB with the contacts for music publishers. This is a fairly simple DB with the exception of the large publishers (such as Sony and EMI) where they have roughly 30 sub-companies apiece. For a big company such as Sony, we will have the exact same contact information for both Sony and the 30 sub-companies that it owns.

What I want to do in my relational db is enter a new company name, then (if it is owned by a larger publisher) choose from a menu of every available company in a "care of" field, and have it relate to that same contact information.

If anyone can offer any advice as to how I should design this relational db, I'd really appreciate it. If my explanation is not clear enough, please let me know and I'll try to better explain it. Thanks so much for your help.

Cameron...

In that situation, as you want to keep it simple, I would suggest that any company records been created wIith 2 ID'S :

- one unique_ID to each company

- the other, call it Master_ID, set by a script either equal to this unique_ID or to the Main Company (care of) unique_ID.

The relationships from Contacts to Company File would be set using this Master_ID.

Hi Cameron!

To expand on Ugo's suggestion ...

You can accomplish this very simply by using a self-join relationship.

1) Add a field to Field Definitions called MasterID (text), auto-enter calculated value and select CompanyID.

2) Create a new Relationship called Master Company. Match your MasterID field on the left with CompanyID on the right.

3) Create a Value List called Master Company, from field all values. On the left select MasterID and on the right select Company Name.

4) Attach this Master Company Value List to your Company:Master ID field via field format, pop-up.

Reassign all sub-companies of Sony by entering Sony's CompanyID in each subs MasterID field. Now each new Company will continue to have it