List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 11 2005 3:09pm
Subject:Re: databse design and table join problems
View as plain text  
Cristi,

 >   Ok, if you do not recommend a big ( about 20 columns ) table, how
 >can I do the join then and how do I add to each customer with the same
 >street name, street number, block of flats number and flat number, an id
 >that will help me join them ?

Oh, you mean get all the _customers_ into one big table. Didn't we start 
with that question? Once you have imported all the tables to MySQL:: 
  (1) add int 'old_id' columns to the cp2, cp3 and cp4 tables,
      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,
       add the new cp2 cp3 & cp4 old_ids, and
        add an auto_increment int primary key,
  (3) import into customer from cp2, cp3, cp4,
       excluding dupes based on name address or whatever you want
  (4) make a customertype table whose columns include
        all columns in cp2 cp3 & cp4 which are not in customers
        an 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
Now you have the generic customer info in customer, and the 
type-specific info in customertypes.

PB

-----

inferno wrote:

> Hi,
>
>    Ok, if you do not recommend a big ( about 20 columns ) table, how 
> can I do the join then and how do I add to each customer with the same 
> street name, street number, block of flats number and flat number, an 
> id that will help me join them ?
>    I need to be able to see a customer even if it only has a packet in 
> cp2/cp3/cp4 table, not only if he has a packet in every table.
>
> Best regards,
> Cristi Stoica
>
> Peter Brawley wrote:
>
>> >    I have the data from MS Access to MySQL, half imported by now,
>> > that is not the problem, the same with importing from excel files.
>> >    The problem is how can I get all the data in one big table ?
>>
>> With the data you originally described, one big table looks like a 
>> bad idea.
>>
>> PB
>>
>>
>> inferno wrote:
>>
>>> Hi,
>>>
>>>     I have the data from MS Access to MySQL, half imported by now, 
>>> that is not the problem, the same with importing from excel files.
>>>     The problem is how can I get all the data in one big table ?
>>>
>>> Best regards,
>>> Cristian Stoica
>>>
>>>
>>> Peter Brawley wrote:
>>>
>>>> Cristi,
>>>>
>>>> >    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.
>>>>
>>>> 1. First thing may be to turn the spreadsheets into tables. It's 
>>>> trivial to turn a spreadsheet into an Access table (eg 
>>>> http://databases.about.com/aa123100a.htm).
>>>>
>>>> 2. Once you have all the representations in Access, you have a 
>>>> choice to make: standardise the data representation in Access, or 
>>>> export all the data in its various models to MySQL and standardise 
>>>> the mess there. Presumably you know Access much better than MySQL, 
>>>> so you may want to do this standardisation in Access before 
>>>> exporting to MySQL. In that case, you'll want to implement logic of 
>>>> the sort I described in Access, before exporting the data to MySQL.
>>>>
>>>> 3. You have choices for how to export tables from Access to MySQL. 
>>>> Simplest may be to export from Access to text files, then use the 
>>>> MySQL utility mysqlimport to import the data into a mysql table. 
>>>> Or, you can also use an Access-to-mysql converter script (Google 
>>>> for them), or a GUI tool like DbTools, which is freeware. Or 
>>>> (perhaps the most complex method), you can create the target MySQL 
>>>> table, use ODBCAdmin to create a DSN for it, and export to that.
>>>>
>>>> Is that the info you are looking for?
>>>>
>>>> PB
>>>>
>>>> -----
>>>>
>>>> inferno wrote:
>>>>
>>>>> 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
>>>>>>
>>>>>>
>>>>>>  
>>>>>>
>>>>>>
> ------------------------------------------------------------------------ 
>>>>>>
>>>>>>
>>>>>>
>>>>>>  
>>>>>>
>>>>>
>>>>>
> ------------------------------------------------------------------------ 
>>>>>
>>>>>
>>>>> No virus found in this incoming message.
>>>>> Checked by AVG Anti-Virus.
>>>>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 
>>>>> 10/10/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
>>>>
>>>>
>>>>  
>>>>
>>>> ------------------------------------------------------------------------
> 
>>>>
>>>>
>>>>
>>>>  
>>>>
>>>
>>> ------------------------------------------------------------------------ 
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG Anti-Virus.
>>> Version: 7.0.344 / Virus Database: 267.11.14/127 - Release Date: 
>>> 10/10/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
>>
>>  
>>
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.14/128 - Release Date: 10/10/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/128 - 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