List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 10 2005 6:51pm
Subject:Re: databse design and table join problems
View as plain text  
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
Thread
databse design and table join problemsinferno9 Oct
  • Re: databse design and table join problemsPeter Brawley10 Oct
    • Re: databse design and table join problemsinferno10 Oct
      • Re: databse design and table join problemsPeter Brawley10 Oct
        • Re: databse design and table join problemsinferno10 Oct
Re: databse design and table join problemsinferno11 Oct
  • Re: databse design and table join problemsPeter Brawley11 Oct