October 7, 2004 at 3:51 pm
Hi,
I am a bit confused on what to do with regard to selecting a primary key for the following table:
CREATE TABLE [record_sightings] (
[Person_ID] [int] NULL ,
[Place_ID] [int] NOT NULL ,
[bird_ID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Questionable] [int] NULL ,
[Number_Observed] [int] NULL ,
[Estimated] [int] NULL ,
[Source] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Export_Code] [int] NULL ,
[Sighting_Date] [datetime],
[Hab_locale] [int] NULL ,
) ON [PRIMARY]
GO
the problem is Person_ID, place_ID, bird_id have many to many relationship amongst them.
Any suggestions on how should I proceed in selecting a PK will be greatly appreciated.
thanks,
V
October 7, 2004 at 4:23 pm
I'm not sure I understand the relationship requirement. You could simply make a primary key of all three columns. Therefore, so long as any one differs you are allowed a new row. For greater detail answer the following:
Can a single Person_ID have multiple Place_IDs?
Can there be multiple instances of a single Person_ID/Place_ID combination?
Can a single Place_ID have multiple Person_IDs?
Can a single Person_ID/Place_ID combination have multiple bird_IDs?
Can there be multiple instances of a single Person_ID/Place_ID/bird_ID combination?
There are other combinations. I'll let you volunteer any you think are notable.
October 7, 2004 at 4:39 pm
>Can a single Person_ID have multiple Place_IDs?
Unfortunately yes. And the date we got, a single person have same place_IDS (I know bad data entry).
>Can there be multiple instances of a single Person_ID/Place_ID combination?
Yes (see above).
>Can a single Place_ID have multiple Person_IDs?
Yes.
>Can a single Person_ID/Place_ID combination have multiple bird_IDs?
Yes.
>Can there be multiple instances of a single Person_ID/Place_ID/bird_ID combination?
Unfortunately Yes, and this is what messing up. Not only this some of the other columns can be same too. In some cases we have only on column where there is a difference in data.
For example, we have two rows of data which look like:
row1: 2, 101, bird_01, 0, 4, 0, Null, NULL, 0, 0, 1
row 2: 2, 101, bird_01, 0, 0, 0, NULL, NULL, 0, 0, 1
We are in a fix on how to handle such a data. Hence we thought we will ask for expert opinions.
thank you,
V
October 12, 2004 at 7:18 am
In addition to what has been stated, with the DDL posted, only Place_ID can be part of a primary key as all columns in a primary key have to be NOT NULL.
By its definition PRIMARY KEY is the primary key used to retrieve data and whether or not it is CLUSTERED will make a difference on INSERT/SELECT statement efficiency.
So, whether or not you add a primary key depends on how the data is inserted, modified and retrieved.
Without any indexes (primary keys are indexes too) sql will be forced to do table scans to locate any data (even one row) and even with indexes sql may decide to do index scans instead of seeks if the data volumn to be selected is large.
If you are only inserting data and not modifying then retrieval should dictate any primary key/index to boost performance but remember inserts in clustered data can be costly as the data has to be reorganised.
As already mentioned if you have duplicate valid data then this negates the possibility of a primary key.
I would suggest that your best bet would be to analyse your queries to see if any optimum indexes can be added to speed up the queries and any primary key would be meaningless and not useful unless you need to update the data !
Far away is close at hand in the images of elsewhere.
Anon.
October 12, 2004 at 8:16 am
Dave,
To most part we will run SELECT queries on the table. But occassionally we will need to run an insert or update on the table.
Based on the discussion so far I think we need to go composite key routes, and change some of the columns to not allow nulls. I will beat on it and see how that goes.
thank you all for all your helpful suggestions and comments,
as always they helped out,
-V
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply