List:General Discussion« Previous MessageNext Message »
From:inferno Date:October 10 2005 7:07pm
Subject:Re: databse design and table join problems
View as plain text  
Hi,

    First of all thank you for your reply.
    The table names I've wrote in the e-mail were just for info, I do 
use underscore instead of space, as you can see I do the same for the 
fields in the table.
    In theory it is simple but I need some help on how that will be 
"translated" to mysql, I have a concept in mind, but I do not know how 
to exactly apply it to the situation.
    I am in a situation where I have the data gathered from more persons 
and everybody had a personal way of designing the database or excel 
table and now I need to build a database that will include all the data 
in the tables bellow, that is way I was not able to give you a table 
description from mysql since I do not have all the tables imported into 
mysql.
    After I will import the tables into mysql I have to make somehow the 
new table and I would like it to be good, and that it can be further 
upgraded ( maybe a new service will need to be entered for a customer 
and I need to have all the data in one place ) and to be able to handle 
each entry not by using 4 fields that are varchar.

    If you / someone can help me with this task I will be very grateful, 
as I have said before I am a beginer and do not have much experience 
with MySQL.

Best regards and thank you for your advice,
Cristi Stoica

Peter Brawley wrote:

> 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
>
>
>  
>
>------------------------------------------------------------------------
>
>
>  
>


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