Jump to content

Relationships for Dummies


This topic is 6530 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I've been working with filemaker since a very early version so creating a flat file type database for me is very easy. Even using lookup fields to populate information was easy. Using a field to see data from another table is somewhat simple.

The problem I start to panic at is when I need about ten tables for various things and then realize that I have no idea what I'm doing and how I"m going to tie this stuff together.

I mean, say I have a book table, an ISBN table, and a person table. In very rare cases an author can be an editor and sometimes an artist as well. Do I need three "title" tables or maybe one person table and a title table? Check boxes for each title?

It gets even worse with royalty databases. I built a really slick one that is great when there is one author, but with split royalties for anthologies I have to enter the book as many times as there are authors because I don't know how to tie one book to many authors.

I just need a "smack me over the head with a keyboard" book so I can grasp the very basic concept I guess. :)-) If possible, please point me in the right direction.

Thanks!

Rebecca

Link to comment
Share on other sites

And now I'm really scratching my head because it seems that now not only are there related fields, but related external databases as well. I was just reviewing the business kit for FMPro 8.

I'm sure the idea is not that difficult. I understand the 1 to many, 1 to 1, but putting the concept together is driving me nuts.

Thanks!

Link to comment
Share on other sites

Rebecca:

The relational part of database design can be tough to grasp at first, but once you get it, everything falls into place pretty quickly.

For example, your royalty database. The way to set this up would be to have a single record for a given book, and then to have a related table called (let's say) Contributors. If you've already got a database of all the different people (editors, writers, etc.), then the Contributors table would have (at least) the following data in it:

Book Serial Number

Contributor Serial Number

Percentage

The first field would contain the serial number (or whatever key field you've got) of the book. The second would have the same sort of data for the contributor. If you don't have a table of contributors, you could have fields for the contributor's contact data (name, address, etc.) here instead. The third field would be their portion of the royalties for that book.

Now, to create a record in Contributors, you would start from the book record. Take the book record's serial number (key field) and create a new record in Contributors and place that data in the Book Serial Number field. Then you would go to the Contributor table (or a portal to that table) and allow the user to enter either the contributor's data, or otherwise select the Contributor from a list, in which case you'd grab the contributor's serial number from the table/file of possible contributors and put it into the Contributor Serial Number field.

Now, for the relationships. There are two relationships in this example. The first is between the book and the Contributors table. Link the Book Serial Number in Contributors to the serial number/key field in the book table. This is the same field that you pulled data from in the example. The second relationship would be between the Contributors table and a table of all possible contributors (your contacts db). Again, link the Contributors Serial Number to the key field in your contacts db.

Now you can show related data in each table - so, for example, in the book record, you can create a portal to the Contributors table, and show everyone who contributed to the book. In the case of a book with a single author, that's the only name that would show up. In the case of an anthology, everyone who contributed would show up.

You can spice it up by adding a field in Contributors called Role (or whatever) where you could enter "Editor" or "Writer" or "Illustrator" or whatever.

That may seem like an awful lot, but I hope it at least starts you in the right direction.

-Stanley

Link to comment
Share on other sites

Thanks Stanely. I will try it. The concept does seem easy until I try to put it into action. Now that I realize a lot of the information is taken from outside tables it makes a little bit more sense. I've been going back and forth between MySQL and FileMaker and although the concept is the same, the method is different.

Thanks!

Rebecca

Link to comment
Share on other sites

This topic is 6530 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.