Re: composite primary key vs. primary key on additional single column
Date: Mon, 23 Mar 2009 10:11:59 -0700 (PDT)
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
> [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...
Received on Mon Mar 23 2009 - 12:11:59 CDT