Cristi,
> I have the following tables: ( some in Microsoft Access and some
> in Excel ) and I want to migrate the data into MySQL and develop
> an interface in PHP for easy administration and control...
A few points:
1. Instead of spaces, use underscores or nothing in table names
2. If Address_Code contains addresses of Sales_Representatives, it
needs an
column for Sales_Representative ids.
3. One way to merge cp2, cp3 and cp4 would be:
(1) add int 'old_id' columns to cp2, cp3 and cp4,
populate with sequential values such that
old_id values are unique across cp2, cp3 or cp4
(2) make a new customers table structure as desired, and include
the new cp2 cp3 & cp4 old_ids, and
an auto_increment int primary key,
(3) import into customer from cp2, cp3, cp4,
excluding dupes computed without reference to old_id
(4) make a customertype table whose columns include
all columns in cp2 cp3 & cp4 which are not in customers
auto_increment primary key id
customer_id
customer_type (whatever values you want corresponding to cp2
etc)
(5) populate customer_type from joins on
customer and cp2
customer and cp3
customer and cp4
using old_id
(6) drop the old_id column from customers
PB
-----
inferno wrote:
Hi,
I have the following tables: ( some in Microsoft Access and some
in Excel ) and I want to migrate the data into MySQL and develop an
interface in PHP for easy administration and control.
*1) Sales Representative Code
*sr_id INTEGER ( unique )
sr_user VARCHAR
sr_name VARCHAR
sr_email VARCHAR
/id INTEGER ( auto increment and primary key ) this is something
that I will want to add/
*2) Address Code*
address_street VARCHAR
address_street_number VARCHAR
address_street_block_of_flats_number VARCHAR
address_staircase_number VARCHAR
address_area VARCHAR
address_container INTERGER
address_code VARCHAR
/id INTEGER ( auto increment and primary key ) this is something
that I will want to add/
*3) Customer Packet 2*
cp2_contract_no INTEGER ( unique )
cp2_name VARCHAR
cp2_street VARCHAR
cp2_street_number VARCHAR
cp2_block_of_flats_number VARCHAR
cp2_staircase_number VARCHAR
cp2_apartament_number VARCHAR
cp2_packet_old VARCHAR
cp2_packet_new VARCHAR
cp2_packet2 VARCHAR
cp2_user VARCHAR
cp2_customer_phone VARCHAR
cp2_id_serie VARCHAR
cp2_id_number INTEGER
cp2_personal_code INTEGER ( it's the unique code of a person so I
cannot use it as a unique key since a client can have more
contracts and more locations )
*4) Customer Packet 3*
cp3_client_id INTEGER( it is not for the location, so if I select
all the data with this code it can show more than one entry since
one client can have more locations )
cp3_contract_number INTEGER
cp3_customer_name VARCHAR
cp3_customer_packet VARCHAR
cp3_street VARCHAR
cp3_street_number VARCHAR
cp3_block_of_flats_number VARCHAR
cp3_staircase_number VARCHAR
cp3_floor_number VARCHAR ( ground floor is considered P in the
current database and other problems )
cp3_flat_number VARCHAR
*5) Customer Packet 4*
cp4_client_id INTEGER
cp4_contract_number INTEGER
cp4_customer_name VARCHAR
cp4_customer_packet VARCHAR
cp4_street VARCHAR
cp4_street_number VARCHAR
cp4_block_of_flats_number VARCHAR
cp4_staircase_number VARCHAR
cp4_floor_number VARCHAR
cp4_flat_number VARCHAR
Now a little info on the tables:
Customer Packet 3 and 4 client_id are identical, but they do not
exist all the time.
Customer Packet 2,3,4 can be grouped based on street,
street_number, block_of_flats_number, staircase_number,
flat_number, the floor number is not in the Customer Packet 2, but
there are a few clients that are only inf CP2, CP3 or CP4 and I
need to add him also, with the data that I find in the tables,
otherwise I will lose this clients and the will not show up anymore.
>From all the tables I will have to be able to make a new table
that will have all the data, and if a customer is only in the
customer packet 2, let's say, he will have blanks for the other
fields in table CP( Customer Packet)3 and 4.
Lets say consider this example like this:
I have CP2 cable contracts, CP3 internet contracts and CP4 the VoIP
contracts and I need to get all the data in one table.
I will need to be able to have an unique ID for each entry, so that
is way I am thinking of creating a big table with all this data
since I cannot use string as a condition, I've had some problems
with imports from excel/csv into mysql and I would like to be able
to control each location.
I will need to get all this data into a new table that looks like
this:
*Custmer Data
*id unique, primary key
customer_name
customer_street
customer_street_number
customer_block_of_flats_number
customer_staircase_number
customer_flat_number
customer_cp2_packet_old ( use cp2_packet_old )
customer_cp2_packet_new ( use cp2_packet_new )
customer_client_id ( you will find it in cp3 or cp4, it should be
in both, but since user error exists, it's not in both. )
customer_cp3_packet ( use cp3_customer_packet )
customer_cp3_name ( use cp3_customer_name )
customer_cp3_contract_number ( use cp3_contract_number )
customer_cp4_packet ( use cp4_customer_packet )
customer_cp4_name ( use cp4_customer_name )
customer_cp4_contract_number ( use cp4_contract_number )
customer_area
customer_container
customer_code
( The customer_area, customer_container, customer_code are based on
street, street number, block of flats number, staircase number)
I think I will have to join the tables, but I have no experience
with joining tables.
MySQL version: 4.0.24
OS: linux
If you have any sugestions or recomandations on how the new table
should look / how to solve this problem please feel free to advise
me since I am willing to learn.
Best regards,
Cristi Stoica
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date: 10/9/2005
------------------------------------------------------------------------
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 10/10/2005
------------------------------------------------------------------------
