Defining relationships

[eburg]

I have 7 tables: pub, writer, editor, client, articles, pub group, adreps. Each has an autonumber primary key. Of the 42 relationships between them, 12 are one to may, but 30 are many to many.
I want to query all combinations of clients, pub, writer, editor, and article. Also, all combinations of pub, pub group, and adreps.
Do I need a junction table for each many to many, or can I combine them? My understanding is that generally junction tables contain the primary key from 2 tables. What is the best way to define the relationships between these tables?
Thanks in advance.
Eric

 

[willir]

Eric

For a "true" many-to-many relaitonship, you do need a joiner or intermediary table. But sometimes, the "join" may be included in another table.

For example, a writer may write articles for different publishers. Likewise, a publisher will publish articles from different writers.

Assuming...
tblWriter
WriterID - primary key
WriterLN
WriterFN

tblPublisher
PubID - primary key
PunName

The Article, can be joined two ways, depending on how you handle the Artciles table.

If the Article table is separate, which means that the same article can be published in more than one location...

tblArticle
ArticleID - primary key
ArticleName
WriterID - foreign key stored here for writer

Joining table would be...

tblPubArticle
PubID - foreign key to publisher table
ArticleID - foreign key to article table
PubDate
+ other info

...OR...
If an article can only be published by one publisher...

tblArticle
ArticleID
ArticleName
PubID
WrtierID

Here, the "joiner" table is actually the part of the Article table.

...Next...
Some data is "static" - it is unlikely to change often. For example, Country, State / Provence. You may handle this several ways... use a value list in combo / list boxes, or separate tables for each.

A cheat I use is a Category table for storing static variables...

Category
CatType - text, 8 to 25
CatCode - text, 8 to 15
CatDesc

Primary key is CatType + CatCode

CatType CatCode
Country USA
Country UK
Country Canada
StatePrv Texas
StatePrv New York
StatePrv Sussex
StatePrv Quèbec
PubCat Mystry
PubCat SciFi

I find this approach cuts down on some of the tables -- but it is not for M:M and only for static data.

Hope this gives you some ideas...

 

[eburg]

Thanks Willir,
I see now. Create tblArticle like this:

tblArticle
ArticleID - primary key
PubID
WriterID
EditorID
ClientID
Title
Date

Then I can create a form with 4 combo boxes populated with records of the foreign keys. NotInList can trigger appropriate forms to enter data in the foreign key tables.

This will allow querying any combination of Pub, Writer, Editor, Client, Title, and Date. Correct?

Thanks again,
Eric

 

[willir]

Absolutely.

But I say again. Make sure you can get the reports and information you need to meet the objective of the database. Work it out on paper. Be thorough and thoughful with this stage.

Here, with your design, you are limiting the Article to a specific publisher and writer. Which may be perfect for your needs. Just make sure it works for you, and that you will not have a situation where the same article may appear in different publications. OR have plan in how to handle this event if it happens.

With a well designed database, it should be very easy to grab most of the requried info, and a tad hard to grab the last bit.

With a poorly designed database, which may seem simipler to design, you fight with the data all the time.

I did a post last year on a somewhat simialr database. It may shed some light for you...

http://www.tek-tips.com/viewthread.cfm?SQID=628486&SPID=700&page=4

Also, some articles (pardon the name - articles ;-) ) to review on design and setup...

http://www.abcdataworks.com/fundamentals.htm

(Word) OR (HTML)

http://r937.com/relational.html

And...

http://www.mvps.org/access/tencommandments.htm

Richard

 

[eburg]

Richard,
Thanks for your help. Hope this thread is still alive. My attention was demanded elsewhere.
Back to the design. I have the following tables:

tblPublications
PubID
fields for contact, rates, etc.

tblWriters
WriterID
FN
LN

tblEditors
EdID
PubID
FN
LN

tblClients
ClientID
Name
Contact FN
Contact LN

tblKeyword
KeyID
ArtID
Keyword

tblArticle
ArtID
PubID
WriterID
EdID
Title
Date
Size

tblArticleClient
ArtID
ClientID

In setting up the relationships, I find I can't enforce integrity in some cases. Here are the relationships:
1. A publication can have many editors, an editor has one pub.
2. A publication can have many articles, an article has one pub. Both of these produce 1:M with integrity.
3. A writer can have many articles, an article has one writer. This won't allow referential integrity.
4. An article can cover many clients, a client can have many articles. I use tblArticleClient to create two 1:M, but again no integrity in either join.
5. An article can have many keywords, a keyword can appear in many articles. Do I need an intermediate table to get two 1:M relationships?
I'm having trouble designing a form to input the info from articles, but I suspect the faulty relationships contribute to this.
Am I on the right track here? Why no integrity in some cases?

 

