List:General Discussion« Previous MessageNext Message »
From:inferno Date:October 9 2005 4:38pm
Subject:databse design and table join problems
View as plain text  
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

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