Ok, it's been a couple years since my database design class, and I'm having a hard time thinking of how to design a database I have to create.

I want to track my organization's mentions in articles across the world.
So, I have come up with this rough database design (attached), and as you can see, multiple outlets (newspapers, websites, tv stations, etc.) can provide multiple articles, and multiple articles can be seen in multiple outlets.
Now, I know I'm supposed to make another table when this happens, but I need some help from you all to figure out what to call the new table, and what fields should be apart of it.

The whole point of the database is to see where the organization is getting mentioned (geography), if the org is mentioned in a positive, neutral, or negative tone, and if the org is a significant, exclusive, or marginal part of the story (prominence).
If I can get this data, I can then create a pie chart within access (thats the dbms, I'm using). But formatting those reports/charts is a whole 'nother question

Outlets needs to be related by storyID. That should put you on the right track.

Hmm. If I do that, I still have a many to many relationship between the two tables.
Any ideas on how to get it to a 1 to many, or 1 to 1, if that's what I need?

Alrighty, one more hint:

One story has many outlets... The common factor is the story, so why is outletid in your story table?

Ok, I think I'm following. Do I just make a new table that has storyID and outletID as the two fields? Like this:

That works. This assumes you want to be able to assign the same outlet to multiple stories, that's what you want right?

That's correct. For example, the NY Times could have 10 or so stories, and also one story, (eg. "Man Walks on Mars") can be published in multiple outlets (NY Times, Chicago Tribune, LA Times, Washington Post, etc.)

I had one question though. If I wanted to find the anser to this question: "Which stories were published in outlets outside of Texas?" How could I do that with this db design?

1 Create first a table and call it tblCirculations. Under this table add fields CirculationID, StoryID, CircularName, etc..

2. Create another table called tblOutlets. Under this table add fields CirculationID, State, City, etc..

3. Create another table called tblStories. Under this table add fields StoryID, datePublished, Headline, ArticleBody, etc..

Link the two circulationID fields of the tblCirculations table and the tblOutlets

Link the two StoryID fields of the tblCirculations and the tblStories tables.

That should do what you want to accomplish..

If you don't create a separate tables for your chart indicators like geography, tone, and prominence, states just make sure you hard-code them to provide drop-down list selection during data entry because inconsistency in spelling can throw off your counts.

COOL! I will give that a shot right now! One question, can you give me an example of what a circulation, and circular name is? It's not outlets is it?
Thanks for your explanation. I appreciate the help very much.

I got that field name from your Outlets table "Circulation" or circular I thought would refer to newspapers, etc? You can call them Circulation if they are names of media or circulationID if you are using it as code to link to the circulation tables. Whatever as long as you know what it means to you and what it refers to..

Oh, I see. Basically circulation is the number of veiwers/readers that a particular outlet has. For example, the outlet named NY Times has a circulation of 1,000,000 (people).
Does that make sense?
Having said that, how could I keep with your design, and make it work since circulation isn't really that valuable of a piece of data.

Oh OK! We're not far off, just add another field in tblCirculations and call it QtyCirculated which would be a number field. So you can allow more than one circulars in any particular state. DOes that make sense? You have circular name( ex NY TIMES) under this table and QTYCirculated and have circulationID linked to your outlets circulationID which will have New York State.

Oook. I think I see what you mean. I'll work on this, and try it going. I'll be back if I have anymore questions.
Thanks for all your help Teddy and Fred! I appreciate it.

Hello. I am going to write this in a different way. I still don't quite get it.
Thanks.

Background: My org. wants to track where it gets mentioned in stories throughout the world that get published in various media outlets (newspapers, websites, magazines, journals, tv stations, etc.)

A story gets published in one or multiple outlets and different outlets can run the same story on different days. For example, Newspaper A publishes a story with the headline of "Man Walks on Mars" on Jan. 1, 2006; and Website B publishes the same story, "Man Walks on Mars", on Jan. 2, 2006.

Now, for each story that mentions my organization, I would like to track
1. if it portrayed my org. in a positive, neutral, or negative tone
2. if my org's prominence in the body of the story was exclusive, significant, or marginal.
3. to see every outlet this story was published in and the date it was published for the particular outlet (keeping in mind different outlets can run the same story on different days).
4. See the widest place, geographically, it was published in. For example, if a story ONLY ran in a regional paper, it would be considered a "regional" story. However, if it was published in the local paper, in a different state, AND in London, England, the "widest geographic coverage" in this example would be "International".
The categories of "geography" would be regional, in state-non regional, national, and international.

I think that about covers the business rules, that I can think of.
I have attached my version of a diagram, but I need help normalizing, and making sure I get all the business rules above accounted for. As you can see, I only have a Stories table, and Outlets table. I need to figure out how to put multiple dates and multiple outlets to a story. And I haven't figured out how to do that and what other table(s) are needed for this.

Any help would be greatly appreciated. Thanks.

I got you I believe..
YOu need an EventTable that will log all the events in a day.
Under this table you'll have EventID which you can have autogenerated number field.
So you need to create 3 tables ...tblEvents, tblStories, and tblOutlets

A. tblEvents
fields 1. EVentID - use this as key field with autogenerated property
2. DatePublished - set duplicates OK
3. OutletID
4. StoryID
5. HeadLine - specific to OutletName or media name Headline
6. StoryBody - Specific to OutletName or media name content
7. Tone - 3 selections dropdown
8. Prominence - 3 selections dropdown
9. Geography - 4 selections dropdown
10. QtyCirculated

B. tblStories
fields 1. StoryID
2. StoryDescription - YOur own description of story different from Headline text or StoryBody content. This will play important role in search referencing. For example you have a word here "Mars". You can query this field for text "Mars" and will link the StoryID to the tblEvents.StoryID and pulls all the records in the tblEvents with same StoryID and each record will have link from tblEvents.OutletID to tblOutlets.OutletID and pulls all the matching OutletID records.

C. tblOutlets
fields 1. OutletID
2. Name
3. Type
4. City
5. State
6 Country

Link tblEvents.outletID to tblOutlets.OutletID
Link tblEvents.StoryID to tblStories.StoryID

Make sure the drop downs selection data are hard-coded or create separate tables for consistency of spelling if you want accurate accounting.

NOTE: IF Geography refers to Outlet Location then move the field to the tblOutlets.

This should do it..