Need advice of number of tables in database

[HelenaLB]

I hope this is in the right forum.

I'm working from the Contact Management template (FM 11 pro adv). I'm new to FM and I'm unsure as to how many tables I need. Reading "The missing manual" and the actual manual hasn't helped either...

This is what I need:

Contacts, searchable by, for example:

- expertise

- type (ex: scientist, funder etc.)

- task force membership

- publications

- funding area

- type of service provided

- speaker at a certain conference

etc.

For example, someone might want to find all contacts who are hydrogeologists in Spain and spoke at a specific conference.

Or, all contacts who provide printing services in London.

I've thought about it both from the query-side and the data-entry side, and can't decide how to set it up.

Do I create separate tables for publications, expertise etc. or do I just create fields in the contact table?

Thanks in advance for any advice.

[Vaughan]

If a contact can have one and only one expertise, then make it a field. If they can have more than one, it needs to be a related table.

Rinse and repeat for all other entities.

[Cue for somebody else to suggest the entity-attribute-value model.]

[HelenaLB]

Thanks!

Is that a general rule (one attribute per contact)? What about when people have more than one phone number or more than one address...?

I'm sorry if these are dumb questions, I just need to figure out the general principles for working with FM.

[comment]

Is that a general rule (one attribute per contact)?

The general rule is one instance of a specific attribute per record.

There are exceptions to the rule: for example, you could use a checkbox field to store multiple values in the same field - with some limitations imposed on reporting abilities. But in general, 'one person can have many addresses' translates to two tables (Contacts and Addresses) and 'one person can belong to many groups' to three (Contacts, Groups and Memberships).

Note also that a checkbox is strictly an on/off affair: you can attach many skills to a single person, but you cannot grade their proficiency in those skills.

[HelenaLB]

Thank you for the clarification :)