Solved

Table Design

Posted on 2014-09-12
7
401 Views
Last Modified: 2014-09-14
This question involves the proposed table design of a database. It will be a single user Access database. Please comment on the table design.

An ERD and report is attached.

This question is different from my last question with regard to the proposed subjects and topics tables. Sample reports are shown below. The attached excel file contains the sample reports.

I am designing a database that will be used to track the emails that are contained in PDFs. Numerous emails are printed to a single PDF file. Each PDF contains numerous emails. The PDFs are not going to be stored in a database. Only the file path and file name will be tracked. This will be a single user database.

Sent e-mails. The following will be tracked:
1) The date and time the e-mails were sent.
2) The sender’s first and last name.
3) The text of the message body will be copied into a field.

E-mail Images. The following will be tracked:
1) An image of every e-mail. A multipage PDF will contain an image of every e-mail.
2) The e-mail image file path.

Subjects and topics can be associated with one or more emails. The sample reports shown below gives examples of subjects and topics.

ENTITIES

Entity: Child Subjects
Description: The lower level subject of a top level subject.
Rules:
A given email may have two or more lower level subjects.
An email cannot have only one child subject.
A given email may have zero, one or more child subjects.
Attributes:
     Child Subject ID --An arbitrary unique sequential number assigned to every child subject.
     Child Subject--The lower level subject of the top level subject of a particular email.
    Parent Subject ID --Identifier that specifies the parent subject.

Entity: Child Topics
Description: The lower level topic of a top level topic A child topic provides more detail about a top level topic.
Attributes:
     Child Topic ID --An arbitrary unique sequential number assigned to every child topic.
     Child Topic--The lower level topic of the top level topic of a particular email.
    Parent Topic ID --Identifier that specifies the parent topic.

Entity: Public Comments
Description: A public comment can be associated with one or more emails.
Attributes:
     Public Comment ID --An arbitrary unique sequential number assigned to every public comment..
     Child Topic--The lower level topic of the top level topic of a particular email.
    Parent Topic ID --Identifier that specifies the parent topic.

Entity: Email
Description: The particular email message that is being tracked.
Rules:
The text of the message body will be copied into a field.
Attributes:
Email ID --An arbitrary unique sequential number assigned to every email. arbitrary unique sequential number assigned to every email.
Date sent --The date on which the email was sent.
Time sent --The time that the email was sent.
Body Text --The text contained in the email message.
SenderID --Identifier that specifies the individual who sent the file.
Parent Subject ID -- Identifier that specifies the parent subject. /*required*/
Parent Topic ID -- Identifier that specifies the parent topic. /*optional*/

Entity: Email Child Subjects
Description: This linking table resolves the original many-to-many relationship between the Email and Child Subjects tables..
Attributes:
Child Subject ID --/*required*/ Identifier that specifies the child subject.
Email ID -- Identifier that specifies the given email. /*required*/
Child Topic ID -- /*optional*/ Identifier that specifies the child topic.

Entity: Email Parent Subjects
Description: This linking table resolves the original many-to-many relationship between the Email and Parent Subjects tables..
Attributes:
Parent Subject ID --/*required*/ Identifier that specifies the parent subject.
Email ID -- Identifier that specifies the given email. /*required*/
Parent Topic ID --  /*optional*/ Identifier that specifies the parent topic.

Entity: Email Parent Topics
Description: This linking table resolves the original many-to-many relationship between the Email and Parent Topics tables.
Attributes:
Parent Topic ID --/*required*/ Identifier that specifies the parent topic.
Email ID -- /*required*/ Identifier that specifies the given email.

Entity: Email Child Topics
Description: This linking table resolves the original many-to-many relationship between the Email and Child Topics tables..
Attributes:
Child Topic ID -- /*required*/ Identifier that specifies the Child topic.
Email ID -- /*required*/ Identifier that specifies the given email.

Entity: Email Public Comments
Description: This linking table resolves the original many-to-many relationship between the Email and Public Comments tables.
Attributes:
Public Comment ID -- Identifier that specifies the public comment. /*required*/
Email ID -- /*required*/ Identifier that specifies the given email.

Entity: Email Senders
 Description: The individual who sent the email.
Attributes:
     Sender ID --An arbitrary unique sequential number assigned to the individual who sent the file.
     First Name --The first name of the individual who sent the file.
Last Name --The first name of the individual who sent the file.
Email Address -- The sender’s email address.

