One to Many to Many

[bcooney]

If it's a key - it's an ID. That's my rule. Catchy, huh?

[comment]

So no 0.1%?

[bcooney]

Got one! Gender: Male / Female - that's a custom value list with no IDs, radio set.

In fact, it's a bit of a story on our team, since the customer reminded us that some people find that question offensive.

[Wickerman]

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.

I was referred to this thread from elsewhere, and I thought I might just make an attempt at elucidation for the OP. I think you were originally envisioning would make a problematic relationship graph:

Contacts > Roles < Contributions > Products

This makes my head ache a little bit just trying to explain why it doesn't work, but I think it has to do with Comment's comment that you are conflating The idea that Contacts may generally fulfill different kinds of Roles with the fact of their performing thatRole in a particular Product.

To put it another way, coming from the *left* you seem to intend Roles like a checkbox on the Contact record, ticking off Contact skills . . . but coming from the *right* side of the graph, you are treating the Roles as thought they are *people* who actually performed the Credit Contribution Roles in a particular book!

SO, if you build it this way and go from a Product Record, you never actually *get* to a Contact name, because there's no way of knowing which Contact is involved, because each Role record can have Many Contacts!

I think what you intend is instead simply

Contacts < Contributions > Products

. . . and *within* the Contributions Table, you want a simple FIELD called "Role" specifying what particular Role the Contact Fulfilled in this particular instance of participation (this "Credit"). So the idea is like: "Hemingway contributed to 'The Sun Also Rises'! [Create Contribution Record] -- Oh Really? How did he contribute? -- "He was the Author!" [Enter 'Author' in Role Field of Contribution Record]

It can be useful to think about other fields that can be usefully placed in this join table -- for instance, how much the person was paid for this contribution. ("How much was he Paid?" or "What kind of Pen did he use?" -- you can store all sorts of things particular to the fulfillment of that Role *for that particular Product).

If you want to be able to search for which contacts are available to perform various kinds of work in general, that would be a Field in the Contacts table -- perhaps a checkbox, or perhaps a sub-table if you want to go the more fine-grained approach.

Forgive me if I've mis-read the scenario -- I'm not as deeply skilled as most here, but I do a lot of similar sorts of solutions, and this sounds familiar to me.

[Wickerman]

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.

Contacts > Roles < Contributions > Products

Actually, looking again right away I think I got that original interpretation wrong. Should be:

Contacts < Roles < Contributions > Products

. . . but I think my general line of thought holds. Because now it looks like you're going to be making superfluous Role records - you have say 35 Role records that are all "Author", and they only become differentiated by their parent 'Contact' record.

Comment's original suggestion of a 'star shaped graph seems better (and is a variation on having a Roles field on the Contributions Table as I suggested).:

Contacts < Contributions > Products

..............................|

...........................Roles

Each contribution is a single Contact performing a single Role for a single Product.