Hello,
Imagine I have a table called countries. Every country is the world has a
unique name so should my table just have one field called ie CountryName or
should I have 2 fields including eg Countryid?
I mean, if the countryname fields are unique anyway what is the point of
creating an id field? It just seems to me to create extra baggage.
I'm all new to this so someone point me in the right direction!
Thanks,
td.
toedipper wrote:
>
> Imagine I have a table called countries. Every country
> is the world has a unique name so should my table just
> have one field called ie CountryName or should I have 2
> fields including eg Countryid?
>
> I mean, if the countryname fields are unique anyway what
> is the point of creating an id field?
History, for one... A few decades ago the former Belgian
Congo became Zaire, while the former French Congo retained
the name Congo. These days, the former Belgian Congo is
called Democratic Republic of Congo, while the former French
Congo is officially named the Republic of Congo. So how do
you figure which Congo you are talking about unless you have
a numeric code for it? How would you handle the name changes
from Belgian Congo to Zaire to D.R. of Congo, if the name
were a key?
Another reason is multilinguality. If you plan to store
information in multiple languages, you may want to take into
consideration the fact that countries' names are written
differently in different languages. Moreover, some countries
have multiple official laguages and, consequently, multiple
official names. Ireland has two, Switzerland, four...
Cheers,
NC
toedipper wrote:
> I mean, if the countryname fields are unique anyway what is the point of
> creating an id field? It just seems to me to create extra baggage.
It should be much faster to index on an integer field than a string field.
Cheers,
Nicholas Sherlock
NC wrote:
> ... So how do
> you figure which Congo you are talking about unless you have
> a numeric code for it? How would you handle the name changes
> from Belgian Congo to Zaire to D.R. of Congo, if the name
> were a key?
I agree with NC. You will probably be making refences to these rows in this
table. Let's say you have a table called languages, and these language
rows point to countries that they are used in. These pointers will break
if you are using the name of the country as its key, and you change the
name without changing every single reference to it.
Personally, I always use an auto_increment key for every table I use. It
just makes me feel better. :)
Peace,
Ryan 'RetroMan' Heuser
"Nicholas Sherlock"
news:d83f85$nc5$1@lust.ihug.co.nz...
> toedipper wrote:
> > I mean, if the countryname fields are unique anyway what is the point of
> > creating an id field? It just seems to me to create extra baggage.
>
> It should be much faster to index on an integer field than a string field.
>
> Cheers,
> Nicholas Sherlock
First it's much faster and I think today we can live with 1 byte for this.
I use it like this:
CREATE TABLE countrys(id TINYINT UNSIGNED, countryname VARCHAR(40));
But more important is that you can do something like this:
Like you can see is from every user only the countryid stored in the table.
So this is not only much faster but save also much space. For every user
only 1 byte in place of the countryname!
I find it helpful to have a contry ID field that is standardized. SAy,
three-letter code. Then you can use the country name field for display
and unimportant stuff, but when you want to be sure what you're doing in
the code, you do operations and queries with the ID field. makes the
database more efficient and faster too.
-- PD-man.
toedipper wrote:
> Hello,
>
> Imagine I have a table called countries. Every country is the world has a
> unique name so should my table just have one field called ie CountryName or
> should I have 2 fields including eg Countryid?
>
> I mean, if the countryname fields are unique anyway what is the point of
> creating an id field? It just seems to me to create extra baggage.
>
> I'm all new to this so someone point me in the right direction!
>
> Thanks,
>
> td.
>
>
>
>
>
>
>