Link to home
Start Free TrialLog in
Avatar of Mark01
Mark01Flag for United States of America

asked on

Database Design

I am working on an e-mail tracking database that will track only a small amount of information. As time permits, I will revise the entity analysis so that more information can be tracked. Please comment on the definition and relationship data. Here is a quote from a previous EE comment: “You will have separate definition and relationship data -- this is critical!.” “... For example (D) mean that entry is a definition, otherwise assume it’s a relationship. {} indicates suggested areas for consideration, and are thus optional.”

I am currently using the following book to learn database design:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands-/dp/B00DIKUOUA/ref=sr_1_11?s=books&ie=UTF8&qid=1408736146&sr=1-11&keywords=database+design+for+mere+mortals

This will be a single user database that tracks the emails that are contained in PDFs. Numerous emails are printed to a single PDF file. Each PDF contains numerous emails. I need help with the text of the message body will be copied into a field.

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

Here are the proposed entities:
File
Image
Path
Sender

Here are the proposed attributes:
Date
Time
File name
First name
Last name
SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark01

ASKER

I am not going to store the PDFs in a database. I am just going to track the PDF's file path and file name.
One way:
Try to list all fields you feel necessary to produce your report. Add some sample data in this manual report.

Starting with this report as un normalized table, check help for "description of database normalization basics". Follow the instructions to create the required tables.
Avatar of Mark01

ASKER

Here is some more detail.

Entity: File
Description: A single PDF file. Numerous emails are printed to a single PDF file. Each PDF contains numerous emails.
Attributes: Name

Entity: Email
Description: The e-mail that was sent.
Attributes: Date, time, Message body text

Entity:  Sender
Description: The individual who sent the e-mail.
Attributes: first name, last name

Entity: Path
Description: The location of the PDF. Example: c:\email\file1.pdf
Attributes: ?
@Mark01
I suggest devoting this question to just normalize the table.

Start with a new database.
Start creating one table using all expected columns. Call it, toBeNormalized.
Add some representative data that will help in the normalization process.
Avatar of Mark01

ASKER

hnasr, I appreciate your comments but I am using Scott Pletcher's advice about the importance of the design and definitions of the data.

Here's the link:
https://www.experts-exchange.com/questions/28503680/Table-Design.html?anchorAnswerId=ccepted-solution#accepted-solution
That what I meant and it is the first step in the way.

How can you define things which are not properly identified?

The normalization identifies the required entities to use. Then you have to document the entities by describing every object.
Avatar of Mark01

ASKER

According to Scott Pletcher's comment, I am working on the logical design: i.e., "entities" and "attributes," which is the first step. Do you disagree with this first step?

Here is my first attempt (from above):

Entity: File
Description: A single PDF file. Numerous emails are printed to a single PDF file. Each PDF contains numerous emails.
Attributes: Name

Entity: Email
Description: The e-mail that was sent.
Attributes: Date, time, Message body text

Entity:  Sender
Description: The individual who sent the e-mail.
Attributes: first name, last name

Entity: Path
Description: The location of the PDF. Example: c:\email\file1.pdf
Attributes: ?
Avatar of Mark01

ASKER

To help clarify this question, I would like to know if the proposed entities and attributes are correct. If they are not correct, please suggest the correct entities and attributes.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark01

ASKER

I do not understand your answer. Do you have any links to articles that explain how to create a data model?
Try:
description of database normalization basics

The bold line in previous comment represents the heading column, which I assume are required for a report.
Then next step is to normalize this table producing required entities with respective join fields to link the tables.
Avatar of Mark01

ASKER

Thank you, Dave Baldwin and hnasr.
Welcome!