Sign in to follow this  
Followers 0

One to Many to Many

40 posts in this topic

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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).

0

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

"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

0

Share this post


Link to post
Share on other sites

Posted

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

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.

0

Share this post


Link to post
Share on other sites

Posted

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?)

0

Share this post


Link to post
Share on other sites

Posted

they might have written one with a different publisher.

In what way is this relevant to your solution?

---

OP = original post/poster

0

Share this post


Link to post
Share on other sites

Posted

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. :)

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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?

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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 ...

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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 ) )

0

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

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

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

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.

0

Share this post


Link to post
Share on other sites

Posted

@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.

0

Share this post


Link to post
Share on other sites

Posted

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

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?

0

Share this post


Link to post
Share on other sites

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
Sign in to follow this  
Followers 0