solutions wanted advice wanted for custom database

[Noreenmachine]

I have created a database from our ancient FM 3 database. I needed to add more fields so a search could be more refined and more details could be added.

I have a problem though. I have business contacts, personal contacts and staff. Sometimes business contacts have home addresses; and personal contacts, as well as staff, have home addresses.

I have created a few tabs - for business contact info, contact details (as in what kind of business they are, etc.) but I'm not sure how to distinguish between a business contact's home address and a personal contact's home address - sometimes these would be the same info, but not always. I would like to keep all home addresses (for staff, personal contacts and business) to be in a separate layout so it can be protected from some viewers. Should personal contacts be in a separate table? I'm so far not able to wrap my brain around relationships (no pun intended!).

I also have a checkbox set on a separate tab to categorize staff and personal contacts. Is there a better way to do this? I should mention that I'm new to FM and my brain hurts. I've got so many fields I should be in real estate!

Any advice would be greatly appreciated. Thanks.

[David McQueen]

There are two ways you can do this:

1. Flat file

2. Relationaly

For flat file, you would have a multitude of fields for bus, staff, personal, home info and you can set them up in separate layouts if you want to keep the different address info separated. You can have calculated fields checking to see if a particular sector, say bus address, is empty so that you can search for just staff, bus etc.

You can also have autofill buttons such that if home is the same as work, click the button and the script will populate the fields.

This is probably the easiest way to get your head around the problem. It is not elegant, but it will work. It does have the advantage that all is in the same file so creating print reports etc is quite simple. Also if this becomes a base for something else, say event capture, when did I last talk to this person, all of the contact info is in one place for creating an event record.

Relationally, you can do many things. One division of data would be to have a contact file, address file and a communications file. Address and communications would be related to contact via a contact ID.

Going this route, you would have one contact record with the type of contact labled and then you could have multiple address records for home, work etc and show them in a portal. You can also have multiple comm records for say home ph, work ph, email, IM, Cell etc and show them in a second portal in the contact record.

This approach has the advantage of being able to handle multiple addresses and com methods with a minimum of fields and you will not get taken aback if some new form of communications takes place. It is simply a matter of creating another label possibility in the com file.

Using FMP 3 you will have to monitor file sizes. If my memory serves me correctly, the max file size is 32 megs. Go above that and the file will self destruct. So do not get comfortable having a photo field or anything of that nature. Confine the program to text data as much as possible.

All that being said, though FMP3 is old and long in the tooth, it is a full 32 bit application. IF you do not have printer dirver issues, it will probably run just fine.

HTH

Dave McQueen

[Noreenmachine]

Hi there. Thank you for your response. I also posted this in another section to which nobody replied

We have upgraded to FM8 and we only have about 1800 records so far.

I am still fuzzy on relationships. I was thinking of creating personal contact info in a different table, but not sure if that would be most efficient.

Thanks again for responding.

[Lee Smith]

I deleted your other topic, and sent you a private message.

[David McQueen]

You need to understand the concepts of

Entities

Attributes

Relationships

Think of entities as a rational grouping of characteristics - which are called attributes - One instance of each attribute per entity. This should be in one table.

The relationships bring different tables together for specific purposes.

Example:

Entity: Contact

Attributes:

Name

Sex

birthday

religion

Type (Staff, Supplier......) Could be multiple entries here.

Contact ID

Entity: Location

Attributes:

Street Address

Apt

Box

Town

State/Prov

Zip/Post Code

Location Designation (Work/Home/Club....)

Location ID

Contact ID

Locations are another entity than contacts because there can be more than one location per contact.

Carrying it further:

Entity: Communications

Attributes:

Type (Ph, Cell, IM, Email)

ComCode (Phone Num., Email Address....)

ComID

LocationID

ContactID

Again there can be more than one Com per contact and Coms could be related also to locations.

These are just examples so that you can take a look and see what makes sense in your particular circumstance. You will have to look at your actual operation and how the data should be split up.

HTH

Dave McQueen