Relationship Chaos

[BFarrow]

Two Newbie Questions:

1. How do you know when to make something a value list as opposed to a series of fields?

2. When creating a join table, do you use the fields from the existing tables in the join table or create new fields that have relationships with the existing fields?

[aldipalo]

Hi and welcome to the forum.

1. IMHO, you use a value list when you have repetitive data and you want to insure that data entry is consistent. Cities, States, ZipCodes, Counties, Countries, Titles, Prefixes, Suffixes, Product Names, Sizes, Weights, etc., just to name a few. I'm sure there are many other uses as well. Value lists are attached to the corresponding fields.

2. There are many different ways to use a join table and you can use different fields to accomplish your goals. Generally, when developing a Parent/Child (one to many) relationship you would use the Parent::ParentID (A serial number) -> Child::fk_ParentID as your related fields. Keep in mind that if you use data fields and the data on one side changes it breaks the relationship. In some instances that's ok, but, in general That's a mistake. If you are relating a Company to a Contact by Company Name and you change the Company Name (the company gets sold or merged) you lose all the contacts and you must restore every one of them in order to make it right.

Take a look at these sites for some good reading material:

http://www.filemaker.com/help/MacTOC8.html

http://www.foundationdbs.com/downloads.html

(Read the "White Papers as well as DataBase design.)

HTH