One to Many to Many

[jn08]

Hi everyone,

I've been trying to put together a contact / product / role relationship in my database and it's doing my head in. I think I might not have the entities quite right. This is a book database with multiple contributor role types (author, editor, designer etc). Each product is a book.

Each contact has multiple contributor roles (one to many).

Each contributor role is associated with multiple products (many to many).

Each product has multiple contributor roles (many to many)

What I've done is create a contacts table, which is the parent to a roles table. I associate each contact to multiple roles.

I then have a join table (called contributions) linking roles to products.

If anyone can follow any of that, I'd appreciate your help in re-thinking it.

Cheers.

[bcooney]

People, Roles and join btw. ( if each person can be more than one role (both an editor and author)).

Join btw Product and People (Contributions), each join record has a RoleID and PeoID, you can add Peo to the join filtering by their Roles in the PeoRole table.

[comment]

Each contact has multiple contributor roles (one to many).

Each contributor role is associated with multiple products (many to many).

Each product has multiple contributor roles (many to many)

If I follow this correctly, the Contributions table should have a ContactID, a RoleID and a ProductID - thus joining the three tables in a "star-join", e.g.:

[Adam] is an [author] of [Plastic Delights]

[betty] is an [editor] of [Plastic Delights]

[Adam] is a [translator] of [blokes & Sheilas]

etc.

[jn08]

Not quite, comment. Because the relationship between People and Books goes via Roles (and via contributions - the join table), only People with associated Roles can be selected as contributors from the Product context.

