In-Reply-To: <99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.gmail.com>
References: <99b656cb0708210709j5182b73cna63a2d275efa0e84@mail.gmail.com>
	<99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.gmail.com>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <4A704EF5-A7B2-4279-BA46-D222DF4211EA@myemma.com>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: Database normalization
Date: Tue, 28 Aug 2007 09:46:13 -0500
To: Sebastian Ritter <ritter.sebastian@gmail.com>

On Aug 28, 2007, at 6:47 AM, Sebastian Ritter wrote:

> Hello,
>
> I have a fairly basic question about database design where im not =20
> sure which approach is considered correct.
>
> I have two different entities: Clients and Services. Both allow =20
> users to add progressive updates about the two entities.
>
> The update/message format is exactly the same for both. Should I =20
> make two different tables:
>
>   client_updates and service_updates
>   or
>   one table with extra columns : is_client, client_id, service_id, =20
> where either client_id or service_id would be null depending on the =20=

> is_client boolean?
>
> The major problem is simply relating the foreign key in the updates =20=

> table back to the correct entity, client or service.

Are client_id and service_id artificial ids?  If so, is it an actual =20
requirement that they have distinct id sequences?  I.e. is it =20
necessary that there can be both client_id=3D1 and service_id=3D1?  If =20=

not, you can use one table, say Entities, lose client_id, service_id, =20=

and is_clent and replace them with entity_id and entity_type.  Then =20
your foreign key in your updates table just needs to reference =20
entity_id, or possibly (entity_id, entity_type) if you want to be =20
really strict about things.  If you want to make querying the table =20
simple for either case create Clients and Services views on the =20
table.  This also gives you the ability to add other entity types =20
where you may to track whatever kind of updates these are.

Erik Jones

Software Developer | Emma=AE
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