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