[thelist] Tables referencing each other

Bill Moseley moseley at hank.org
Wed Mar 25 16:22:42 CDT 2009


On Wed, Mar 25, 2009 at 02:01:04PM -0700, Anthony Baratta wrote:
> Bill...
> 
> Another way is to cheat and use your profile table ID.
> 
> Select Top 1 * from Profile
> where cust = 67
> order by id DESC;
> (MS SQL)
> 
> Select * from Profile
> where cust = 67 and rownum = 1
> order by id DESC;
> (ORACLE)
> 
> Select * from Profile
> where cust = 67
> order by id DESC
> limit 1;
> (mySQL)

That's feeling a lot like my logging table Matt had helped me with
back on March 6th.  There I needed to know the most recent log entry
in a join with a large number of users.

Maybe I'm not looking at the query right but if I want to do a big
join of users and their "current" profile I'm thinking I need a
sub-query.  Not the end of the world, but it's those queries that keep
showing up in our logs as the hogs.

Heck, I'm tempted to denormalize the profile data into the customer
table and use the profile table like an audit table.  Profiles
change very rarely (for most customers it's one-to-one).

> This way you don't have the deal with a current flag at all. The last 
> profile entered for a customer should be the "most current" one.

Yes, or a date would work, too, as Ron pointed out.


> You can create a view to return only the most current profile for each 
> customer and select against by Cust ID that instead.

A materialized view would be best since this the profile data changes
rarely.  Never used them in Postgresql.


> Also, you can carry the profile table ID into your order table, and that 
> will link to the current profile at the time of the customer order.

Yes, that's what I'm doing.

Thanks!

-- 
Bill Moseley.
moseley at hank.org
Sent from my iMutt



More information about the thelist mailing list