From: Erik Jones <erik@myemma.com>
To: Sebastian Ritter <ritter.sebastian@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database normalization
Date: Tue, 28 Aug 2007 09:46:13 -0500
Message-ID: <4A704EF5-A7B2-4279-BA46-D222DF4211EA@myemma.com> (raw)
In-Reply-To: <99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.gmail.com>
References: <99b656cb0708210709j5182b73cna63a2d275efa0e84@mail.gmail.com>
<99b656cb0708280447x4eb7c45axec01c8d0ebc6c82d@mail.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
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, ritter.sebastian@gmail.com
Subject: Re: Database normalization
In-Reply-To: <4A704EF5-A7B2-4279-BA46-D222DF4211EA@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox