only one question please, maybe it is a faq, but could be a boolean question
for
people that haven't time...
I have My_user table, here there's a field 'nick_name' not Null and unique,
is it good for primary Key or is better a field int. identity.
Thanks"VincEnzo" <TOGLIv-franco@.libero.it> wrote in message
news:KSLoc.51573$Qc.2092127@.twister1.libero.it...
> Hi,
> only one question please, maybe it is a faq, but could be a boolean
question
> for
> people that haven't time...
> I have My_user table, here there's a field 'nick_name' not Null and
unique,
> is it good for primary Key or is better a field int. identity.
> Thanks
If nickname must always be present and is a common way of identifying a user
then it seems on the information so far to be a good candidate for a primary
key.
However, as for the second question - (I assume you mean would an integer
identity be a better candidate for a primary key), and not (would nickname
be better stored as an intereger intentity) then this depends a bit on the
other tables in your database.
How many people do you have in your User-table ... If it is very high ( >
10,000 ) then you might find it very difficult to continue to find unique
nicknames ...
It also depends on the number of other related tables you have that will
refer back to this table.
If the number of related-records is very high ( > 100,000 ) , then you may
find that table-size is important and than you want narrow tables.
On the other hand, if you have a small database then you may fnid that
readability is more important than efficiency or table storage.
Choosing a primary key can depend on a lot more things than I've mentioned
(and the numbers I've quoted are just fictitious examples ) ...
The best answer to "what is the best Primary Key" is often "it depends"|||thanks Steven Wilmot
this answeresatisfy me.
I thought that numbers was more faster than string
but the true was more coplexity
ciao
Vincenzo
"Steven Wilmot" <steven-news@.wilmot.me.uk> ha scritto nel messaggio
news:40a3a8ce$0$58821$5a6aecb4@.news.aaisp.net.uk.. .
> "VincEnzo" <TOGLIv-franco@.libero.it> wrote in message
> news:KSLoc.51573$Qc.2092127@.twister1.libero.it...
> > Hi,
> > only one question please, maybe it is a faq, but could be a boolean
> question
> > for
> > people that haven't time...
> > I have My_user table, here there's a field 'nick_name' not Null and
> unique,
> > is it good for primary Key or is better a field int. identity.
> > Thanks
> If nickname must always be present and is a common way of identifying a
user
> then it seems on the information so far to be a good candidate for a
primary
> key.
> However, as for the second question - (I assume you mean would an integer
> identity be a better candidate for a primary key), and not (would nickname
> be better stored as an intereger intentity) then this depends a bit on the
> other tables in your database.
> How many people do you have in your User-table ... If it is very high ( >
> 10,000 ) then you might find it very difficult to continue to find unique
> nicknames ...
> It also depends on the number of other related tables you have that will
> refer back to this table.
> If the number of related-records is very high ( > 100,000 ) , then you may
> find that table-size is important and than you want narrow tables.
> On the other hand, if you have a small database then you may fnid that
> readability is more important than efficiency or table storage.
> Choosing a primary key can depend on a lot more things than I've mentioned
> (and the numbers I've quoted are just fictitious examples ) ...
> The best answer to "what is the best Primary Key" is often "it depends"|||>> I have My_user table, here there's a field [sic] "nickname
<datatype> NOT NULL UNIQUE" Is it good for PRIMARY KEY or is better a
field [sic] INTEGER IDENTITY. <<
Rows are not records; fields are not columns; tables are not files.
When you look for a key, ask yourself:
1) Is it unique?
2) Is it verifiable in the reality modeled by the database?
3) Can it validate itself in the front end? (Check digits? regular
expression?)
4) Is it familiar to the users?
5) Is it a superkey?
Clearly the IDENTITY property is not a consideration at all. It is a
proprietary non-relational, physical locator for the internal
representation of the data in storage and has absolutely nothing that
can be validated or verified.
But let's ask the same questions about "nickname" and see some
problems. I am assuming that "nickname" is a character string with a
nickname in the usual sense in it, sicne you did not bother with DDL
or specs.
1) Do you ever have two people with the same nickname? In the real
world, yes. Every redheaded guy gets the nickname "Red" or "Carrot
top", so you will have to assign these things and force them to be
unique in the database.
2) You can ask everyone what their nickname is, and hope they have
one. And that they give you the one in the database, not the one in
the real world.
3) No.
4) Not the one in the database.
5) No.
Therefore, I would suggest that you look for a better key. Perhaps a
government assigned identifier or something like that.|||thx
"--CELKO--"
ok my example, nickname, not was happy, but
if we have a table with a few rows and has two field
idgategory and nameCategory.
the value of nameCategory is not Null and unique.
Can i get nameCategory like PK and throw idgategory that it is identity...
Now I understand YES
right Steven? right Celko?
grazie
ciao
<jcelko212@.earthlink.net> ha scritto nel messaggio
news:18c7b3c2.0405131115.3e974df9@.posting.google.c om...
> >> I have My_user table, here there's a field [sic] "nickname
> <datatype> NOT NULL UNIQUE" Is it good for PRIMARY KEY or is better a
> field [sic] INTEGER IDENTITY. <<
> Rows are not records; fields are not columns; tables are not files.
> When you look for a key, ask yourself:
> 1) Is it unique?
> 2) Is it verifiable in the reality modeled by the database?
> 3) Can it validate itself in the front end? (Check digits? regular
> expression?)
> 4) Is it familiar to the users?
> 5) Is it a superkey?
> Clearly the IDENTITY property is not a consideration at all. It is a
> proprietary non-relational, physical locator for the internal
> representation of the data in storage and has absolutely nothing that
> can be validated or verified.
> But let's ask the same questions about "nickname" and see some
> problems. I am assuming that "nickname" is a character string with a
> nickname in the usual sense in it, sicne you did not bother with DDL
> or specs.
> 1) Do you ever have two people with the same nickname? In the real
> world, yes. Every redheaded guy gets the nickname "Red" or "Carrot
> top", so you will have to assign these things and force them to be
> unique in the database.
> 2) You can ask everyone what their nickname is, and hope they have
> one. And that they give you the one in the database, not the one in
> the real world.
> 3) No.
> 4) Not the one in the database.
> 5) No.
> Therefore, I would suggest that you look for a better key. Perhaps a
> government assigned identifier or something like that.|||>> ... two fields [sic]category_id IDENTITY and category_name NOT NULL
UNIQUE. <<
Rows are not records; fields are not columns; tables are not files;
there is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless.
If you want to build a look-up table with a code called "Categories",
then you need to design this code. Is it a vector code (ISO tire
sizes)? A hierarchical code (Dewey Decimal)? Concatenation code?
What?
The problem is that you want a single, simple magic answer. There is no
magic answer. Designing a database is hard work and takes years to
learn to do correctly.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||ok, thanks
i go to work
see you in two years
ciao
"Joe Celko" <jcelko212@.earthlink.net> ha scritto nel messaggio
news:40a3fd38$0$201$75868355@.news.frii.net...
> >> ... two fields [sic]category_id IDENTITY and category_name NOT NULL
> UNIQUE. <<
> Rows are not records; fields are not columns; tables are not files;
> there is no sequential access or ordering in an RDBMS, so "first",
> "next" and "last" are totally meaningless.
> If you want to build a look-up table with a code called "Categories",
> then you need to design this code. Is it a vector code (ISO tire
> sizes)? A hierarchical code (Dewey Decimal)? Concatenation code?
> What?
> The problem is that you want a single, simple magic answer. There is no
> magic answer. Designing a database is hard work and takes years to
> learn to do correctly.
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||"Steven Wilmot" <steven-news@.wilmot.me.uk> wrote in message news:<40a3a8ce$0$58821$5a6aecb4@.news.aaisp.net.uk>...
> "VincEnzo" <TOGLIv-franco@.libero.it> wrote in message
> news:KSLoc.51573$Qc.2092127@.twister1.libero.it...
> > Hi,
> > only one question please, maybe it is a faq, but could be a boolean
> question
> > for
> > people that haven't time...
> > I have My_user table, here there's a field 'nick_name' not Null and
> unique,
> > is it good for primary Key or is better a field int. identity.
> > Thanks
> If nickname must always be present and is a common way of identifying a user
> then it seems on the information so far to be a good candidate for a primary
> key.
> However, as for the second question - (I assume you mean would an integer
> identity be a better candidate for a primary key), and not (would nickname
> be better stored as an intereger intentity) then this depends a bit on the
> other tables in your database.
> How many people do you have in your User-table ... If it is very high ( >
> 10,000 ) then you might find it very difficult to continue to find unique
> nicknames ...
> It also depends on the number of other related tables you have that will
> refer back to this table.
> If the number of related-records is very high ( > 100,000 ) , then you may
> find that table-size is important and than you want narrow tables.
> On the other hand, if you have a small database then you may fnid that
> readability is more important than efficiency or table storage.
> Choosing a primary key can depend on a lot more things than I've mentioned
> (and the numbers I've quoted are just fictitious examples ) ...
> The best answer to "what is the best Primary Key" is often "it depends"
The only condition to define a primary key is that it has to be unique.|||>> The only condition to define a primary key is that it has to be
unique. <<
Necessary, but not sufficient. An invalid and/or unverifiable key is
worse than useless; it is dangerous.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Necessary, but not sufficient. An invalid and/or unverifiable key is
>worse than useless; it is dangerous.
>--CELKO--
I agree but would you care to elaborate a little bit on this ?
Randy
http://members.aol.com/rsmeiner|||>> I agree but would you care to elaborate a little bit on this [An
invalid and/or unverifiable key is worse than useless; it is dangerous]?
<<
Scenario 1: The part number is a simple sequential number. ANY integer
could be a part number and it has no syntax or check digit to tell me.
I cheerfully send the orphans rat poison instead of penicillin when the
input clerk makes a simple typo on the order form.
Scenario 2: There is a natural key in the data, but it is not declared
unique in the schema. Instead, the DB designer used a proprietary
locator (rowid, auto-increment, identity, GUID, etc.). The real key
changes, but the locator does not. Data integrity is destroyed. Then
the Sarbanes-Oxley auditors and the 60 MINUTES crew show up on the same
day.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Scenario 1: The part number is a simple sequential number. ANY integer
>could be a part number and it has no syntax or check digit to tell me.
>I cheerfully send the orphans rat poison instead of penicillin when the
>input clerk makes a simple typo on the order form.
>Scenario 2: There is a natural key in the data, but it is not declared
>unique in the schema. Instead, the DB designer used a proprietary
>locator (rowid, auto-increment, identity, GUID, etc.). The real key
>changes, but the locator does not. Data integrity is destroyed. Then
>the Sarbanes-Oxley auditors and the 60 MINUTES crew show up on the same
>day.
>--CELKO--
I have fought the Scenario 2 battle more then a few times.
Seems that the basic M$ classes are geared towards this.
They actually teach students to use Identity fields as the primary
key even if there is a valid and unique column that can be used.
I can always tell when someone has been to that 1st course or 2.
Even had 1 little snot tell me that my method was "old school".
Just because I used a valid column as the primary key.
Randy
http://members.aol.com/rsmeiner|||Joe Celko (jcelko212@.earthlink.net) writes:
> Scenario 1: The part number is a simple sequential number. ANY integer
> could be a part number and it has no syntax or check digit to tell me.
> I cheerfully send the orphans rat poison instead of penicillin when the
> input clerk makes a simple typo on the order form.
Only if your application is designed to display the primary key for
the user. Which in many cases it shouldn't
> Scenario 2: There is a natural key in the data, but it is not declared
> unique in the schema. Instead, the DB designer used a proprietary
> locator (rowid, auto-increment, identity, GUID, etc.). The real key
> changes, but the locator does not. Data integrity is destroyed. Then
> the Sarbanes-Oxley auditors and the 60 MINUTES crew show up on the same
> day.
The good thing is that the natural key changed, but the database was
left bascially untouched. It was a simple update.
The problem with natural keys is that they rarely live up to the
strong requirements for a primary key in a database. Typical cases
are that some items does for some reason not have the expected value
(so you have to invent one), or there are unexpected duplicates.
These keys are often good enough for data entry and searching, but the
application have to handle the situation that there are two persons
with the same SSN, or corresponding registration number.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment