Table Relationships Question 5

[muriel]

I need a sanity check on my database design. The database will store bibliographic information and will be queried (explained below). Here's what I have so far.

Main Table
ID
Source Title
Source Type
Name of Periodical/Newspaper, etc
Author 1
Organization 1
Author 2
Organization 2
Author 3
Organization 3
Author 4
Organization 4
Publisher
City
Volume
Number
Pages
Keyword

Source Type, Periodical/News, etc, Author (1-4), Organization (1-4), Publisher, City, and Keyword are all lookup tables. I have to give users the ability to add new entries to the lookup tables, in case there are any new ones.

Question - I will need to query the database to give the following data:
1) Authors and all organizations associated with them
2) Authors and all source titles associated with them
3) Organizations and all authors associated with them

Do I need to create separate tables for source type, peridiocal/news, etc, author, org, publisher, city, keyword - as one to many relationships? If so, should I create author 1, author 2, etc as separate tables?

I will have a main data entry form, so now I'm getting confused as to how to set up the form and where all the data will be stored so we can query the database.

Sorry if this is confusing - I've been thinking about it 24/7 and I really need some advice.

Thanks!

 

[Ogart]

The way I'd do it is this:

1. Have a table for organizations.
ID, Organization, Address, etc,

2. Have a table for Author (assuming they are indepentant of organization)
ID, Author, Phone, etc

2.1 If you really want to be slick, limit the values on the Main Table as a combo box lookup from just the reference tables in 1 and 2. I'd probably keep referential integrity, but a real guru might take issue with this, and I don't pretend to know all your requirements.

3. Have a table that has Main Table ID and Org ID. Another table for Maintable ID and Author ID.

The con is you've got more tables, and the query stuff takes a bit longer to get set up the first go-around. The pro is that you can more easily add to stuff later. I think the term for this is "Denormalized", which is another way of saying, "As little data as possible is repeated anywhere."

THat's my $0.02. A real developer may have a better approach. I'm just a dumb user.

Good luck,
Chris

-->Just because you can, doesn't mean you should.

 

[willir]

muriel

First, I would like to say I am impressed -- you started the right way in that you looked at what you wanted for your outcomes. I have seen many who start the other way and then wonder why they can not get the results they want in their reports.

Before proceeding, you may wish to review rules of Normalization.

I would tweak the design presented by NPSHr in the event you have any many-to-many relationships

tblOrg
OrgID (autonumber, primary key)
OrgName (text)
- plus other information you want to track unique to organization


tblAuthor
AuthID (autonmber, primary key)
AuthName (text, 25 to 35)
- other author information you want to track


tblPublication
'I am assuming that the publisher and organization are different
'I am assuming publication is different than pbulisher

PubID (autonumber, primary key)
PubType (text, 10 to 25, newspaper, journal, trade mag, etc.)
PubName (text, 25 to 50, name of your "source" - "New York Times", etc)
Publisher (text, 25 to 50, you can actually have th epublisher on a different table)
City (text, 25 to 35)
- plus other stuff unique to the publication


tblArt
'Published article
ArtID (autonumber, primary key)
Volume (text or numeric - text probably better)
PageNo (numeric or text - do you get "A1" and "C1" for "page numbers?)
- other info you want to track on bibliography that is unique to bibliography
- we will revisit the design for this table later...



Now for the fun stuff, and this will depend on how you answer some questions. The decision that has to be made is whether there is a one-to-many (1:M) or many-to-may (M:M) relationship. This will affect how you "join" your tables.

Basically 1:M would be an invoice "header" with customer info, purchase order, etc and the invoice "detail" with details for each item on the invoice - product no., prices, quantity, etc. Here, the invoice detail and invoice header is joined using the invoice number stored on each invoice detail record.

A M:M: is more complex. An example is teachers and students in a high school. A teacher can teach many students taking different subjects. A student can have many (well more than one) teacher. Here, you can not store the teacher ID on the student record and you can not store the student ID on the teacher record. You tie the two together using course which includes both the teach and student ID records. (Sorry for going off on a tangent, but this is important since you have to answer the questions.)


Objective: Determine relationship between Author and Organization and answer your questions...

1) Authors and all organizations associated with them
3) Organizations and all authors associated with them
(Again - kudos for asking questions!)