[lespaul]

Here are some minor suggestions/changes:

tblPersons
PersonID
FName
LName
Type (editor or writer - if a person can be both this would need to be changed)

tblPublicationEditors
PulicationID (FK to tblPublication)
EditorPersonID (FK to tblPersons)

tblArticle
ArtID
PubID
WriterID
Title
Date
Size

(removed editor from tblArticle, editors belong to publications not articles)

tblKeywords
KeywordID
KeywordDesc


tblArticleKeywords
ArticleID
KeywordID

leslie

 

[eburg]

Thanks for the quick response!
Editors are bound to the Publication, but writers are not.

 

[eburg]

Leslie,
I made the changes you suggested, except I left writers and editors separate. I still have two integrity issues.
1. When I join tblWriters with tblArticle on WriterID, I can't enforce integrity.
2. Joining tblClients with tblArticleClient on ClientID creates a 1:M with integrity, but joining tblArticleClient with tblArticle on Article ID doesn't enforce integrity.
What causes those two joins to violate integrity rules?
Thanks again,
Eric

 

[DanJR]

And here's another way to arrive at getting a first relational model. I didn't know what the candidate keys were so i guessed...

[tt]
HERM MODEL:
(Entities)
PERSON = ({id, first_name, last_name},{id})
CLIENT = ({client_name},{client_name})
PUBLICATION = ({pub_name,pub_date},{pub_name,pub_date})

(Relations)
EDITOR = ({PERSON, PUBLICATION},{begin_date, end_date},{PERSON, begin_date})
CONTACT = ({PERSON, CLIENT},job_title,{CLIENT})
WRITER = ({PERSON,ARTICLE},NULL,{ARTICLE})
COVER = ({CLIENT,ARTICLE},NULL,{ARTICLE})
ARTICLE = ({PUBLICATION, WRITER},{title},{PUBLICATION,title})
KEYWORD = ({ARTICLE},{word},{ARTICLE, word})


RELATIONAL MODEL:
(Entities)
PERSON' = {id, first_name, last_name}
with minimal key {id}

CLIENT' = {client_name}
with minimal key {client_name}

PUBLICATION' = {pub_name, pub_date}
minimal key {pub_name, pub_date}

(Relations)
EDITOR' = {id, pub_name, pub_date, begin_date, end_date}
with minimal {id}
FK's [id] [⊆] PERSON'[id]
[pub_name,pub_date] [⊆] PUBLICATION'[pub_name,pub_date]

CONTACT' = {id, client_name, job_title}
with minimal key {client_name}
FK's [id] [⊆] PERSON'[id]
[client_name] [⊆] CLIENT'[client_name]

WRITER' = {pub_name, pub_date, title, id }
with minimal key {title, pub_name, pub_date}
FK's [pub_name, pub_date, title] [⊆] ARTICLE'[pub_name, pub_date, title]
[id] [⊆] PERSON'[id]

COVER' = {pub_name, pub_date, title, client_name}
with minimal key {pub_name, pub_date, title, client_name}
FK's [pub_name, pub_date, title] [⊆] ARTICLE'[pub_name, pub_date, title]
[client_name] [⊆] CLIENT'[client_name]

ARTICLE' = {pub_name, pub_date, title}
with minimal key {pub_name, pub_date, title}
FK's [pub_name, pub_date] [⊆] PUBLICATION'[pub_name, pub_date]

KEYWORD' = {pub_name, pub_date, title, word}
with minimal key {pub_name, pub_date, title, word}
FK's [pub_name, pub_date, title] [⊆] ARTICLE'[pub_name, pub_date, title]
[/tt]


For example,
- A PERSON is an EDITOR for a PUBLICATION
- A PERSON is a WRITER of one for more ARTICLES
- A PERSON is a CONTACT for a CLIENT.

 

[willir]

Hi eburg

I can't enforce integrity.

Do you have data in your tables? Are you creating relationships after entring data?

You probably have missing data - specifically, an article without a writer. If you do not know the writer, you may want to consider creating an "anonymous" or other "generic" term for the writer.

Ditto for tblClients with tblArticleClient where you probably have an ArticleClient record without a client.

...Moving on
DanJR makes a valid point about using a contact or person table for tracking all people, regardless of responsibility. This approach has several advantages...
- Avoid duplication of entries if a writer is also and editor type of thing.
- One contact form / table can be used rather than having to hunt down contact info on seperate locations.

However, the "cost" is that you have to wade through other data when selecting writers, editors, etc. (You can add a contact type to address this issue)

One other point about contact info.
Can an article be written by more than one writer? If so, then you would need a joiner table.