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
>
>
Attachment: [text/html]
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
