PHP SQL - 1 field or two?

Author

1 field or two?

toedipper

2005-06-06, 8:56 pm

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.

2005-06-07, 3:56 am

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

Nicholas Sherlock

2005-06-07, 8:57 am

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

Ryan Heuser

2005-06-07, 4:00 pm

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

Marco Hauer

2005-06-08, 8:57 pm

"Nicholas Sherlock" <n_sherlock@hotmail.com> wrote in message
news:d83f85$nc5$1@lust.ihug.co.nz...
> toedipper wrote:
>
> 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:

<select name='countryid' size='1'>";
$query = "select * from countrys order by countryname";
$result = mysql_query($query,$link);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo"<option value='{$row['id']}'";
if ($countryid == $row['id']) { echo" selected";}
echo">{$row['countryname']}</option>\n"; }
mysql_close($link);
echo"</select>

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!

Sponsored Links