how to make the entry unique

[Peter Primrose]

Hi guys,

I have a table of people with the following fields:

firstName
lastName
DOB
email
address
group

as you can see, nothing makes an entry unique. so say I would like to get some info about John Smith, I might found more than one entry for John Smith.

Question: any idea how to make an entry unique? hashing???

I'm using mySQL.

Thanks for any help
Peter

[Shailesh Chandra]

add one more column like person_id in your table and define it as a primary key of table.
and insert a unque value for this column,for every person in you table.

you can also use sequence for person_id

hope this helps

[Craig Jackson]

Actually, I agree with and it would be my choice to do the ...

add one more column like person_id in your table and define it as a primary key of table.

But the email address, in my opinion can be used as a candidate for a unique key, provided you make it a requirement to populate it with data.

[Shailesh Chandra]

Originally posted by Craig Jackson:

But the email address, in my opinion can be used as a candidate for a unique key

now I agree with you for email , but email adress is something that may change frequenly,
eg: I may close my web based email id due to any reason like spam, email provider policy etc

and if corporate e-mail id is there it would change if one change company.

changing e-mail address will cause change in primary key.This case will cause problem later on.

shailesh

[Jeanne Boyarsky]

E-mail could still have a unique index and be used for log-in though. You can map that to your personId. This has the benefit of allowing the user to easily change their e-mail while allowing you to use a stable key.

[Peter Primrose]

thanks for all the replies.

I'm looking for somthing like a sequence, is there any help from Java/Sql that helps making it?

thanks
peter

[Shailesh Chandra]

here is the link for sequences, this will help you.

shailesh
[ March 21, 2005: Message edited by: Shailesh Chandra ]

[Peter Primrose]

thank you Shailesh

this is exactly what i needed.

peter

[Shailesh Chandra]

all the best !

[s choukse]

I have read the reponses posted by all of you and I totally agree that a database sequence will provide the primary key to the table and will resolve this problem.

Having said that I would still not recommend the use of a Databse Sequence as an Unique Identifier. This is because a sequenece has no business significance and when modelling a database we should always model the actual business coz this will keep the schema "healthy" in the long run. I would recommend you should go for a composite primary key and using a database sequence should be the last resort.

Let me know your thoughts.

[Jeanne Boyarsky]

Originally posted by s choukse:
Having said that I would still not recommend the use of a Databse Sequence as an Unique Identifier. This is because a sequenece has no business significance and when modelling a database we should always model the actual business coz this will keep the schema "healthy" in the long run. I would recommend you should go for a composite primary key and using a database sequence should be the last resort.

I agree that a database sequence should be the last resort. However, I think it is the necessary last resort in this case. Most of the fields with business meaning here could change - possibly at the same time.

For example, someone could get married (changing last name), update her e-mail to reflect the new last name and move (changing address) at the same time. She could also decide to legally change her first name while she has to fill out the paper work anyway. This leaves you with DOB and group as unchanging fields in the primary key. (Group could probably change too, but I'm not sure of the business meaning in this case.)

Granted, this is an unlikely scenario. But it is a plausible one. In fact, just changing the e-mail is probably enough to throw a wrench into things. I think it is more important to have an unchanging key than one with business value. If you happen to have one that is both, all the better.

[Paul Sturrock]

This is because a sequenece has no business significance and when modelling a database we should always model the actual business coz this will keep the schema "healthy" in the long run. I would recommend you should go for a composite primary key and using a database sequence should be the last resort

I'm not sure you should get hung up with the "business significance" of a possible candidate key - I'd always use a surrogate key, regardless of whether there is a good natural key available. This is because - as Jeanne points out - its almost impossible to think of a natural key which will remain constant. From an application development point of view too, composite keys tend to create more work than they are worth. I'd always recommend modelling with candidate keys, but implementing the model with surrogate keys. Databases give us an easy way to get a unique, required, constant value - why not use it?