MySQL: Create a table that maps data from one table to another
$35-75 USD
Paiement à la livraison
General Project Description
SEE THE ATTACHED PDF FOR A MORE READABLE VERSION OF THIS DESCRIPTION.
This project involves the creation of a data mapping table that is used to link information across two other tables.
There are three tables involed: a master table, a mapping table and a "perfect" table. The mapping table (this project) is used to "link" the inconsistent data in the master table to very stable data in the "perfect" table.
Conceptual Example of Mapping Table
abc news -> ABC News
Abc News -> ABC News
ABC news Local -> ABC News
ABC NEWS TONIGHT -> ABC News
Master Table
The Master Table contains the names of the web sites and their URLs. The website names are often inconsistent or wrong. The same URL may have many similar names. This is the problem that we will correct with a mapping table.
Column: Inconsistent Name
Column: URL (host name only)
Mapping Table
The Mapping Table translates the Inconsistent Name from the Master Table to the Perfect Name.
Column: Inconsistent Name
Column: Perfect Name
Perfect Table
The Perfect Table has additional information about the web site. It can only be found if the Mapping Table has a Perfect Name that points to a record in this table.
Column: Perfect Name
Column: URL (host name only)
Column(s): Type of Web Site.. and other information
General goals of this project
#1: The ultimate goal of this project is to create the mapping table so that a search to the Master Table can find the information in the Perfect Table. The mapping table should have two columns:
name varchar(40)
name_preferred varchar(40)
#2: Mark the lookup field in the Perfect Table when that record is a unique, valid lookup.
Special Note
This document uses the term "URL" to describe the field used for matching. Specifically, this is actually the host name of the URL, not the entire URL. Example: It is "[url removed, login to view]" not ". In the database tables, this is referred to as the FQDN.
There is no Perfect Table entry for many of the Master Table records. A mapping is expected only for those records where there is a Perfect Table entry. The remaining Master Table records will not have a Mapping Table entry.
Work Requirements
This project will require exchanging mySQL dumps for proofing work.
This project will require a back-and-forth collaboration for data debugging.
The work for this project must be scripted as much as possible. This is so you will not have to do work by hand every time I ask for a correction. If handmade changes are required, they should be done in a temporary table of your own invention, applied electronically by script and then removed when this project is finalized.
Special Considerations
The URLs in the Perfect Table are the important key to creating the Mapping Table. Unfortunately, some of the URLs refer to multiple organizations. This is an important problem. URLs that do not have only one Perfect Name cannot be used for mapping and should be excluded. (Please put the words "I read this" in your bid so I know you have truly read this project description. Good luck!)
For example: The FQDN field "[url removed, login to view]" has four unique names: Abingdon Argus, Avon Sentinel, Macomb Eagle and Roseville Independent. There is no way to determine a good Perfect Name because the FQDN is not specific.
Suggested Starting Point
The simplest approach may be to look at the Perfect Table (media_freelancer) to see how those records can be matched up with the records in the Master Table (master_freelancer).
Nº du projet : #7731765
À propos du projet
Décerné à:
Hi this is bhargava expert in mysql . I had completed couple of projects like this . I am ready to provide efficient solution. I had a java programs to do this task . i can modify couple of lines of code to so that Plus
10 freelances font une offre moyenne de 55 $ pour ce travail
"I read this". I would like to work on this project! I find interesting the concepts of the Master, Perfect, and Mapping table. PM so we can discuss further details. Thanks in advance
I have done this approximate spelling data transformation many times at work. Most recently I did a table like this to standardize abbreviations in vendor names (inc, INC, Inc. to Inc). I plan to turn in the 2 table Plus
Have already done the basic queries. Will need some clarifications as to some column names w.r.t mapping table and media table. And I have read "I read this" in Special considerations.
