Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 05A769FA1B3 for ; Tue, 28 Aug 2007 12:10:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 46807-07 for ; Tue, 28 Aug 2007 12:10:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.233]) by postgresql.org (Postfix) with ESMTP id A00759FA0DD for ; Tue, 28 Aug 2007 12:10:39 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so1244168nzf for ; Tue, 28 Aug 2007 08:10:38 -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=irzcw0hVlF/pKYs4OEtLR8c3RP4S7kePVLI0RQA2uBDmuSzfe9Wn2x31PfQR1ygoDTsfv8j5usSnLCkZGm4iplVM+FQCNRxt6atdFAqxePjG7Pni9IxSAVwgVkQNRxZAGc5zMbImHX8cgbn+3YLF3DarL0/AAD/PBWHSwCmZsFE= 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=buTwo1QaxNB1bjd0zbH0KGCglBj7cC2CS/f9hbodQ8rN15775fHTw7uTcQAbEynrERED+rFO8cU+U6xVsjwvJnlSwGGF/YnFNjlxgHUMuVUiCJfpGqhtB9lOmPh1GVy0cVpQEBGwWNMjC9Jphi/0kKtXZxmkDABFQNezOV9tJQU= Received: by 10.141.195.18 with SMTP id x18mr3438557rvp.1188313837678; Tue, 28 Aug 2007 08:10:37 -0700 (PDT) Received: by 10.141.40.8 with HTTP; Tue, 28 Aug 2007 08:10:37 -0700 (PDT) Message-ID: <99b656cb0708280810p559380c6tb3d867f3254ed417@mail.gmail.com> Date: Tue, 28 Aug 2007 16:10:37 +0100 From: "Sebastian Ritter" To: "Erik Jones" Subject: Re: Database normalization Cc: pgsql-sql@postgresql.org In-Reply-To: <4A704EF5-A7B2-4279-BA46-D222DF4211EA@myemma.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29306_5508155.1188313837669" References: <99b656cb0708210709j5182b73cna63a2d275efa0e84@mail.gmail.com> <99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.gmail.com> <4A704EF5-A7B2-4279-BA46-D222DF4211EA@myemma.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/242 X-Sequence-Number: 29180 ------=_Part_29306_5508155.1188313837669 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, The views option sounds best in my opinion but sadly i cant play with things like inheritance or views as Im using Django as my ORM and since its relatively new, it doesnt yet support such database operations. Id like the ids for each "entity" to be serial and hence there would be overlap between client_ids and service_ids. Cheers, Sebastian On 8/28/07, Erik Jones wrote: > > On Aug 28, 2007, at 6:47 AM, 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. > > Are client_id and service_id artificial ids? If so, is it an actual > requirement that they have distinct id sequences? I.e. is it > necessary that there can be both client_id=1 and service_id=1? If > not, you can use one table, say Entities, lose client_id, service_id, > and is_clent and replace them with entity_id and entity_type. Then > your foreign key in your updates table just needs to reference > entity_id, or possibly (entity_id, entity_type) if you want to be > really strict about things. If you want to make querying the table > simple for either case create Clients and Services views on the > table. This also gives you the ability to add other entity types > where you may to track whatever kind of updates these are. > > Erik Jones > > Software Developer | Emma(r) > erik@myemma.com > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > ------=_Part_29306_5508155.1188313837669 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi,

The views option sounds best in my opinion but sadly i cant play with things like inheritance or views as Im using Django as my ORM and since its relatively new, it doesnt yet support such database operations.

Id like the ids for each "entity" to be serial and hence there would be overlap between client_ids and service_ids.

Cheers,
Sebastian

On 8/28/07, Erik Jones <erik@myemma.com> wrote:
On Aug 28, 2007, at 6:47 AM, 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.

Are client_id and service_id artificial ids?  If so, is it an actual
requirement that they have distinct id sequences?  I.e. is it
necessary that there can be both client_id=1 and service_id=1?  If
not, you can use one table, say Entities, lose client_id, service_id,
and is_clent and replace them with entity_id and entity_type.  Then
your foreign key in your updates table just needs to reference
entity_id, or possibly (entity_id, entity_type) if you want to be
really strict about things.  If you want to make querying the table
simple for either case create Clients and Services views on the
table.  This also gives you the ability to add other entity types
where you may to track whatever kind of updates these are.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



------=_Part_29306_5508155.1188313837669--