Entity: Files
Description: A standard-format PDF file used to hold emails.
Rules:
Only PDFs will be used. No Excel, Word, etc. files will be used.
Numerous emails are printed to a single PDF file.
All emails in a single PDF.
PDFs are currently not stored in a database; they are stored as standard PDF files.
Files may have zero to many associated comments.
Attributes:
File Id --arbitrary unique sequential number assigned to every file name. File names alone may not be unique, since the same file name could be stored in different paths.
File Name --The name of a standard-format PDF file used to hold emails.
Path Id --identifier that specifies the drive and path to the file.
File Type Id --Physical file type; typically this will correlate to the file extension, but that’s not actually required. For example, a ‘[.]txt’ file could actually [.]csv-format data and vice-versa.

Entity: File Types
Description: Physical file type; typically this will correlate to the file extension, but that’s not actually required. For example, a ‘[.]txt’ file could actually [.]csvformat
data and vice-versa.
Rules:
The File Type will probably be limited to PDF and .jpg.
99% of the time only PDF will be used.
There is almost zero chance that .DOC and a .DOCX will be used.
File Type ID --Arbitrary unique sequential number assigned to each unique File Type
Default Extension --default extension for this type of file: ‘PDF’ for pdf files.
File Type --Standardized description of the the file type: ‘PDF’.

Entity: Paths
Description: The path of a file. Example: c:\email\. This does NOT include the file name, which is part of the file data.
Attributes:
Path Id --arbitrary unique sequential number assigned to each unique path.
Path --the remainder of the physical path to the file
Drive --The “drive” name; may be a single letter, a share, a volume mount point, etc.


 Entity: Parent Subject
 Description: The top level subject that can pertain to more or more emails.
Rules:
An email must have a parent subject.
An email can have both a parent subject and an child subject.
A parent subject or both a parent subject and an child subject can be associated with two or more emails.
Subjects will be shared by email, future documents and future letters tables.

 Attributes:
Parent Subject ID --An arbitrary unique sequential number assigned to every parent subject.
Parent Subject --The top level subject of a particular email.

Entity: Parent Topic
 Description: The top level topic that can pertain to more or more emails.
Rules:
An email must have a parent subject.
An email may have zero or one parent topics.
The same parent subject and/or parent topic can be associated with one or more emails.
Topics will be shared by email, future documents and future letters tables.

 Attributes:
Parent Topic ID --An arbitrary unique sequential number assigned to every parent topic.
Parent Topic --The top level topic of a particular email. Provides additional detail about the top
level subject.


Here are the sample reports:

Sample Reports (Parent Subject, Child Subject, Parent Topic and Child Topic.)

Sample Reports showing the association of four emails with a parent subject, child subject, parent topic and child topic.

The Parent Subject, Child Subject, Parent Topic and Child Topic tables shown below are filled with sample data.

Parent Subject
Missing Files
Payroll
Trustee Reports


Child Subject
Errors
Quickbooks
T Rowe Price Acct # 4972


Parent Topic
Co-Trustee False Statement
Co-Trustee Tanya accuses Jesse of file theft
Mary states totals are wrong
Tanya accuses Fred of incompetence


Child Topic
Disbursement mismatch
Files from Jesse's desk are missing
Mary's frequent threats
Suspected employee paycheck overpayment



Scenario ( 1)
Subject: Payroll
Child Subject: Quickbooks

Topic: Co-trustee False Statement
Child Topic: Mary's frequent threats

Sender: Julie Jones
Date: 1-1-14
Time: 4:00 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...



Scenario (2)
Subject: Missing Files
Child Subject: T Rowe Price Acct # 4972

Topic: Co-Trustee Tanya accuses Jesse of file theft
Child Topic: Files from Jesse's desk are missing

Sender: Tanya Jones
Date: 1-3-14
Time: 4:14 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...



Scenario (3)
Subject: Payroll
Child Subject: Quickbooks

Topic: Tanya accuses Fred of incompetence
Child Topic: Suspected employee paycheck overpayment

Sender: Tanya  Jones
Date: 1-9-14
Time: 3:04 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...



Scenario (4)
Subject: Trustee Reports
Child Subject: Errors

Topic: Mary states totals are wrong
Child Topic: Disbursement mismatch


Sender: Jesse Pavin
Date: 1-10-14
Time: 1:20 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...

**********************************************************
Sample Report (Parent Subject Only)

