List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:January 30 2010 12:24am
Subject:Re: 50 things to know before migrating from Oracle to MySQL
View as plain text  
Rudy Lippan wrote:
> On 01/29/2010 02:57 PM, Chris W wrote:
> 
>> Hardcore stupid if you ask me.  I suppose it is "possible" to have a
>> valid reason (can't imagine what it might be) for using more than 61
> 
> How about complex data requirements?  Depending on the resolution of
> your data set, I could see a "simple" person-type object that contained
> name, address, SSN, mother, and birth_info starting to approach the limit.
> 

You described one table with 5 columns.

> Cities change, address changes, names change, and even mothers can
> change. 

All of these would be tracked in different rows, not different tables.


> The simple-looking street part of an address can have (at least)
> number, direction, name, suffix, any of which can change.
> 

That's one more table for addresses. So far you are up to two whole tables.

In a simplified Object-to-Database map, most object types (classes) 
equate to a single table. Each table will contain several columns. Each 
column will represent one particular property of the object. For objects 
that contains lists of sub-values or sub-objects, you use another table 
(usually called a child) related to the first (often called a parent).

>> joins.  But I would be willing to bet that 99.99% of the time if you get
>> even close to that many joins you have a very poorly designed database. 
>> I would also bet that 80% of the people who are actually writing queries
>> with that many joins don't have a solid grasp of the fundamental
>> principles of relational database design.
> 
> I suspect otherwise. In my experience, most of the time when someone
> does not understand relational databases, there is a tendency towards
> fewer tables; and, in the few cases where I have seen too many tables,
> the joins were more likely to be done in the application code than in
> the database... Fun Times there....
> 
> The real art is trying to balance the need of simplicity and ease of
> understanding with the need for flexibility, and that has nothing to do
> with relational theory. Complex datasets are, by their nature, complex,
> and can only be simplified so much. You try to hide the complexity, you
> shift it, you move-it, you send it to its room, you pretend it is not
> there. And yet it still pops up at the most inopportune times and has to
> be dealt with.
> 

OK, after this last statement I will cut you some serious slack. 
However, and I hope you agree, unless someone is using some rather 
obscene normalization, most queries should not require joins of more 
than 10 or 12 tables to resolve.

My personal thumbrule is that if I have more than about 7-9 tables in a 
single query, I should probably attack the problem in stages. I do this 
because the physical act of logically (internally) representing all of 
those columns across all of those row permutations in memory can become 
a burden to process.

-- 
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN


Thread
50 things to know before migrating from Oracle to MySQLchanguno 28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLJohan De Meersman28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLShawn Green28 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLpaul rivers28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLJohn Meyer28 Jan
    • RE: 50 things to know before migrating from Oracle to MySQLDaevid Vincent28 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLCarl28 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLJohnny Withers29 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLfsb29 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLChris W29 Jan
      • Re: 50 things to know before migrating from Oracle to MySQLmos29 Jan
        • Re: 50 things to know before migrating from Oracle to MySQLTom Worster1 Feb
          • Re: 50 things to know before migrating from Oracle to MySQLCarl1 Feb
            • Re: 50 things to know before migrating from Oracle to MySQLMichael Dykman1 Feb
          • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies1 Feb
      • Re: 50 things to know before migrating from Oracle to MySQLRudy Lippan30 Jan
        • Re: 50 things to know before migrating from Oracle to MySQLShawn Green30 Jan
          • Re: 50 things to know before migrating from Oracle to MySQLRudy Lippan31 Jan
        • Re: 50 things to know before migrating from Oracle to MySQLJigal van Hemert30 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies29 Jan
      • Re: 50 things to know before migrating from Oracle to MySQLJørn Dahl-Stamnes29 Jan
      • Re: 50 things to know before migrating from Oracle to MySQLJigal van Hemert30 Jan
    • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies1 Feb
      • Re: 50 things to know before migrating from Oracle to MySQLJigal van Hemert1 Feb
    • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies1 Feb
  • Re: 50 things to know before migrating from Oracle to MySQLJohn G. Heim29 Jan
  • Re: 50 things to know before migrating from Oracle to MySQLMartijn Tonies29 Jan