- Can an author write articles for more than one organization?
- Can an organization have more than one author?

If no to either, then store the ID on the "many" side. For example an author writes only for one organization but an organization can have more than one author. For tblAuth, add
AuthID (numeric, long, forein key to author)

eg: tblAuth (will now look like...)
'Assume author belongs to only one organization

AuthID (autonmber, primary key)
AuthName (text)
OrgID (numeric, long, foreign key to organization


If yes to both, you have a M:M. Need to create a relation or join or "profile" table linking both. Your original design suggest that you will have a M:M relationship.

eg: tblAuthOrg

AuthID (numeric, long, foreign key to author)
OrgID (numeric, long, foreing key to orgnization)
- Primary key is AuthID + OrgID
- add other information you want to track unique to an orgnization and author, i.e. owner of orgnization, chief publisher, etc.)



Objective, Next, answer your second question -- Determine relationship between author and articles

2) Authors and all source titles (or articles) associated with them

- Can an author write more than one publication?
- Can a publication have more than one author?
Repeat the analysis for 1:M vs M:M. (I suspect if you are restricting yourself to newspaper articles, you may be able to get away with a 1:M. But if you are including medical and scientific articles, you will have a M:M. Your original design suggest a M:M)

If you will only have one author for an article, then your article table may look like..

eg: tblArt will now look like...
'Published article

ArtID (autonumber, primary key)
AuthID (numeric, long, foreign key to author)
Volume (text or numeric - text probably better)
PageNo (numeric or text - do you get "A1" and "C1" for "page numbers?)


If you have a M:M, and not a 1:M, you have to again create a profile or join table...

tblArtAuth
ArtID (numeric, long, foreign key to article)
AuthID (numeric, long, foreign key to author)
- plus anything unique to the author and article


But we are not done with the publication article table. We have to repeat the process of determining either a 1:M or M:M relationship for the publisher.

For simplicty, I am going to assume that you have a 1:M relationship for publication and articles (a publication has many articles but an article has only one publication), and I am going to assume that you do indeed have a M:M for articles and athors. An example of your artcle table would look like...

eg: tblArt
'Published article

ArtID (autonumber, primary key)
PubID (numeric, long, foreign key to publication)
Volume (text or numeric - text probably better)
PageNo (numeric or text - do you get "A1" and "C1" for "page numbers?)


I saved the best for last -- keywords. I suspect you will have a M:M relationship, and this will become the most powerful part of your database.

I have two thoughts on this. The normalized table would be...

tblKeyW

ArtID (numeric, long, foreign key to publication article)
KeyWord (text, is this going to be one word or allow a phrase??)
Primary key would be ArtID + Keyword

BUT, I think I would use an autonumber on this instead for practicle considerations...

tblKeyW

KeyID (autonumber, primary key)
ArtID (numeric, long, foreign key to publication article
KeyWord (text, again, are you going to allow just one word or a phrase)

You can force the Article + Keyword to unique so as to avoid duplicates.


Does this work?

1) Authors and all organizations associated with them

If an author works for only one orgnation

select tblAuth.*, Org.* from tblAuth, tblOrg where tblOrg.OrgID = tblAuth.OrgID

But for a M:M using the join table tblAuthOrg
(I used Access query for this...)

SELECT tblAuth.*, tblAuthOrg.*
FROM (tblAuth INNER JOIN tblArtAuth ON tblAuth.AuthID = tblArtAuth.AuthID) INNER JOIN tblAuthOrg ON tblAuth.AuthID = tblAuthOrg.tblAuth;

2) Authors and all source titles associated with them

(Going to assume M:M)

SELECT tblArtAuth.*, tblAuth.*
FROM tblAuth INNER JOIN (tblArt INNER JOIN tblArtAuth ON tblArt.ArtID = tblArtAuth.ArtID) ON tblAuth.AuthID = tblArtAuth.AuthID;


3) Organizations and all authors associated with them

Reverse of 1.


Lastly, keywords...

To get Articles using key words...

SELECT tblArt.ArtID, tblKeyW.KeyWord
FROM tblArt INNER JOIN tblKeyW ON tblArt.ArtID = tblKeyW.ArtID;

