Relationship woes

[hank101]

Hi folks,

I'm creating a database and I'm having problems with relationships (I am a guy after all). I have 3 tables set up,

Authors

Agents

Manuscripts

I'd like to import all the old data I have into these layouts, all the fields are correct. Going forth, when creating new manuscript records, I'd like it to grab the information from the Authors and Agents tables, if the Author already exists in the database. I have serial number fields and have had it work to grab the Authors info, but not the Agents.

I've attached a copy of my database, it's still in the beginning stages, so nothing is final.

Any help would be appreciated.

thanks in Advance

Hank101

Manuscript_Testing_Clone.fp7.zip

[Fenton]

You need a more direct relationship between Manuscript and Agency, based on the chosen Agency foreign key for that Manuscript.

Also, you have an Author foreign key in the Agents table. This implies that there is only 1 possible Author being handled by that Agent. If that is not true, then you need an AgentAuthors table to handle multiple authors per agent. If it is true that an author could have more then 1 agent, then it would function as a "join" table. Otherwise it's a child of Agents.

The answers to these questions determine how you'd use the above with Manuscripts. Possibilities: Choose an author, lookup their latest agent (or choose an agent from list of their agents)? Choose an agent, filter authors to their authors? Etc..

Remember, a Relationship Graph is not an ERD (Entity Relationship Diagram). It will have (many) more table occurrences than the base tables, for functionality (or organization).

Manuscript_Testing_fej.fp7.zip

Edited September 16, 2008 by Guest
Forgot file

[hank101]

Hi Fenton,

thanks for your response and help, I will look into the questions you brought up and see what Information I really need.

The answers to these questions determine how you'd use the above with Manuscripts. Possibilities: Choose an author, lookup their latest agent (or choose an agent from list of their agents)? Choose an agent, filter authors to their authors? Etc..

I think I'm leaning towards finding an Author, then creating a new manuscript entry, showing all the data from Author/Agent, then adding the new information. I have to investigate more in what's needed, I did add some new stuff, since this Is is an upgrade from an old FM3 database.

thanks again, I'll get back to you if/when I have more questions.

hank101