|
|
Posted by Gleep on 05/12/07 06:35
On Fri, 11 May 2007 23:28:42 -0500, "Shark" <Shark@ocean.sea> wrote:
>I have a database of 1500+ records of contacts in a poorly formed database.
>I am looking for a PHP/MYSQL contact management system that I can use to
>rebuild my records and go forward. Does anyone have any suggestions?
>
>If not, perhaps I can get some advice on how to write code to handle the
>table structures described below:
>
>here is the original table structure:
>TABLE NAME: ENTRY
>(entry_id (PK), name, category_id, street, city, zip........)
>This meant that one entry could only be linked to a one category and have
>one address both of which are not true. As a result I have several
>duplicate
>entries becuase people enter the same entry twice so that they can list
>additional addresses and be seen in different categories.
>
>so now I have created 3 tables to handle this:
>TABLE NAME: ENTRY
>(entry_id (PK), name..........)
>
>TABLE NAME: ADDRESS
>(address_id (PK), entry_id (FK), street, city, zip......)
>
>TABLE NAME: CATEGORY
>(catindex(PK), entry_id (FK), category_id(FK) )
>
>I am having trouble writing code to migrate the the data from the old table
>structure to the new one.
>
what i would do in a situation like this, is to export all the data into an excel spreadsheet.
make sure to use an oddball delimiter like the ~ key instead of ,
if you have things set up on your some server, will make things much easier. Another cool tool is
Navicat or MySQLfront some kind of database building tool or there is good old phpMyAdmin.
set up the structure with the new data base with 3 new tables. Make sure it's a Normalized DB If
you don't know what that is there are tons of tutoirals online that explain what 'normalized' is
once you have your tables set, split out your data in excel into the 3 tables then import into mysql
navicat is really good at this, cause you get an interface to match the excel columns with table
fields. Without that tool you can do it manually but it's tedious fussy work.
then if you manage to import all the new data into the db, dump the entire thing to an sql file.
once you have you dump your current table and import with the new sql table and create a new db.
oh yeah one more thing.... you'll have to re-write your queries with proper join statements now
that you ported your data out with different fields and tables.
good luck, sounds like a nightmare project from hell.
Navigation:
[Reply to this message]
|