Many tables to one table: Help please!

[Fredrik]

Hi!

I'm trying to make a database to manage information on books, authors and narrators of audiobooks. These three entities have their own tables and I also have a table for countries.

I want to assign a country to each author and narrator, and in a layout based on the countries table I want to show related records from authors in one portal and narrators in another one.

I have tried to make this work with different types of table occurences and join tables but I can't get it right.

Any help would be greatly appreciated:-)

[eos]

Apart from anything else, I suggest combining the Authors and Narrators table into one People table and

adding a join table BookRole “between” People and Books, as well as a table Roles.

This structure gives you the flexibility to handle multiple narrators or authors for a book, or when someone is

an author and narrator in the same or for different books, or for adding editors etc. (It seems that your Book

table also doesn't distinguish between books and audiobooks.)

As for the relationship based on country: Why don't you post your (zipped) database file so we can have a look at it?

PS: You should add your platform and FM version in your profile. This helps when trying to find an answer

to more specific questions

[LaRetta]

Hi Fredrik, welcome to FM Forums!

I would suggest only one table for 'people' called Authors, combining author and narrator and include a Type field to designate which they are. You could also just use a flag field if they are narrator but later you might want to add another type of 'person' so by using a Type field, you would have more flexibility. By combining author and narrator, you can search for a 'person' in one table. You might also have an author who may narrate a different book and using two tables would mean redundant information on a single 'person'. Also, if you wish to list the 'people' and the books/audio books they have written, it is far easier if they reside in same table. Idea is ... like things should be in same table.

Is the country table used to specify where the author lives or is country where a book of theirs is published? Can a person reside in two countries or if based upon the book, books can be published in different countries, can't they? You will certainly need a join table between Books and Authors called AuthorBooks and you may want a join table between Countries and Authors.

I would be happy to provide a sample file but I need to understand whether the country is for the book or the author.

[Fredrik]

Thanks for the quick reply eos and LaRetta!

Making one 'people' table sounds like a good idea and might solve my problems if I can get some sorting with portals to work:-)

My idea with the countries table is to specify the nationality of the different people. And I was thinking that it would be enough with one country per person. But maybe I should just put a join table there too so if I ever want to specify more than one country per person I could do that too. So I can probably get this working.

But I have another problem (that is similar to the first one I had but not possible to solve in the same way) too that you maybe can help me with:

I have been wanting to create a table for Years. My thought was that every record in this table would be a year, e.g. 1980 or 2012, and that each record would contain some information about historical events that took place that year and so on. So far so good, but I would also like relate this to both books and people for fields like when the book was originally published and year of birth and death for people. And then I would like to have portals in the years layout showing related records.

[comment]

My thought was that every record in this table would be a year, e.g. 1980 or 2012, and that each record would contain some information about historical events that took place that year

A table of Years seems rather redundant, unless you have something specific to say about each entered year. The historical events that took place in a year do not qualify; an event should be an individual record in an Events table, where Year may be a field.

[eos]

While a Year table might seem redundant at first glance, you have to put the year somewhere to trigger the relationship(s).

The usual way would be to create a global somewhere and change the value, but a dedicated Year table has the advantage that

you could view several years side by side.

I would create an Events table for all events, like Born, Died, Published etc., and then use a join table to join People, Books etc with

an event and a date. This way you could watch all or (using an event filter) specific events in one portal.

[comment]

a dedicated Year table has the advantage that

you could view several years side by side.

Could you explain what you mean by that?

[eos]

Go to your Years table and call the record for 2011. Watch all events in 2011.

Open a new window and switch to the 2012 record. Watch all events of 2012.

etc. Then put the windows side by side.

[comment]

If I thought such view useful, I would design a layout with 2 portals - using 2 global fields.

As a rule, your data model should not be determined by display requirements. Years (as such) are not an entity, and a table of years carries no information.

[eos]

If I thought such view useful, I would design a layout with 2 portals - using 2 global fields.

As a rule, your data model should not be determined by display requirements. Years (as such) are not an entity, and a table of years carries no information.

What would you do if you want to compare 3 years, or more? And why do you think that a Years table as described had any influence on the data model? It would simply be a tool of convenience. Adding it or taking it away again doesn't change the remaining tables or the data they contain.

[comment]

What would you do if you want to compare 3 years, or more?

I guess I would use 3 portals and 3 global fields...

And why do you think that a Years table as described had any influence on the data model?

I believe the question in this thread is about the data model. A table, IMHO, is a part of the data model. Or perhaps I don't understand your point.

In any case, a table of Years that contains no non-trivial attributes is redundant - and not only at first glance. Even in your multiple windows scenario, you need a record for each window - not a record for each year.

[Fredrik]

First of all I would like to thank you people for helping a newbie such as myself with my elementary problems:-)