A parent subject or both a parent subject and an child subject can be associated with two or more emails. The following sample report shows two emails associated with only the parent subject “Missing Files.”

Parent Subject: Missing Files

Sender: Jesse Pavin
Date: 1-10-14
Time: 1:20 p.m.

Sender: Tanya  Jones
Date: 1-9-14
Time: 3:04 p.m.



PROPOSED TABLES


ChildSubjects
ChildSubject
ChildSubjectID
ParentSubjectID

Child Topics
Child Topic ID
Child Topic
ParentTopic ID

Email
EmailID
DateSent
TimeSent
SenderID

(Note about above table: I received a comment stating that it is better to combine the DateSent and TimeSent fields into a single field.)



EmailChildSubjects
ChildSubjectID
EmailID
ChildTopicID

EmailParentSubjects
ParentSubjectID
EmailID
ParentTopicID

EmailParentTopics
ParentTopicID
EmailID

EmailChildTopics
ChildTopicID
EmailID

EmailSenders
SenderID
FirstName
LastName
EmailAddress

Files
FileID
File

FileTypes
FileTypeID
DefaultExt

ParentSubject
ParentSubjectID
ParentSubject

ParentTopic
ParentTopicID
ParentTopic

Paths
PathID
Path

Here is the ERD
ERDreport.pdf
SampleData.xls
0
Comment
Question by:Mark01
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
Hi Mark,

I can't quiet figure out what's your question. Please specify what you need help with in 3 to 4 sentences.

Also I notice that you are not using a naming convention in your ERD. The DB you are creating is very complex and you will eventually need to do some vb coding. Using a naming convention makes it a lot easier to navigate through code. You can find some naming convention at this link:

http://access.mvps.org/access/general/gen0012.htm
0
 

Author Comment

by:Mark01
Comment Utility
I am going to add 7 new tables to the database. Do you agree with the proposed tables? Here are the new proposed tables:

ChildSubjects
ChildSubject
ChildSubjectID
ParentSubjectID

Child Topics
Child Topic ID
Child Topic
ParentTopicID

EmailChildSubjects
ChildSubjectID
EmailID
ChildTopicID

EmailParentSubjects
ParentSubjectID
EmailID
ParentTopicID

EmailParentTopics
ParentTopicID
EmailID

EmailChildTopics
ChildTopicID
EmailID

ParentSubject
ParentSubjectID
ParentSubject

ParentTopic
ParentTopicID
ParentTopic


Here are the sample reports:

Sample Reports (Parent Subject, Child Subject, Parent Topic and Child Topic.)

Sample Reports showing the association of four emails with a parent subject, child subject, parent topic and child topic.

The Parent Subject, Child Subject, Parent Topic and Child Topic tables shown below are filled with sample data.

Parent Subject
Missing Files
Payroll
Trustee Reports


Child Subject
Errors
Quickbooks
T Rowe Price Acct # 4972


Parent Topic
Co-Trustee False Statement
Co-Trustee Tanya accuses Jesse of file theft
Mary states totals are wrong
Tanya accuses Fred of incompetence


Child Topic
Disbursement mismatch
Files from Jesse's desk are missing
Mary's frequent threats
Suspected employee paycheck overpayment

Scenario ( 1)
Subject: Payroll
Child Subject: Quickbooks

Topic: Co-trustee False Statement
Child Topic: Mary's frequent threats

Sender: Julie Jones
Date: 1-1-14
Time: 4:00 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...



Scenario (2)
Subject: Missing Files
Child Subject: T Rowe Price Acct # 4972

Topic: Co-Trustee Tanya accuses Jesse of file theft
Child Topic: Files from Jesse's desk are missing

Sender: Tanya Jones
Date: 1-3-14
Time: 4:14 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...

Scenario (3)
Subject: Payroll
Child Subject: Quickbooks

Topic: Tanya accuses Fred of incompetence
Child Topic: Suspected employee paycheck overpayment

Sender: Tanya  Jones
Date: 1-9-14
Time: 3:04 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...



Scenario (4)
Subject: Trustee Reports
Child Subject: Errors

Topic: Mary states totals are wrong
Child Topic: Disbursement mismatch


Sender: Jesse Pavin
Date: 1-10-14
Time: 1:20 p.m.
File: c:\email\file1.pdf
Email Contents: .. text from e-mail...

**********************************************************
Sample Report (Parent Subject Only)