To get Authors using key words... (A little longer, but it works)

SELECT tblAuth.AuthName, tblArt.ArtID, tblKeyW.KeyWord
FROM tblAuth INNER JOIN ((tblArt INNER JOIN tblKeyW ON tblArt.ArtID = tblKeyW.ArtID) INNER JOIN tblArtAuth ON tblArt.ArtID = tblArtAuth.ArtID) ON tblAuth.AuthID = tblArtAuth.AuthID;


Hope this helps
Richard

 

[Ogart]

Like I said, a real IT person has a better answer. Star for Richard. Very complete answer.

 

[muriel]

Wow, Richard!! Thanks so much!!! That level of detail should point me in the right direction. I'm going to work on this again tonight and will let you know how it goes.

Again, thanks!!!

 

[muriel]

Some followup - I need to set up a form based on this structure to allow users to enter the data correctly. Can I set up my form with fields from different tables, so the data gets stored in the correct table and the queries work right?

Again, thanks so much. I'm continuing to hammer this out.

 

[willir]

muriel

You can do this several ways, and how it works will depend on your needs.

A form can be based on a table or a query. although do-able, I suspect you will want to break up what forms to use for specific tasks.

Try using a "paper" method, and consider what needs to be done by function...

First you will need forms to setup any master files -- organization, publisher, author. This should be done first. The forms wizard is pretty good for this, then modify it. Hint: You will need to choose a style. Yes, you can use graphics or pictures, but this may slow the application if it is to be shared on a network.

Then, using index cards or paper, follow through on how you want your regular users to access the information.

Be aware of your options. Aside from the simple single record or page form, you have...

- subforms
embedded in a main form and usually used to link a parent record to a child record. The parent on the main form. The child on the subform. If done properly, data entered into the child form will be linked (using the appropriate ID) to the parent.

- contuous forms
will display many records matching the criteria for the form instead of one. Often, a contiuous form will be used as a subform, but it will work on a main form too.

- tab form
uses tabs to display pages of data.

- menu or switch board.
No records are displayed -- just a bunch of command buttons used to guide the user to the appropriate task area.

And then mix and match. For example, insert a continuous form on a tab form.

Lastly, the real estate on the screen is expensive. There is a real art to designing a form to display the required information without being too busy, and uses a screen resolution suitable to your users. (Hint: 800x600 or higher - does everyone have 17" or 19" or bigger monitor - anyone using a 15" monitor?)

Okay, having said all taht, here are some ideas...

Have an Organization form that uses a tab form. At the top of the form, have the name of the organization.

On the first tab, have the address, phone number, etc (by moving this information will free up space at the top)

On the next tab, list authors in a continuous subform. This subform is actually built using the tblAuthOrg table. The OrgID field is hidden and is used to link to the main form. Have two combo boxes that use the AuthID field linked to the Author table. In the first combo box, make the last name visible; in the second, make the first name visible. On the subform, have a (small) command button that opens up the Author table.

For the author table, again use a tab form. At the top, have the name. On the first tab, have the Organizaton (or Organizations) - a (small) command button opens up the Organization form. On the second tab, have the articles on a contuous form. For the article subform use the tblArtAuth table. The AuthID is hidden and linked to the main table. Using the same technique as discribed for the Organization table, use one or more combo boxes for the ArtID to link to the tblArt to display title and such. A command button opens up a form that displays more information on the article.

And so on.

But this is just my style. There are some heavy weights who have been doing this stuff professionally for years who may have a better system, and you may develop your own style.

I say again, go through a "paper" plan first. Designing a form takes time, and then realizing it wont work is a waste.

One other thing, providing the design is correct, the working with the forms should be fairly easy albeit tedious. You may want to keep an Access reference book as your friend for a few nights.

Richard

 

[muriel]

Richard - if you're out there, I have a few more followup questions. Again, thank you SO MUCH for all of your help!!
I've been working with Access for awhile, but this is the first time I've had to create a database that is asked to do so many things.

1) I'm having trouble getting the queries right - I keep getting errors like syntax error, statement not supported, etc. Am I missing something?
2) Should my author data be stored in the Authors table or the AuthorOrganization table? Same question for Organization data.
3) My final task is to create word documents/reports - I'm testing out mail merge, but wondering if you have any thoughts on this process.

