getting started with relationships

[richshone]

Having worked for many months on a solution related to my line of work, I am only just entering the relational side of things as I write this. I have a good book that I am reading and yet I am either missing a few basic things or am biting off more than I can chew. I want to pose a simple question so I may understand this a little better. Case: I have a file of 'events' that are usually days where an actitiy occurs and I want to relate it to a file of hotels that is continually growing and accessible when the information is already there from a previous 'event'. Since this is a possible many events relating to a possible many hotels, is this therefore a many to many relationship or am I missing something here? What could I use as the 'key' to relate them together? I assume that if I get this concept I can pursue the more complicated aspects of my database on a more relational level. Thanks for any help to a beginner...

[Chuck]

If many events could be related to many hotels, then yes, you have a many-to-many relationship. To build these relationships, you need to have what is often referred to as a join file, a file that sits between the events file and the hotels file and is a one-to-many relationship to each of them.

In your events file you should have a unique ID such as Event_ID. The same with the hotel file, having a Hotel_ID. The file that sits between them will have a field for each. So your Event_Hotels file will have an Event_ID and a Hotel_ID (in addition to it's own unique ID field). Relate the Event_Hotels file to Events using the Event_ID on both sides. Do the same thing with the hotels file using the Hotel_ID.

Also create the reverse relationships for each pair of files. Hotels will be related to Event_Hotels via the Hotel_ID field. Same with Events.

Once you have this set up you can then create calculation fields to get data from one of the files to be viewed in the other. For instance, let's say that while you're in the hotel file, you want to view the names of all of the events related to that hotel.

In Event_Hotels create a calculation text field, Event_Name and set it to Events::Event_Name (assuming there is such a field in Events).

Then you can have a portal in Hotels that looks at the Events_Hotels relationship, and place the field Event_Hotels::Event_Name (the calc field you created) and you will be able to see a list of all the events for that hotel.

Is that all clear?

Chuck

[richshone]

You have made yourself quite clear so thank you, but I do want to ask two questions for some clarification on the subject.

1. If for any event there will only be one hotel 'at a time' (from a choice of hotels either existing in or being added to the database), does this still mean that it is many to many in relationship?

2. I have read already about the join file in many to many relationships but have so far not had its practical use explained properly. For instance, is this 'join file' essentially an invisible file that runs behind the solution or does it become the main 'window' from wich the database is accessed? In short, is the join file just a small file of relational fields and that has no layouts of its own?

I also need to add things like contact info for event coordinators, venue information, and client info (which is further broken into departments) - all records which will be reused for other events. At this point all of this I have done within a single file, which is of course very redundant as I start to reuse the same data. If this winds up as three or four files with many to many relationships, should the 'event' even be at the 'centre' of the database at all? Any good books on the subject that are also practical? I have yet to see anything that starts to solve some of the things I would like to with my solution. Thanks for any help getting started with this relational thing

[Chuck]

quote:

---

Originally posted by richshone:

1. If for any event there will only be one hotel 'at a time' (from a choice of hotels either existing in or being added to the database), does this still mean that it is many to many in relationship?

---

If you want to be able to track not only the current event, but past events as well, you would use two relationships. To track the history of events, you would use a relationship to the join file. To track the current event, you would use a relationship to the Events file with the Hotels file having a field for Current_Event_ID.

quote:

---

2. I have read already about the join file in many to many relationships but have so far not had its practical use explained properly. For instance, is this 'join file' essentially an invisible file that runs behind the solution or does it become the main 'window' from wich the database is accessed? In short, is the join file just a small file of relational fields and that has no layouts of its own?

---

That depends on what you want. While a join file is probably almost never used as a main menu file, it could have it's own fields and/or a user interface to it, depending on what it's nature is.

For instance, an invoice system would probably have an Invoices file, a Products file, and a join file, often called Line_Items. While in this case you probably wouldn't have a user interface for Line_Items, it would have some of its own fields, such as Quantity and Cost (Quantity in Line_Items * Unit_Cost in Products).

Whether there is a user interface to the join file depends on whether or not the user ever needs to interact directly with the data. Sometimes, there might be a print layout in a join file. For instance, in the above invoices example, I would probably print the invoices from the Line_Items file, given the trouble with printing portals.

quote:

---

I also need to add things like contact info for event coordinators, venue information, and client info (which is further broken into departments) - all records which will be reused for other events. At this point all of this I have done within a single file, which is of course very redundant as I start to reuse the same data. If this winds up as three or four files with many to many relationships, should the 'event' even be at the 'centre' of the database at all? Any good books on the subject that are also practical? I have yet to see anything that starts to solve some of the things I would like to with my solution. Thanks for any help getting started with this relational thing

---

Now you're beginning to get into database design, which is a complex topic. A good FileMaker centric book that has information on this is Using FileMaker Pro 5 by Rich Coulombre and Jonathan Price. There are also books that are dedicated just to relational design, such as Relational Database Design Clearly Explained by Jan L. Harrington and Database Design for Mere Mortals by Michael J. Hernandez. I would start out with the FileMaker book first though.

Chuck

[Chuck]

That depends on what you want to email. Are you interested in emailing an actual FileMaker file, or just some of the data from the file? If you're just going to email data, are you looking to email it as a tab delimited text file, or in a more user readable form?

What is the purpose of emailing the file? What information are you trying to get to the recipient? Without knowing the answers to these questions, I can't really make a recommendation.

For instance, it's possible that you don't need to email a file at all, but that an email constructed from within FileMaker (using Send Email, Open URL or a third party POP plug-in) would be the best choice. Or if you do need to email the actual file for some reason, then perhaps a clone that sits idle in the system available only for importing the data you want. There wouldn't be a relationship, just an Import script.

Chuck

[richshone]

hmm... lots of questions to think about but I WAS thinking that (a) I didn't want to send an email of the whole file but instead just the info relevant (which makes a clone the wrong way to go I would think); (: I wanted to keep the integrity of the file's look and any calculations therein intact and so a text version of the file's data seems a wrong approach however... © it never occured to me to send an email from within the file with this data included ... if there are any third party applications or plugins that might help me I am all ears... thanks

[Chuck]

If you don't want to send the actual file, but just want to send the data in a record, or a sub-set of data, you can use FileMaker's built-in Send Mail or Open URL script steps.

If you want to send them a report with precise formatting (so that columns will definately line up and it will look exactly as you see it on the screen or when you print it out), emailing a PDF version of the report might be the way.

If you find that Send Mail and Open URL won't work for you, there are a number of plug-ins available that provide emailing directly from FileMaker (without the need for an external email client) such as MondoMail and dbMailer.

Think about what you want to do, exactly. If it was a perfect world, what would your recipient receive, and based on that, how can you get it to them?

Chuck

[richshone]

Thanks Chuck, you have been very articulate in your answers to my questions. I especially appreciate the book recommendations as I already purchased the book by Rich Coulumbre, it looking like the most comprehensive explanation of the concept of Relational Databases within the list of Filemaker specific books. Now I am confident that there isn't another better text out there. Assuming I get on top of this I will look for the other books when I can justify it. As for my project, as I already have a good idea of the look I want (having done the layouts once already), I guess I will just have to experiment a little to figure out the best way to design my overall database so it works efficiently.

I do have one question though - namely that as some of the time I will want to be able to e-mail certain data without sending the whole file I was thinking that this info should be separated into a different file even though it really is one to one in relationship - eg. income data related to the event. Any thoughts or other suggestions? Thanks, SEAN