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


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