Elementary Relationship question

[z178]

Have 2 tables in a database

Artists and Paintings with a OM relationship between Artists and Paintings.

In a layout based on Paintings, I have a field for artist Name derived from the Artist's table. If I use the OM relationship to fill Artist Name from Artist table using a value list generated from the Artist table, I see that I dont save much in terms of file size. Because In the table view I still see that the Artist name field in the Paintings table is filled with Same name in multiple records.

Whether I enter artist name into paintings table manually or using a value list via the relationship, my database filesize remains the same !

Wouln't this be redundant data?

Am I doing things wrong here?

Wouldn't I be better off creating a value list for artist names and check the option to enter othervalues than keeping a separate table for artist Names and generate a value list from it to fill Artist Names in the paintings table?

[Tim W]

Hi,

Just display the Artist from the Artist table through the relationship. eg. Artist::Name

The relationship stores the match fields for the tables, to know which Artist Name to display.

Two tables makes adding artists easier, however you could just use a value list if you prefer.

There is almost never only one right way to solve a problem. Each solution holds pros and cons, lending it's use to the desires of the designer.

HTH,

Tim

[Fenton]

If the artist name is a field in Paintings, then you might as well display that on the layout, not the related field. But if you are concerned with file size, you should create an auto-enter serial id field for Artist, and use that as the relationship key. That is, no artist name field in Paintings, just the Artist ID. Then you can do as Tim says, just display the artist name in Paintings as a related field. That is the most space-optimized way.

It is also possible in later versions of FileMaker to create a value list of all Artist IDs, [x] Also show the name (should be a Last, First calculation),* and [x] Show only values from 2nd field. That way only the names drop down, in either Browse or Find mode, but the field is actually the Artist ID.

To polish it off, you put the related artist name field on top of the Artist ID field in Paintings, but change its Field Behavior to no entry in Browse or Find. It then looks like the name field and acts (much) like the name field, but is the ID field.

*There is one caveat to this technique. You cannot have 2 artists with the same first and last names; because only the first one's ID will ever get chosen, the other will never be in the value list. But that would be pretty rare, and can be handled by including a middle initial field in the calculation, and validating the fields to not have a duplicate.

[z178]

Thanks everyone.

And thanks Fenton for FM101 tutorials @ your website. tis good info for noobies like us.

Please update 'em for >FMP7 ? :)

Edited October 2, 2007 by Guest