Thanks again!!!

 

[lespaul]

1) What kind of problems are you having with your queries?
Do you have your relationships set up?

2) What 'author data' are you referring to? If it is a detail about the author, it should be in the author table; if it is about the author's organization's it should be in that table. Please provide a little more detail.

3) What kind of reports/documents do you need, it may be easier to do them in Access (it's possible to do in Word, but may not be needed.

leslie

(ps - Just in case you don't recognize the name, I'm the one that recommended the normalization in your first post, I gave Richard a star there too!)

 

[muriel]

Thanks for the response!

I have my relationships set up as Richard outlined above. I've determined that my relationship between Authors and Organizations is one to many (an org can have many authors, but an author only writes for one org).

I have data in the Authors table and the Organizations table, but the query doesn't return any data. It shows all the fields, but no data.

I have no choice but to work with Word - my boss wants Word.

 

[lespaul]

So, you HAVE set up the relationships in the relationship window (hate to ask, but you never know!)

Post the query and I'll take a look at it (FYI - to see the SQL generated change the query view to SQL).

I know how that boss thing goes! Here's some info on the mail merge:

FAQ705-3237
Thread705-626487

If you need more info than these provide, I searched the Access VBA forum for 'Mail Merge' and there were around 60 - 65 matching threads.

Leslie

 

[willir]

muriel

You are in good hands with Leslie. And you have done the correct thing to enter some data.

Have you used the query design to create the query, or did you write it your self? Albeit it may look strange with all the brackets in SQL view, Microsoft Access does a pretty good job of creating the query when using the graphical interface.

(Forgive me if this is over doing it -- this type of stuff is much easier to do over a cup of coffee or at the person's desk)

Create your query by going to the Design tab, and create a new query using design view, no the wizard.
- Add tables for Author and Organization.
- IF the relationship has been properly setup, you will see a join line between the two tables using OrgID. IF not, then use the Relationship tool to create the relationship. (You have to be in the Database view for this option to be available.)
- In the tblOrg in the graphical view (top), double click on the "*" - this will depict a an astricts in the lower text part of the design view.
- Repeat for tblAuth
- Left click on title bar of the window and switch to dataview. And switch to Datasheet view. You should see your information.
- IF not, then look at the foreign key for OrgID in tblAuth and make sure there is a corresponding match in tblOrg.

Example

tblOrg.OrgID tblAuth.OrgID tblAuth.AuthID tblAuth.Name
1 1 1 James
1 1 2 Dean
2 2 3 Tom
2 2 4 Betty

For your 1:M, OrgID in the Author table (many side) is used to link to the Organization.

Richard

 

[lespaul]

Thanks for the compliment Richard! Between the two of us, we'll get Muriel all set up as a Database programmer!

Leslie

 

[muriel]

Richard and Leslie - thanks for all of your continuing help!! I've been out for a few days, and now returning to work on the database again. I will try your suggestions and post any more questions.

Thanks again!!

 

[muriel]

Ok, I'm back into this. Thanks again for your help. I'm still having a problem getting data from my query. I have the tables and relationships set up as outlined above. Authors and Orgs is a many to many relationship, as is Authors and Source Titles, and Authors and Keywords.

Here's the query I'm using. It returns the fields, but no data. The Author and Org tables have data in them. I'm obviously doing something wrong, but I don't know what specifically.

Query: (for Authors and all Orgs associated with them)
SELECT Author_tbl.*, AuthorOrganization_tbl.*
FROM Author_tbl INNER JOIN AuthorOrganization_tbl ON Author_tbl.AuthorID = AuthorOrganization_tbl.AuthorID;

 

[willir]

Okay, we have carried this "conversation" over to the Access queries and Jet SQL forum...

Good move Muriel.

 

[danvlas]

1. AuthorOrganization_tbl.AuthorID is by any chance null?
The referential integrity in Access allows you to have orphan records IF the foreign key is null.


2. AuthorOrganization_tbl.AuthorID has by any other values than Author_tbl.AuthorID?
You haven't enforced Referential Integrity, so you can enter any data in the tables.

Open the AuthorOrganization_tbl table and enter an AuthorID that you find in Author_tbl table.

Then run your query. It should return that record.

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html