Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 97C249FA0A1 for ; Tue, 28 Aug 2007 10:01:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 89159-04 for ; Tue, 28 Aug 2007 10:01:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.225]) by postgresql.org (Postfix) with ESMTP id 886149FA044 for ; Tue, 28 Aug 2007 10:01:00 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so1255060wri for ; Tue, 28 Aug 2007 06:00:59 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=dC6in76076GIcS3vB4I8SzP6kasCvzhgJR84NbfL9vzXtfq3/nX+dwDCzuWMhHHQVj2MsFOG6UMlGKo0YEoYvnCnR3bWsKn1CW6a+MIXKXhSW7c+FLhZLm1JcauAGQLfmjfPqSz3Zg7CIimHQ7iXPFkQUnj64y8/9RsqYTJINIU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=mIRYrif2LFU+QmpDoufExvT5Tz0ct6B6OgPWRzQB6ln8wKEfcuTns7hFp7Oa+fIkF7T6esqNzf2918LdpM8Vjibard27SRI5C9qL1HvzDDM+ZzKZIOxnl/uM63SwezeJVEIpys9ai3xZOF+N/lcjaEO8IjlEU0INPOx8sDO/EDY= Received: by 10.141.196.13 with SMTP id y13mr373780rvp.1188306055286; Tue, 28 Aug 2007 06:00:55 -0700 (PDT) Received: by 10.141.15.12 with HTTP; Tue, 28 Aug 2007 06:00:55 -0700 (PDT) Message-ID: Date: Tue, 28 Aug 2007 16:00:55 +0300 From: "Asko Oja" To: "Sebastian Ritter" Subject: Re: Database normalization Cc: pgsql-sql@postgresql.org In-Reply-To: <99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_76908_19756372.1188306055251" References: <99b656cb0708210709j5182b73cna63a2d275efa0e84@mail.gmail.com> <99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/236 X-Sequence-Number: 29174 ------=_Part_76908_19756372.1188306055251 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi I would create one table to log updates with type field and one id filed that contains either client id or service id according to type. On such a table i would forget about foreign keys (thay are better to be avoided anyway if you have millions of records in tables). That way you can share code that displays update history or even create some generic components. In my experience normalized databases have ended up with hundreds of tables and code and screen generation that is very far from what users actually need. Denormalizing and refactoring these databases reduces number of tables by magnitude. Regards, Asko On 8/28/07, Sebastian Ritter wrote: > > Hello, > > > > > I have a fairly basic question about database design where im not sure > > which approach is considered correct. > > > > I have two different entities: Clients and Services. Both allow users to > > add progressive updates about the two entities. > > > > The update/message format is exactly the same for both. Should I make > > two different tables: > > > > client_updates and service_updates > > or > > one table with extra columns : is_client, client_id, service_id, where > > either client_id or service_id would be null depending on the is_client > > boolean? > > > > The major problem is simply relating the foreign key in the updates > > table back to the correct entity, client or service. > > > > Regards, > > Sebastian > > > > > ------=_Part_76908_19756372.1188306055251 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi

I would create one table to log updates with type field and one id filed that contains either client id or service id according to type. On such a table i would forget about foreign keys (thay are better to be avoided anyway if you have millions of records in tables).
That way you can share code that displays update history or even create some generic components.
In my experience normalized databases have ended up with hundreds of tables and code and screen generation that is very far from what users actually need. Denormalizing and refactoring these databases reduces number of tables by magnitude.

Regards,
Asko


On 8/28/07, Sebastian Ritter <ritter.sebastian@gmail.com> wrote:
Hello,

I have a fairly basic question about database design where im not sure which approach is considered correct.

I have two different entities: Clients and Services. Both allow users to add progressive updates about the two entities.

The update/message format is exactly the same for both. Should I make two different tables:

  client_updates and service_updates
  or
  one table with extra columns : is_client, client_id, service_id, where either client_id or service_id would be null depending on the is_client boolean?

The major problem is simply relating the foreign key in the updates table back to the correct entity, client or service.

Regards,
Sebastian



------=_Part_76908_19756372.1188306055251--