Re: composite primary key vs. primary key on additional single column

From: Paulie <>
Date: Mon, 23 Mar 2009 10:11:59 -0700 (PDT)
Message-ID: <>

On Mar 22, 9:05 pm, ciapecki <> wrote:

> individuals1 has as well ind2_id populated for some rows (not all)
> individuals2 has as well ind1_id populated for some rows (not all)
> both tables have common attributes e.g. first_name, last_name
> As a requirement I need to have a new table (combined_inds) with all
> the data from individuals1 and from individuals2 which will look like
> this:
> [ind1_id, ind2_id, first_name, last_name]

This is very bad IMHO - you are duplicating information - in an RDBMS, a datum (such as a last name or first name) should be held in one place and one place only - you run the risk of your data getting out of sync in different locations - this is the reason RDBMS's were invented in the first place.

I would recommend something like (although I don't fully understand your requirement)


joinTabId (PK)         Ind1_Id          Ind2_Id
1                                   5                 7
2                                   6              NULL
3                                   9             NULL
4                                   NULL         4
5                                   2                3
6                                   7                NULL
7                                   NULL           8

Have a unique index on Ind1_Id and Ind2_Id, since they cannot occur more than once in the joining table - and you can have a unique index on fields with NULL AFAICR...


> chris
Received on Mon Mar 23 2009 - 12:11:59 CDT

Original text of this message