[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

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
0
Mark01
Asked:
Mark01
  • 7
  • 6
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
Well, I would never put PDFs in a database.  I would (and do) just write the fields to the columns in the table so I can easily search them with a program or PHP page.
This is my CREATE TABLE for one that I do (though it is for MySQL and not MSSQL).  It's easy to search and doesn't spend any extra time to make PDFs and organize them.
CREATE TABLE IF NOT EXISTS `formtrack` (
  `formID` int(11) NOT NULL auto_increment,
  `formDT` datetime NOT NULL,
  `formINF` varchar(4096) NOT NULL,
  `lname` varchar(32) NOT NULL,
  `useremail` varchar(64) NOT NULL
)

Open in new window

0
 
Mark01Author Commented:
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.
0
 
hnasrCommented:
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.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Mark01Author Commented:
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: ?
0
 
hnasrCommented:
@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.
0
 
Mark01Author Commented:
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:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28503680.html#accepted-solution
0
 
hnasrCommented:
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.
0
 
Mark01Author Commented:
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: ?
0
 
Mark01Author Commented:
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.
0
 
hnasrCommented:
It is not an issue of correct or not. All depends on your requirement and how you approach it.
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...
This is a report from your question. This report does not help extract the proposed entities.

The way I approach the issue is to start with an initial report, What I expect is something like this:
id senderName dateSent timeSent pdfAttachedPath image1 image2 textFromEmail ....
Add all the info required, and from which the proposed entities will be created and link fields assigned. These entities will be the normalized ones, which can be joined to reproduce the initial report specified above.
0
 
Mark01Author Commented:
I do not understand your answer. Do you have any links to articles that explain how to create a data model?
0
 
hnasrCommented:
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.
0
 
Mark01Author Commented:
Thank you, Dave Baldwin and hnasr.
0
 
hnasrCommented:
Welcome!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now