Help: Database Design for Discourse Analysis

[McOx]

Here's my task:

I need to design a database that will allow me to describe a long text that I'm analyzing for my dissertation. I'll be analyzing the text from several angles and inputing all of this data into the database. Eventually, I will need to filter this data and print reports.

Some ground rules:

• The text itself will never be changed. (obviously)
• The reference numbers of the text (i.e. sentence #s) are already set and cannot be altered, though I need to add to them.
  
  
  
  • So, currently, the text is set with chapters and verses. I'll need to add another label, clause #. Each "verse" will have at least one clause and usually more. Currently I'm working with a calculated field of Ch.V.Cl#. These are all unique.
  
  
• I will analyze several characteristics of each clause such as verb form, introducers, conjunctions, clause type, perspective of writer, topic, etc. Each characteristic will only describe each clause one time.
• At this point I'm only building my database, so I'm not sure what kinds of queries or reports I'll be doing in the future.

To complicate matters, I'm a noob to Access .

What I'm thinking so far:
Here's what I'm envisioning. Basically, my database will have two types of tables:

• TYPE 1: A single "CLAUSES" table with columns:
  
  • ClauseID (PrimKey that I'll leave hidden),
  • each clause in a separate field,
  • Ch#,
  • V#,
  • and Clause#,
  • then calculated column: Chapter + Verse + clause#.
  • I'll use this table as a home base, from which I'll input all of the other data. Then other columns will be imported from:
  
  
• TYPE 2: Several tables of "CHARACTERISTICS."
  
  • Each will will also have auto-number ID (as their primary key).
  • "VERB TYPES"
  • "INTRODUCERS"
  • etc.

DESIGN: Each of the CHARACTERISTICS will have a "one to many" relationship with the CLAUSES table. (I.e. CHARACTERISTICS show up in the CLAUSES table many times. CLAUSES will never be reproduced twice.)

• This means my CLAUSES table will have lots of columns with lots of data. From what I've learned, this seems like poor database design: I have too much information stored in the CLAUSES table. However, I wonder if that matters because of the relative simplicity of my database. (i.e. because I only have lots of "one to many" relationships.)
• I have considered creating a separate table wherein I label each clause with only one characteristic––thus dividing my data. But I can't seem to import the basics into a new table: i.e. every clause ("long text") and my calculated column of Chapter+ Verse+Clause#. I need this info in each table because I have to look at it in order to analyze it.

• Ultimately, the reason I landed on this design is because I can't figure out how to get all of my clauses (or even the reference of Ch.V.Cl#) to show up in each CHARACTERSTICS table. But I can get the CHARACTERISTICS tables to show up in my CLAUSES table (mostly I'm using drop-downs).

Here are my questions from my noob perspective:

• Is my proposed design workable for a database of several thousand clauses and all of their characteristics? Or, Is my proposed database "bad database design"?
• If I should go another direction, how can I get my clauses––or at least my calculated column into each CHARACTERISTICS table?
• At this point I'm up for a completely different design structure. As I said, I'm having trouble envisioning how this will work out. So, does anyone have an idea for me?

[Dal Jeanis]

Look at it this way -

Each Clause is a thing. So clauses get a table of their own. (tblClause).

Each Clause will have ONE record. Each record will have ONE clause.

Each Clause will be assigned a unique autonumberkey (ClauseID).

Any other way will make your analysis a nightmare. Just don't do it.

For now, you could put the chapter and verse and clause order info for the clause in this record. It may be moved out later, but it will work here for now... ack, no, make it a separate table. The reason is complicated, but just take my word. You really want that data in a different table to simplify your analysis. But make it one record per clause. I don't think you'll have any verse records, but you might consider making a chapter table as well, to hold higher-level metadata about the work.

You will have a SEPERATE Table that lists all the possible characteristics that any clause might have. Just generic grammatical types, as detailed as you want. Each characteristic will have a unique autonumber key. They might also have other items associated with them, depending on your needs.

Then, you will have a junction table that connects one record in the clause table to one characteristic in the characteristic table that the clause has.

Clause 1 has characteristic 23
Clause 1 has characteristic 15
Clause 1 has characteristic 12
Clause 2 has characteristic 23
Clause 2 has characteristic 19

Is this starting to make sense?

You can bring up a clause in a form, and at the bottom of the form, a subform will show you what characteristics that clause has already been assigned. You can assign it another characteristic, and each assignment will create a record in the junction table, which will then show in the subform. You'll probably also want to make the subform able to delete a charactersistic if you change your mind.

[McOx]

Thanks! Yes, it makes sense.

I've spent the day (and now night) working through your response. (Yep, I'm a noob!)

