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

On Mar 22, 9:05 pm, ciapecki <ciape..._at_gmail.com> 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)

My_Joining_Table

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...

Paul...

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