I still think I might be doing it wrong, though. I created a one-to-many relationship between People and Roles, then a many-to-many between Roles and Books via a join table (this is the Contributions table). The reason being is that I thought a person could only be linked to a book if it had a role associated with it (though I guess this doesn't actually matter). However, people can be associated with roles without being a contributor to a book.

My concern is that under the model that bcooney proposes, each person could potentially have many duplicate roles associated with them. Right? I'm not sure. A person can be an author and an editor, but they can't be an author and an author (though they can be the author of two different books). Is there a way to restrict the values list used to set foreign IDs in the Contributions table to restrict values to the related contact?

For example: John is an editor, but I've neglected to allocate him that role from the People context. While in the Books context on The Big Book record, I remember to add him as a contributor. From The Big Book record on the Books layout, the user will be able to select values from a pop-up for Person and Role. Then let's say I go to The Little Book record, and also add John as an editor. Wouldn't this mean that when I go back to the People context, John's roles include Editor and Editor?

What I'd like to do is restrict the roles that can be allocated to a person (in the Books context) based on whether that role has already been allocated to them in the People context. But I guess I need a way to indicate to the user that the reason they can't allocate a role is because it needs to be allocated from the Contacts layout. Unless I can set it up so that they *can* create a Role record from the Books context, so long as it won't duplicate them. That would be the ideal, but I'm not sure if it's possible.

Hope some of that makes sense, this is very complex (for me).

[comment]

To which part does the "not quite" comment apply?

[bcooney]

"My concern is that under the model that bcooney proposes, each person could potentially have many duplicate roles associated with them. Right? I'm not sure. "

You can script the assignments to roles so that duplicates are not allowed.

demo attached

PeoRoleProduct.fp7.zip

[comment]

I'm used to not getting the OP - but now you too, Barbara?

Let me put it this way: How does a person become an author? Does one need to actually write a book? Or can we just appoint someone as "author" because we feel they could become one, if they apply themselves?

Note: if you answered yes to the last question, then you have two types of "author"; one is a skill and the other is credit. The two should not be confused.

[jn08]

The people may be authors, but they may not have authored a book in my book database - they might have written one with a different publisher.

Thanks so much for your help, guys. I'll check out the example bcooney posted tonight and let you know how I go.

(What's the OP?)

[comment]

they might have written one with a different publisher.

In what way is this relevant to your solution?

---

OP = original post/poster

[bcooney]

I'd like to consider that I'm an author -- just for the amount of posts I write! Comment, your responses are often much more nuanced than most people (myself included) understand.

I assumed assigning a person to a role wasn't the main issue, it was more technique than philosophy. :)

[jn08]

It's relevant to the solution because sometimes I have contacts who are authors, and I want to be able to indicate that they're an author (and search by this role). It might be that they're also an editor or a designer or something else, or they might just be a prospective author who has made it into my contacts list for one of many reasons.

There are lots of reasons why I want to be able to assign particular roles to particular contacts without them being connected to a particular product. For example, I would like to be able to assign the copyeditor role to multiple contacts without them having done any editing on a book in my book database. I may have worked with them before elsewhere, but they haven't necessarily worked on any products in this database. Same goes for media contacts. If I can assign roles and then search by particular roles, I can export mailing lists, search for freelancers with particular interests and so on and so forth.

[comment]

OK, but then you are not speaking of roles in the meaning of "what was your role in project XYZ". As I said earlier, these are skills, or categories - and they are completely independent of the roles these people played in their actual contributions to your projects.

IMHO, the categories of a person could be handled simply by a checkbox in the Contacts table, using a custom value list.

[jn08]

True, though wouldn't that mean I'd have to have as many fields as there are skills? Otherwise each contact could only have one. Right?

[comment]

No, using a field formatted as a checkbox you can select as many skills as you want. The actual content of the field is a return-separated list of the selected skills.

Note that this assumes a Contact-Skill join has no attributes - otherwise it's back to Barbara's suggestion in post #2.

[jn08]

Oh, really? That makes things a bit easier. No, there are no other attributes, just the type of role. All right, I'm going to go make my database vastly more simple ...

[Vaughan]

All right, I'm going to go make my database vastly more simple ...

There are limitations to having all the roles in a single field formatted as a check box: you cannot print a summarised list of people and their roles, for instance.

If the requirements are simple then the checkbox is fine... but often the requirements are not simple.

[jn08]

I don't think I'll need to print it, so that's not an issue. However, I would like to be able to indicate (with the use of a script of similar) whether a particular contact is a particular role. I tried to use:

filtertext(Role ; "Author") = "Author"

But that didn't seem to work. Plus I think it'll get confused if there's an "Author" role and a "Co-author" role.

[comment]

you cannot print a summarised list of people and their roles

Yes, I meant to mention that as well, but got distracted.

I tried to use:

filtertext(Role ; "Author") = "Author"

filtertext?! There is no such function. Try =

not IsEmpty ( FilterValues ( "Author" ; YourCheckboxField ) )

[jn08]

Ah, yes. I meant filter, not filtertext. I don't know where I got that from. Didn't work anyway, but your solution did. I might ask my other question over at the other thread, which is more directly related.

http://fmforums.com/forum/topic/80953-function-to-determine-whether-a-data-from-multiple-related-fields-contains-a-particular-value/page__fromsearch__1

[bcooney]

Second vote against a checkbox set. Do the Join table. If you'd rather not call the table, Roles, then call it Categories.

[jn08]

Why? I don't think there's a sensible way to make the relationships work between contacts -> roles <- books. Or do you suggest making the 'categories' a separate table not linked to products at all (in just the same way as the roles checkbox field is not related now?). If so, what's the benefit? The checkbox field makes it very easy for someone entering data to see what roles are available and to allocate multiple roles without navigating a portal. A separate tab of the layout can show the contributions, and in that instance the role can be specifically set for a particular product, which is quite useful.

[Vaughan]

I've seen many systems -- and I'm sure other devs here have too -- where it was *really* easy to do data entry, but almost impossible to do anything useful with the entered data. Which makes the data worthless and the money spent in system development and data entry wasted.

[comment]

There's a fine line between warning and panic-mongering. A checkbox field is a perfectly legitimate device - and data entered into it will remain useful even if in the future it becomes necessary to convert to a join table.

[bcooney]

@jn08 - Yes, Categories is a separate table, and you would create a join table btw People and Categories (like I did in the demo, but I called the table "Roles").

The benefits to a join table are:

Consistency of data entry - you are storing IDs, not text values. If you decide to change the text value of a Category description, you do not lose the ability to report on all People who are tagged with that Category. For example, 2 yrs from now you change Author to Writer. It is still CategoryID 100. So, all join records still are valid, and globally all instances of "Author" become "Writer." However, with a checkbox, the values in the field are not updated when you change the value list. So, when you do a find for Writers, none of the Authors will be found.

Also, a related value list is much easier to construct if you have a join table.

What else? Wouldn't you want a Category form view that shows all the People who are Authors? Easy to do (and I provided one in the demo). Can't do it if you use a checkbox.

Reporting. Designing a report from the context of the join table will easily allow you to list all the people by category.

20+yrs developing in FM and I can count on one hand my use of a checkbox array.

[comment]

OK, let's go to war on this one:

with a checkbox, the values in the field are not updated when you change the value list.

That's not true. You can use IDs with a checkbox too. What you are attacking here is the use of the values themselves (which you can also do with a join table). In any case, even with a custom value list, renaming a category is possible - and I'd rather build a script allowing the users to do so than add two tables to the solution (and who knows how many TOs to the RG, if you're using anchor/buoy...).

a related value list is much easier to construct if you have a join table.

I'd say exactly the opposite: since the category is a local stored field, defining a filtering relationship is a breeze.

Wouldn't you want a Category form view that shows all the People who are Authors? Easy to do (and I provided one in the demo). Can't do it if you use a checkbox.

And why not?

Designing a report from the context of the join table will easily allow you to list all the people by category.

That is true. Well, partly true: you could still produce such report using a portal, or a calculation field using List(). However, this is the point of this entire discussion: if you do not need such report, why spend so many resources now on the possibility you'll want it in the future?

[bcooney]

Not up for the "war," Comment dear, and I don't agree with your assertion that a join table requires more resources than a checkbox array. "I'd rather build a script allowing the users to do so than add two tables to the solution" - scripts are easier to add than tables?

You did not mention IDs. In fact, you said,"The actual content of the field is a return-separated list of the selected skills."

Perhaps you meant that the field will contain IDs, but what ID? There's no Category table in your proposed solution, is there?

"Note that this assumes a Contact-Skill join has no attributes - otherwise it's back to Barbara's suggestion in post #2."

You never know the full requirements of the OP, huh? I always assume the need for a join table, and I'm usually right. :)

[jn08]

I must say that the checkbox array as a visual feature of the contacts layout is much easier to follow (and fill out) than a portal with a bunch of drop down boxes.

[comment]

You did not mention IDs. In fact, you said,"The actual content of the field is a return-separated list of the selected skills."

I thought we were talking about checkboxes now. A checkbox can contain a return-separated list of IDs OR a return-separated list of actual values (usually, this would use a custom value list, but it doesn't have to). In a business that has established categories, I wouldn't worry too much about them changing. By "worry too much" I mean "add resources to the file to make it easy to happen". It's all a question of balance. Do you use a value list of "NY¶NJ¶CT" or do you plan for the contingency of New York changing its name? Either way, this has nothing to do with checkboxes, because the same question applies to a join table, too.

I always assume the need for a join table, and I'm usually right.

I never assume anything, and (therefore) I'm always right...

[bcooney]

@jn08 - there are other interface choices. You could have a portal of Categories and click an arrow to assign them to the Person. What I'm focusing on is the actual data model.

@Comment - If I need to report by state, then yes, I have a table of States from which I'll build a value list. That'll let me have the full state name as well as the abbreviation.

In fact, in a system I'm working on now, we have a table of all the countries in the world. UK gets tricky, as the Scottish refuse to select UK, and Scotland is a country within the country of the UK. But I digress...

[comment]

If I need to report by state, then yes, I have a table of States from which I'll build a value list. That'll let me have the full state name as well as the abbreviation.

That's not the point here. Do you select the state name or abbreviation - or do you select an ID?

[LaRetta]

I never assume anything, and (therefore) I'm always right...

I'm stealing that. :jester:

[bcooney]

I store an ID and show a state abbrev or name.

That is all I'm in a huff about, Comment. I simply advocate always storing an ID. I'm not big on multi-keys either (although I've used them).

[comment]

I store an ID and show a state abbrev or name.

OK, so you do plan for the contingency of New York changing its name.

That is all I'm in a huff about, Comment. I simply advocate always storing an ID.

No, that's not "all". There are two separate issues here: (1) do you always use IDs, or can you sometimes use actual values; and (2) do you always use a join table, or can you sometimes use a return-separated list. I guess we have now established we disagree on both...

[bcooney]

(1) I can't say always. 99.9%

(2) Again 99.9% of the time it's a join table.

I work on projects primarily where development practically never ends bcs features are constantly added. I always build for the "just in case" to avoid any data conversion. So, yes, I come to these requests on this forum with "baggage."

So, we agree to disagree. That's fine with me. I'm sure we've entertained the forum participants.

[comment]

OK, I'll get off your case now. Though I am still curious: if you won't allow "NY" as a primary key value in the States table, what then is your 0.1%?