I'm trying to get the People table to work with my Texts table but there are some problems. Now that I have consolidated Authors and narrators into a People table I want to put fields on my Texts layout for entering author and narrator. I have also created a join table where I have added a field called Role. I got an author field working by using a single row portal with a pop-up menu field with the foreign key for people ID and a value list with the names from People. I set it so that when I add an author a record is created in the join table, and a script trigger on modify to set the role to author. This worked very well. But the trouble is when I try to the same on a narrator field I just get the same record entered on the author. I understand that I could use just one portal and add the role field to it for creating both authors and narrators, but I would really like to have to fields on my layout with their own labels.

[comment]

I want to put fields on my Texts layout for entering author and narrator.

If you are using a join table, then this information would go in there. Otherwise each text can have at most one author and one narrator. In such case you do not need a join table, only two relationships between Texts and People.

Note that in Filemaker having two relationships between the same two tables requires at least one of the tables to have two occurrences on the relationship graph.

[eos]

I believe the question in this thread is about the data model. A table, IMHO, is a part of the data model. Or perhaps I don't understand your point.

How exactly would a table containing log-entries, audit trail or stored summary data be part of the data model?

Even in your multiple windows scenario, you need a record for each window - not a record for each year

Yes, one record for each window; when each record “is” a different year, where's the difference?

[comment]

How exactly would a table containing log-entries, audit trail or stored summary data be part of the data model?

I don't know. I haven't proposed any such table, and the question seems to me to be both too vague and off the current topic.

Yes, one record for each window; when each record “is” a different year, where's the difference?

The difference is that a table of Windows would contain a unique record for each window - either created at the same time as opening a new window, or perhaps a "bank" of possible windows - while a table of Years would have a record for each year - either the actual year used, or all the years in the usable range.

[eos]

Well, I imagine I know what the difference between a table of Years and Windows is, but where does this "table of Windows” come from

in the first place? To quote myself: “Go to the Years table and call the record for 2011. […] Open a new window and switch to the 2012 record…“

So all of this is supposed to happen in a Years table - which btw is supposed to be convenient, absolutely non-interface- or -data model-determining and totally irregular!

Thank you for the discussion, but I suggest we call it a night (at least that's what it is where I'm at). The main thing is that OP Fredrik will build a nice, fully normalized book database to his liking, which should be child's play now, what with all the input and good advice he got.

PS: As you can see, the smiley palette has some really cute specimens. I wish people would use them more often (or maybe not, there is such a thing as too much cuteness).

[Fredrik]

I would suggest only one table for 'people' called Authors, combining author and narrator and include a Type field to designate which they are. You could also just use a flag field if they are narrator but later you might want to add another type of 'person' so by using a Type field, you would have more flexibility.

I would be happy to provide a sample file[…][THAT WOULD BE GREAT!]

I'm trying to get the People table to work with my Texts table but there are some problems. Now that I have consolidated Authors and Narrators into a People table I want to put fields on my Texts layout for entering author and narrator. I have also created a join table where I have added a field called [book]Role. I got an author field working by using a single row portal with a pop-up menu field with the foreign key for people ID and a value list with the names from People. I set it so that when I add an author a record is created in the join table, and a script trigger on modify to set the role to author. This worked very well. But the trouble is when I try to the same on a narrator field I just get the same record entered on the author. I understand that I could use just one portal and add the role field to it for creating both authors and narrators, but I would really like to have to fields on my layout with their own labels.

I'm sorry to bother you more with this, but I can't get it to work. I think I might have to use different TO's with different matchings in the relationships but I don't have enough understanding of this to pull it off. As I tried to explain in the above post I would very much like to have authors and narrators in different fields with their respective labels on my layout Texts/Books layout.

And I would like to have the possibility to have a person be both author and narrator on the same book and for different books. And on a People layout I would like to have two different portals showing the books the person has authored and narrated.

So if someone could help me with an example file or a detailed explanation about which fields (Flag fields, Type fields or what ever) I would need in which tables and how many TO's and their relationships to the join table I would be very grateful.

[eos]

Please post your (zipped) database; at least for me it's easier to check things with your sample data than

to set up a database from scratch.

[Fredrik]

Please post your (zipped) database; at least for me it's easier to check things with your sample data than

to set up a database from scratch.

Well I don't know if that would be of much help since it's quite chaotic and all most every name for fields, tables, TO's, scripts and so on are in Norwegian.

[comment]

See if this helps:

TwoRoles11.fp7.zip

[Fredrik]

See if this helps:

Thank you very much for the demo file. It has absolutely helped my understanding of my problem.

But what if I want a many-to-many relationship. If I want an Article to have many authors and/or many narrators? Do I need just one join table or more? Or do I just need different TO's of a join table? And last but not least: Do I need a join table between all three TO's of the People table and Article table or just between the Articles and People, or just between Articles and Authors and Narrators?

I hope that wasn't explained in a to confusing way :confused:

[comment]

If I want an Article to have many authors and/or many narrators?

Then you would need to use a join table. You can have a separate join table for each role (e.g. Authorships and Narrations), or one combined join table where the role is a field.

[eos]

