Jump to content

  •  

Photo

One to Many to Many


  • Please log in to reply
39 replies to this topic

#1 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 30 October 2011 - 01:53 PM

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

#2 bcooney  consultant

bcooney
  • Moderators
  • 5,788 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 24d 50m 57s

Posted 30 October 2011 - 02:44 PM

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

#3 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 30 October 2011 - 04:08 PM

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

#4 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 01 November 2011 - 03:33 AM

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

#5 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 01 November 2011 - 04:09 AM

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

#6 bcooney  consultant

bcooney
  • Moderators
  • 5,788 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 24d 50m 57s

Posted 01 November 2011 - 05:29 AM

"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

Attached Files


  • 0

#7 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 01 November 2011 - 07:18 AM

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

#8 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 01 November 2011 - 07:49 AM

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

#9 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 01 November 2011 - 08:01 AM

they might have written one with a different publisher.


In what way is this relevant to your solution?


---
OP = original post/poster
  • 0

#10 bcooney  consultant

bcooney
  • Moderators
  • 5,788 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 24d 50m 57s

Posted 01 November 2011 - 08:27 AM

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

#11 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 01 November 2011 - 08:51 AM

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

#12 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 01 November 2011 - 09:41 AM

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

#13 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 01 November 2011 - 01:20 PM

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

#14 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 01 November 2011 - 01:29 PM

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

#15 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 01 November 2011 - 03:03 PM

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

#16 Vaughan  Mostly Harmless

Vaughan
  • Moderators
  • 10,294 posts
  • LocationSydney, Australia
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:8, 9, 10, 11
  • Membership:TechNet
  • Time Online: 4d 9h 48s

Posted 01 November 2011 - 04:51 PM

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
Vaughan Bromfield
Sydney, Australia

Please post questions to the Forum, not directly to me. Back-up your files before making changes!

Whenever I hear the term "popular culture" I reach for my Iridium Q-36 Space Modulator.

#17 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 01 November 2011 - 05:12 PM

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

#18 comment  consultant

comment
  • Members
  • 24,468 posts
  • Time Online: 341d 1h 49m 13s

Posted 01 November 2011 - 05:46 PM

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

#19 jn08  novice

jn08
  • Members
  • 50 posts
  • FM Application:11 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Time Online: 3d 19h 8m 45s

Posted 02 November 2011 - 12:05 AM

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/...__fromsearch__1
  • 0

#20 bcooney  consultant

bcooney
  • Moderators
  • 5,788 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 24d 50m 57s

Posted 02 November 2011 - 04:36 AM

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




FMForum Advertisers