Newbie relationship question

[zaxatron]

Hi Everyone,

I am no programmer and would appreciate a bit of help from you gurus.

I am designing a small wine product database in FM7 and have created 3 tables.

Table1: Wine Table

Field1: Wine description

Field2: Wine year

Field3: Wine Specification

Field4: Wine Producer

Fiedl5: Wine Region

Filed6: Wine Province

Filed7: Wine Logo (Container)

Table2: Producer Table

Field1: Producer

Filed2: Producer Logo (container)

Filed4: Region

Table3: Locality Table

Field1: Region

Filed2: Province

My problem is how to create the actual relationships.

I want my "wine table" to use info from the "Locality Table" and the "Producer Table". Also my "Producer Table" must use info from the "Locality Table"

Ex. When if I inserted a Producer previously and on a new record I select the existing producer for the wine product and my producer logo is inserted automatically.

I am not sure if I explained myself, but whatever relationships I am trying to create are not working as expected.

I want to ask you gods of the Filemaker heavens for some help.

Thanks

Best Regards

Zaxatron

[zaxatron]

Come on people, some feedback on my question if you can!

[Fenton]

From what I can tell you have a nested structure. Since each wine has a producer, and since that producer is in a certain province and region (?), you may not need anything more than the producer ID in a wine. Of course, this breaks down if a producer has multiple provinces and regions; but that's a whole 'nother thing, and you'd have to tell us about it.

I used IDs, not names, and didn't bring the other foreign data actually into the tables. Unless you must flatten the data for export or searching there's no real need. But you could put them in, and make them lookups. Or just use the names.

Wineries_fej.zip

[zaxatron]

Hi Fenton,

Thanks for your reply, I really appreciate it.

Yes you are right a wine product can belong to a region (ie Tuscany), and a wine producer can have multiple regions.

Should I set up 2 separate fields for the region? Ie "Wine Region" and "Producer Region"?

I thought the structure might look like this, but tell me if I am wrong:

WINE TABLE PRODUCER TABLE REGION TABLE

WineID

WineCode

Wine Description

Wine Bottle Logo

Producer------------------Producer Name

Producer Logo

Producer Region

Wine Region----------------------------------------------Wine Region

Province

I would appreciate you comments on this.

Thanks

Best Regards

Zaxatron

[zaxatron]

Hi Fenton,

Thanks for your reply, I really appreciate it. (disregard previous post incorrectly formatted)

Yes you are right a wine product can belong to a region (ie Tuscany), and a wine producer can have multiple regions.

Should I set up 2 separate fields for the region? Ie "Wine Region" and "Producer Region"?

I thought the structure might look like this, but tell me if I am wrong (see attached jpg file)

I would appreciate you comments on this.

Thanks

Best Regards

Zaxatron

[Lee Smith]

Hi Zaxatron, and Welcome to the Forum.

Did you know that you can [color:"blue"] Edit your post.

You don't need to abandon your post and start a new one. All you have to do is to click on the [color:"blue"] Edit Button above the post that you want to change, there is even an option to delete a post if you want. However, you shouldn't Delete one that has been responded to.

HTH

Lee

[Fenton]

If a Producer has multiple locations, then you'd need a join table between Producers and Provinces (each province being in a region; I'm guessing). You don't really do these multiple connections with more fields in existing single-entity tables.

Then you'd just have to assign a region to the wine directly, in the wine table. You could not automatically know the region from the producer. I can't see why you'd really care what provinces and regions a producer was in; since you'd be assigning the RegionID to the wine directly. So Wines (not wineries as I had in my file, oops) would have a RegionID; simple enough.