Or have a look into this one. Note that this is quite rudimentary (e.g. for

selecting persons a pop-up isn't the best tool when you have lots of them),

but it should give you a clearer picture of the overall structure.

BooksSample_eos.fmp12.zip

[Fredrik]

First of all I would like to thank you for all the help I have gotten here. I'm really getting somewhere now;-) This is a great forum!

But of course I have some more questions:

See if this helps:

I have been trying to implement your model for my database and I'm getting some good results. But I have one problem with the match field that you called cPeopleIDs. I have created two join tables that go between Authors and Narrators. A consequence of this is that the fields AuthorID and NarratorID are in the join tables and therefore become related fields in the cPeopleIDs calculation. This causes the field cPeopleIDs to be unstorable and not indexed. And therefore it doesn't work as a match field (I think). I would really like a solution for this, but I frankly have no idea what to do! Can I create another match field or is there another workaround?

[…]for selecting persons a pop-up isn't the best tool when you have lots of them[…]

I'm interested to know what you think is a better way to do this.

[comment]

But I have one problem with the match field that you called cPeopleIDs. I have created two join tables that go between Authors and Narrators.

You cannot mix'n'match the two approaches. With a join table, all the information about a join (e.g. Adam is an author of "Book of Eden") goes into the join table - and nowhere else.

See also:

http://www.fmforums.com/forum/showpost.php?post/246136/

[Fredrik]

With a join table, all the information about a join (e.g. Adam is an author of "Book of Eden") goes into the join table - and nowhere else.

Sorry, I should have made my problem more clear. Everything with the join tables are working just fine. My problem is connecting the Persons TO to the Articles so that I can base a layout on the Persons TO and get the right information in the portals there.

[comment]

With a join table, you do not connect People to Articles directly - that's what the join table is for. You can, however, place a portal to Articles on a layout of People (and vice versa).

[eos]

Sorry, I should have made my problem more clear. Everything with the join tables are working just fine. My problem is connecting the Persons TO to the Articles so that I can base a layout on the Persons TO and get the right information in the portals there.

Fredrik, why don't you take a closer look at my example DB? The Relationship Graph shows how a join table does exactly that - joining Books with Persons, Books with Genres, Persons with Countries etc. Check how the portals on the Person and the Books layouts combine information from the join table, as well as from tables several “hops” away.

[Fredrik]

With a join table, you do not connect People to Articles directly - that's what the join table is for.

I have not created a join table between People and Articles - that is still a direct relationship, but the match field doesn't work with the calculation in your example. I have created a join table between Articles and Author, and another one between Articles and Narrator. It is possible that there is no way of getting this to work. but I at least wanted to make that clear, and it would have been such a simple and elegant solution.

Fredrik, why don't you take a closer look at my example DB? The Relationship Graph shows how a join table does exactly that - joining Books with Persons, Books with Genres, Persons with Countries etc. Check how the portals on the Person and the Books layouts combine information from the join table, as well as from tables several “hops” away.

I will definitely take closer look at your DB tomorrow. And hopefully I will figure it out.

Thanks again, and good night:-)

[comment]

I have not created a join table between People and Articles - that is still a direct relationship, but the match field doesn't work with the calculation in your example. I have created a join table between Articles and Author, and another one between Articles and Narrator.

I am not sure if you realize that in my demo, Author and Narrator are merely additional occurrences of the People table. In any case, the direct relationship is not an option when the true cardinality is many-to-many.

Before going any further: why do you prefer having two join tables over one? Two join tables would make sense only if the fields required to describe an authorship are significantly different from those that describe a narration - and even then a single join table might prove the more practical solution.

[Fredrik]

Before going any further: why do you prefer having two join tables over one?

This was just something that I tried to get my DB to work based on your demo, but it didn't work for me so I have abandoned this approach.

Fredrik, why don't you take a closer look at my example DB?

I have taken a closer look now and I discovered that I could solve my problem by adding a Roles table and doing some sorting in my portals. So that worked just great!

But I have one more problem with my DB that I'm not quite sure how to solve and that I hope you could help me with. And that is the events table. I'm not sure if I need a join table for it or not? What I want to do is enter a publication date on my texts layout while filling in the other information about a text. And I am thinking about the scenario when a text has more that one author and therefore a publication event is tied to them all. And also I want to fill in date of birth and death on the peoples layout. And what if I want to add an event for a country - like Independence Day or something?

[LaRetta]

Hi Fredrik,

The same table can feed many tables. You see this with Notes tables where you may wish to add a note into Customer record, and add a note into an Invoice, or add a note onto a Product. Just add the primary key from any table into your Events table, create a relationship with Allow Creation on and attach a table occurrence of Events anywhere it is needed (see example). There is only one Events table of course and the _A signifies 'allow creation' is on.

BTW, in the example I've attached, the Addresses table is your Countries table.

Edited June 4, 2012 by LaRetta

[Fredrik]

Thanks for the events example LaRetta! I'm really beginning to get a greater understanding of table occurrences and the relationships among them. And I think this topic is nearing it's end now. Thank you all for helping me out with my elementary problems - I truly appreciate it:-)