Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 5E59D2E0170 for ; Sun, 25 Nov 2007 13:56:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 23876-03 for ; Sun, 25 Nov 2007 13:56:32 -0400 (AST) Received: from serv01.siteground137.com (ns1.siteground137.com [67.15.250.10]) by postgresql.org (Postfix) with ESMTP id 429332E0168 for ; Sun, 25 Nov 2007 13:56:32 -0400 (AST) Received: from c-71-193-202-175.hsd1.or.comcast.net ([71.193.202.175]:41562 helo=[192.168.1.2]) by serv01.siteground137.com with esmtpa (Exim 4.68) (envelope-from ) id 1IwLiV-0004qB-TK; Sun, 25 Nov 2007 11:56:28 -0600 Subject: Re: EAV or not to EAV? From: Jeff Davis To: Reg Me Please Cc: pgsql-general@postgresql.org In-Reply-To: <200711220948.36142.regmeplease@gmail.com> References: <200711220948.36142.regmeplease@gmail.com> Content-Type: text/plain Date: Sun, 25 Nov 2007 10:01:29 -0800 Message-Id: <1196013689.24251.342.camel@jdavis> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - serv01.siteground137.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - j-davis.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/1301 X-Sequence-Number: 124297 On Thu, 2007-11-22 at 09:48 +0100, Reg Me Please wrote: > I have to take into account the update history of every single > field into a number of tables. > Those updates can come to the future (next values) but also to the > past (history fix) and apply only to some fields, usually not to the > entire record. > Every lookup in those tables is always related to a timestamp that > normally is the current timestamp, but can also be a past timestamp. > > I've come up with a "traditionally regretted" EAV design with the add of > timestamps for the history. And the related problems, some of which have > been solved by Joe Conways's crosstab contrib. Here are two more ideas: 1) Vertically partition the tables so that each field you want to track is in its own table, and then track the history of those individual tables. Then join the tables on a key that doesn't change when you need to see the whole table. This works well if you only have a few non-key fields per table. 2) Store a normal history, but also store a bitmap of the fields that change in each record. You might use partial indexes to be able to quickly select only those records where a certain field has changed. EAV will give you many problems down the line, and I don't think it will solve anything for you. Regards, Jeff Davis