Jump to content

Need advice of number of tables in database


This topic is 4745 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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.]

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4745 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.