Assistance needed in selecting key?

[ADQUSIT]

hi, i have a table called Member_Info and its fields are

Mem_Name
Mem_Membership_ID
Mem_Status
Mem_Type
Mem_Membership_Date
Mem_Data_Of_Birth
Mem_Tel_No
Mem_Cell_No
Mem_Email
Mem_Mailing_Address

now first question is that
1). Do i make Mem_Membership_ID as primary key in this table?
2). I personally think that I add a new attribute called S.No, and that will be primary key? but the problem is that if I make S.No as primary key then there will be two attributes which can be used for primary key? so what i do now?

[si_the_geek]

Will Mem_Membership_ID have a different value for each record?

Assuming that it is the case, will the values for it ever be edited, or are they just added and then left?

Assuming they are not edited, it would be a very good choice for a primary key.


If either of those assumptions are not correct, creating another field for it might be a good idea.

[ADQUSIT]

Sir, the company says that every member will have a unique membership_id, which will always be unique forever. and can't be assigned to anyone. now what you recommend that do i select the Mem_Membership_ID as a primary key or do i need to create the new attribute S.No as a primary key?

[si_the_geek]

You have confirmed that both of my assumptions are correct, so: "it would be a very good choice for a primary key."

As such, creating another field just to be a primary key would be a waste.

[ADQUSIT]

Will Mem_Membership_ID have a different value for each record?

Assuming that it is the case, will the values for it ever be edited, or are they just added and then left?

Assuming they are not edited, it would be a very good choice for a primary key.


If either of those assumptions are not correct, creating another field for it might be a good idea.

Sir please i didn't pick up the complete meaning of these two bold sentences, please briefly explain them for me that i could pick it more better.

[si_the_geek]

They mean if this is the case:

every member will have a unique membership_id, which will always be unique forever.

...it should be the primary key.

[ADQUSIT]

ohhhh i got that sir. Thank you si the geek,

[ADQUSIT]

hi. there is confusion little. I am asking this for my self. that if lets suppose after 1 year or 2, the company demands that member's membership_id is concern with security, so change the primary key to some other field, that why you recommend sir to do in that situation.

[GaryMazzone]

Then you add a new column to the table (parent and child) populate the new field in the parent table. Update the child tables new field with the new vaule joining on the old PK-FK relationship. once that is done you add a new PK (or unique index) to the parent table, drop the FK on the old table and re-create based on the new fields

[ADQUSIT]

if i make lets say S. No as a primary key which will also be helpful for me to check it out the total number of members i have. If i select it as a PK, so what about the MEM_MEMBERSHIP_ID, this is also a unique attribute. So what you all recommends to do with this one? I mean that when we have more than 1 unique attributes so what should we do?