Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Table Design

Posted on 2014-09-05
4
Medium Priority
?
579 Views
Last Modified: 2014-09-07
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.

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.

Here is a sample report:
Sender: Mary Smith
Date: 1-1-14
Time: 4:00 p.m.
File c:\email\file1.pdf
Email Contents: .. text from e-mail..

Proposed Tables

Email
EmailID
DateSent
TimeSent
SenderID

EmailSenders
SenderID
FirstName
LastName
EmailAddress

Files
FileID
File

FileTypes
FileTypeID
DefaultExt

Paths
PathID
Path


ENTITIES

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..

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.

Here is the ERD
ERDreport.pdf
0
Comment
Question by:Mark01
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40306176
You're keeping track of the senders, what about recipients?
0
 

Author Comment

by:Mark01
ID: 40306210
A recipients table will be added later. These proposed tables only cover the essential information that must be tracked. If you look at the other open question (Modeling), you'll notice that the next step is to track subjects and topics.
0
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 40306235
A couple of different things to look at then:

Email:
Date / Time send should be one column.  

If you're going to be storing the files on a NAS . . . you usually access them by a UNC . . . which would make drive letter not meaningful.

If you're storing them on computers, I would put the computer name in there as well.  (EG:  K:\some_path doesn't mean anything to me.  K:\some_path on server X means something . . . or \\x\some_path)


Other than that this looks fine.
0
 

Author Closing Comment

by:Mark01
ID: 40308931
Thank you, Kyle.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

580 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