Re: Database normalization

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(at)myemma(dot)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