I have a couple of questions:
1.) My characteristics are categorized. So I'd like to keep them separate from one another; i.e. "VerbForm", "Introducer", "independent", "dependent", and many others. Can I continue to add more tables to my junction table just like I have for my "verb form" table? 
2.) I've worked out the top of my form: displaying the clause. And I can access/change the clause from a combo box. But what I can't figure out is how to have the subform and form communicate to one another so that they populate the junction table. This seems basic, but I can't figure it out.

Here's my database if that's helpful.

https://www.dropbox.com/s/90qh4x20e4...atabase6.accdb

[Dal Jeanis]

You will save yourself LOADS of trouble if you DO NOT create a new table for each category of characteristics.

Just create a new "category" field on the characteristic table, and put a link in that field to the key of the characteristic's category.

tblClauses
   ClauseID       Autokey

tblCharCategories
   CharCatID      autokey
   CharCatName  

tblCharacteristics
   CharID
   CharName
   CharCatID      (FK to tblCharCategories)

tblClauseChars
   ClauseID
   CharID

You will be able to easily get all the reports and analysis you need MUCH easier if they are in one table, using simple inner joins in a few tables.

Otherwise, as a beginner in SQL, you'll run into major headaches when you start trying to see what types of verbs are related to what types of adverbs - you'll end up dealing with left joins and nulls. Just WAY more complicated than you need.

[McOx]

Thanks! Now that you say it, this seems overly obvious.

I've also successfully created a subform that populates my junction table. (woohoo!) Currently, all of my ClauseChars are in one drop-down combo box.

I'm somewhat unhappy with this, though, as there's no way to enter data for each specific category and increases my risk of missing one. Is there a way to create a subform that displays (and allows entry for) ClauseChars by their associated CharCategory? Perhaps a drop-down combo box for each? Or at least a data sheet that allows this distinction?

I've tried creating some queries (including the cross tab query), but I can't get the query to show columns for CharCategory with results from ClauseChars in the fields. Even if I were able to do so, I'm unsure about how to get from query to form.

Again, Dal, I'm so grateful for your time! It has been very helpful.

[McOx]

QUOTED FROM POST #4:
"You will be able to easily get all the reports and analysis you need MUCH easier if they are in one table, using simple inner joins in a few tables.

Otherwise, as a beginner in SQL, you'll run into major headaches when you start trying to see what types of verbs are related to what types of adverbs - you'll end up dealing with left joins and nulls. Just WAY more complicated than you need."

- - - -

I have one more question: Given that I want to make sure that I enter something for every CharCat for each clause, I propose this solution:

Create 4-5 more tables, each representing a "Characteristic" from tblCharacteristics. Each table would go through the same junction table.
Can I create a dummy value "X" or "Ø" as a kind of "NA" record for each? I assume that this will alleviate the nulls?
But I know nothing of "left joins."
Further, I have not ventured into retrieving data from a DB. So, I'd love to hear the holes in my plan .

[orange]

Access has some rules regarding Naming of fields. Most Access-experienced people will tell you to avoid special characters in field names.
USE ONLY ALPHABETICS AND NUMERICS AND UNDERSCORE"_". DO NOT USE characters such as "<(*&^%$#@!-... in your field and object names.

Access also has a list of reserved words see
http://allenbrowne.com/AppIssueBadWord.html

Your whole dissertation will be based on this database? If so,do some tutorials on DESIGN, SQL, Queries and get a BACKUP regime in place.

[Dal Jeanis]

McOx - You don't need to get overly complicated. No other tables are needed.

Just have two dropdownboxes. The first one is loaded with the categories, as selected DISTINCT-ly or GROUP-ed BY from the first table.

Every time you choose a category from the first dropdown box, the AfterUpdate event of that box will load the second one with the particular choices for the chosen category.

Every time you choose a detail in the second one, you can add that kind of record. That easy.