I need to creat one masterNames table to provide names for several many to many relationships

[chrisford]

I am a Medieval Historian, not a db engineer, and am seeking to create a database akin to a Film example I have used in learning videos. The only difference is that my ‘directors’ could also be ‘actors’ and vice-versa and ‘scriptwriters’ could also be ‘actors’ and/or ‘directors’. I therefore want to maintain only one master list of names to feed my many to many junction tables. This has not worked but creating copies of the names list, renaming them as director/actor/scriptwriter, achieves the desired result. I then created one-to-one relationship between MasterNames and the copy Names tables in the hope that I could input new names and find them updated in the satellite lists. Foolish me! Is there an obvious solution to keeping the different names tables synchronised?



I would be enormously grateful for any pointer to set me on the right path.
Best wishes
Chris Ford

[JoeM]

Not quite I sure I totally understand your data model, mostly because I am not clear on the relationship to your name and titles.
You say that each person could be directors, actors, or scriptwriters, or any combination of those. Is that "on the whole", or are you tracking that by movie?

It might be helpful if you could post some sample so we could see what your data looks like and exactly what you are trying to accomplish. You can upload a copy of your database here.

[June7]

One name table can serve as source for any number of entities (actor, director, screenwriter, etc).

As JoeM said, need to know more about your data structure. How many junction tables and what are they for?