Table Design

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
Mark01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You're keeping track of the senders, what about recipients?
0
Mark01Author Commented:
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
Kyle AbrahamsSenior .Net DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark01Author Commented:
Thank you, Kyle.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.