A parent subject or both a parent subject and an child subject can be associated with two or more emails. The following sample report shows two emails associated with only the parent subject “Missing Files.”

Parent Subject: Missing Files

Sender: Jesse Pavin
Date: 1-10-14
Time: 1:20 p.m.

Sender: Tanya  Jones
Date: 1-9-14
Time: 3:04 p.m.
0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
Comment Utility
Before giving a definite answer I need to know the answer to the following questions:

Can two topic have the same subtopic
Can two subjects have the same subsubject
Can two topics have the same subjects

Assuming that your answer is yes for all then your tables should be:-

tblChildSubjects
fldChildSubjectID
fldChildSubject
fldParentSubjectID

tblChildTopics
fldChildTopicID
fldChildTopic
fldParentTopicID

fldEmailChildSubjects
fldChildSubjectID
fldEmailID

tblEmailParentSubjects
fldParentSubjectID
fldEmailID

tblEmailParentTopics
fldParentTopicID
fldEmailID

tblEmailChildTopics
fldChildTopicID
fldEmailID

tblParentSubject
fldParentSubjectID
fldParentSubject

tblParentTopic
fldParentTopicID
fldParentTopic

You will notice that I have deleted some fields like fldChildTopicID in tblEmailChildSubjects

They are not needed since that you are already recording fldChildTopicID  in tblEmailChildTopics.

As a general statement, I think that the structure is overly complex. Why do you need topic and subtopic. What is the difference between topic and subject. How are you going to ensure that users adhere to such complicated business logic?

I would personally keep it to a single topic level and then layer the subjects eg:

Topic: Farming
Subject:- Banana
Subsubjects: Fertilizing, pest control, markets, ect...

Subject: Machinery
Subsujects:- backhoe, frontend loader, tractor, ect...
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Mark01
Comment Utility
The answer to the following questions is yes.

Can two topic have the same subtopic?
Can two subjects have the same subsubject?
Can two topics have the same subjects?


Your Other Questions

>>Why do you need topic and subtopic?<<

They are needed because the contents of many emails will be thoroughly analyzed. My next EE question will involve quotations from emails. Quotations will also need to be associated with topics and subtopics.

To give you a little more insight about the database, the sample reports indicate individual's behavior. Emails tell a story about how individuals have behaved over a period of time.

Very detailed reports will be prepared that allow the user to quickly understand individuals' behavior over a period of time.

>>What is the difference between topic and subject?<<

My best answer at this time is to look at the sample reports. I have never worked with a database like this.

>>How are you going to ensure that users adhere to such complicated business logic?<<

Very few people will be using the database. Their work will be closely watched.
0
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
In that case the table structure in my previous response would do the job.

However, if I was designing this I would simple store the subject and body text in a table.

tblEmail
fldEmailID
fldDate
fldSubject
fldBodyText
fldSender

Then use queries to search for keywords in the subjects and body text of the email. Then if you want to find emails with the topic "farming" you run a query

Select * from tblEmail Where fldSubject Like "*" & "farming" & "*" OR  fldBodyText Like "*" & "farming" & "*"

Much less stress on your database user because they done have to change anything emails subject and content are stored as is and you let the query worry about the topics, subtopics, subsubtopics and so on. In fact if the emails are coming through microsoft outlook you can create a vba code that will transfer data about each incoming email to the database automatically, no need for user to do data entry.
0
 
LVL 16

Assisted Solution

by:Sheils
Sheils earned 500 total points
Comment Utility
I have look at the report for an understanding of topic and subject. You wrote:-

Subject: Missing Files
Child Subject: T Rowe Price Acct # 4972

Topic: Co-Trustee Tanya accuses Jesse of file theft
Child Topic: Files from Jesse's desk are missing



So in your proposed structure you'd have a record in tblTopic fldTopic =Co-Trustee Tanya accuses Jesse of file theft. That will have an ID and then you enter this id in tblEmailTopics.

I will be surprise if Tanya accusses Jesse of theft so frequently that it justify a lookup table.

I think that you will end up with a tblTopics having about the same number of records as tblEmailTopics.

In a nutshell I think that you are creating too much work for yourself and a nightmare for the  user. The fact that you can't give a simple straight forward definition for topic, subtopic,subject,subject should by itself be a red flag.
0
 

Author Closing Comment

by:Mark01
Comment Utility
Thank you, Sheils.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now