List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 10 2005 7:39pm
Subject:Re: databse design and table join problems
View as plain text  
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
>
>  
>

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