Creating newspaper index

[Hist]

I am helping a voluntary project of creating a newspaper index. My job is to create and structure database. I have created 8 fields Form for inputting data, like: Sections, Subjects, First Name, Surname, (article) Description etc.

I am stack with the last one - Cross Reference. I need to find a way to put data about a person or subject in two or more Sections, i.e. "John Smith, see also Foreign Policy" (Section) while data is completed in "Culture" Section.

Can you help me with this or give any advice for the project?

 

[Pat Hartman]

The Cross Reference is a many-to-many relationship and it will be held in a separate table. I can't figure out from your description what is being cross referenced.

Assuming this is a list of articles and each article has a single title. The article could have multiple authors and authors could write multiple articles so that leaves us with
tblArticle
ArticleID (autonumber pk)
Title
Body
PublishedDT
tblAuthor
AuthorID (autonumber PK)
FirstName
LastName
tblArticleAuthor - this allows multiple authors to be related to the same article. It also allows multiple articles to be associated with a single author. That makes this one type of cross reference.
ArticleID (PK field1)
AuthorID (PK field2)
tblSections
SectionID (autonumber PK)
SectionName
tblArticleSections This allows one article to be related to multiple sections and one section to be related to multiple articles
ArticleID (PK field1)
SectionID (Pk field2)

 

[Mark_]

I would do this as a query at run time.

Query would be

Select * from Table where [Field] = <Criteria>; order by [What you want]

Don't try to save this as you can calculate it at run time. More important, as your table grows you'll run into progressively more and more links to the point where your saving far more of those than actual data.

Think it through; 100 authors, each write 100 papers. Some work together. You could easily have thousands of links there without going too far in.

For what sections they may also be in? Query for that person and get a return of the distinct sections they have results in. For papers they work on with another? Use the paper name to select all matching authors.

I just hope they don't want this to be printed in all possible combinations. THAT would be a pain...

 

[The_Doc_Man]

The problem with "cross-reference" isn't storing the data. That will just be a junction table of some kind or another. The problem will be populating the table by identifying the cross references. It gets more complex since you appear to have more than one type of cross reference to list.

Therefore, to my admittedly feeble mind, the first step here is to enumerate the TYPES of cross-reference you will need.

Since you have First Name and Surname as separate fields and can have a name reference, you are already looking at multiple-field cross-overs anyway. But this might actually be counter-productive since your other crosses might be less complex.

You can have a record for John, Smith - but would you really index on first names WITHOUT indexing on last names? If you have John Smith in two articles, would you have FOUR references (2 articles each for John, 2 each for Smith)? In essence, you have a "multi-field" cross-reference.

The multi-topic part also bothers me a little. I can see you would have references for, oh, let's pick something arbitrary: articles with titles including "Drug Traffic" - which could appear in sections on "crime" as well as on "editorial" and "international" sections. Which means you now have person-field cross-references and title-field cross-references, each potentially with other fields.

With a non-normalized index, you run the risk of having a table-size that is factorial (by reference types) in its growth pattern.

My thought is that you might need to spend some up-front skull sweat on the subject of table structure for the cross-reference. Since I am not sure of what you intend to do with it, I will only add one more reminder:

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first, or at least tell it HOW to find what you want it to tell you.

So that means that when you structure your index, you are going to be dealing with storing the answers to questions you don't immediately know how to ask - because until you build the structure up a little and make some design decisions, you won't even know what questions you CAN ask.

Please don't take this as me being a downer on your project. Rather, I want you to know where you need to spend some design time so that your project CAN be what you want it to be. And also remember that any project of significant magnitude WILL have some rough spots. It is inevitable.