NYCPHP Meetup

[Kenneth Downs]

> It seems like it would be worth the extra overhead to keep
> behind-the-scenes
> digest tables of commonly joined tables, is this something that higher end
> databases (ie. oracle) do?

It's not so much the higher-end databases do it, but that they can do it,
whereas I'm not sure mySQL can.

One method is to use triggers to update the digest tables when detail
tables are updated.

Another method is to use "materialized views", where you create a view
that is kept updated by the system.  In method 1 you do the work, in
method 2 the system does the work.

barring these, you would update the digest tables yourself whenever the
detail tables are updated.

One danger of course is that you introduce contention on the writes if
everybody is trying to hit the digest tables.  If you mave many more reads
than writes this may not be an issue.

>
> -Max
>
> On 10/4/05, Russ Demarest <rsd at electronink.com> wrote:
>>
>> Another consideration is how you are using the addresses and the
>> users. For example if you are going to be joining users with other
>> tables then trying to determine which of those users also have an
>> address in NY the queries can beat on your server. How many users
>> will you have? What with the addresses be used for? Will you need to
>> search all addresses for each user related to other tables?
>>
>> From a data point of view you would normalize it all and I would
>> suggest a states and countries table to be linked to addresses as a
>> well as address type. But when you are trying to come up with all the
>> users with a address in NY who are related to other tables, i.e.
>> groups, you may wish you have it all in one table.
>>
>> If you get really "normal" you could have a "user_addresses" table
>> which would contain the address_type field so if you have 2 users at
>> the same address the address would only be in the address table once
>> but related to both users. :)
>>
>> Don't get me started if you need to track history. "Where did user X
>> live at home 2 years ago?". Then you have a start_date and end_date
>> in the user_addresses.
>>
>> I have done it both ways and there really is not a clear winner,
>> depends on the situation. I am glad I am not tracking user address
>> history any more,
>>
>> Good luck
>>
>>
>> On Oct 4, 2005, at 11:53 AM, Allen Shaw wrote:
>>
>> > Stephen Musgrave wrote:
>> >
>> >
>> >> This is an interesting topic because I'm approaching a question based
>> >> upon this principal. There is an application that I am building
>> >> where
>> >> the User record can have 5 addresses (home address, work address,
>> >> permanent address, international address, etc, etc). I'm considering
>> >> making a table called UserAddress and then linking it to address ID
>> >> fields in the User table. I'm on the fence about it because while I
>> >> don't want a monstrous User table with tons of columns, I also don't
>> >> want to over normalize.
>> >>
>> >>
>> >>
>> > Here's a great working example to explore the issue. I would let the
>> > User table contain no address info, and then let the UserAddress table
>> > contain one address per row with an extra column for UserID. This
>> > seems
>> > like a clear one-to-many relationship between a person and his/her
>> > many
>> > addresses (also assuming it could be any number between 0 and 5,
>> > right?), so adding 5 sets of columns to the User table doesn't seem
>> > right...
>> >
>> > Honestly, the only reason I'm writing here is so someone can
>> > correct me
>> > if I'm wrong (and if I'm right then this might actually helping
>> > somebody
>> > -- neat-o ...).
>> >
>> > --
>> > Allen Shaw
>> > Polymer (http://polymerdb.org)
>> >
>> > _______________________________________________
>> > New York PHP Talk Mailing List
>> > AMP Technology
>> > Supporting Apache, MySQL and PHP
>> > http://lists.nyphp.org/mailman/listinfo/talk
>> > http://www.nyphp.org
>> >
>> >
>>
>> _______________________________________________
>> New York PHP Talk Mailing List
>> AMP Technology
>> Supporting Apache, MySQL and PHP
>> http://lists.nyphp.org/mailman/listinfo/talk
>> http://www.nyphp.org
>>
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org


-- 
Kenneth Downs
Secure Data Software
631-379-0010
ken at secdat.com
PO Box 708
East Setauket, NY 11733