Calling by 'id number' how do you know what the content is with out a name?

The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Calling by 'id number' how do you know what the content is with out a name?

Hi all

Having a little problem understanding how I can recognize my 'id number' when calling from my table? I was calling from my table by the 'venue name' so I could easily recognize what the content was about, but after a bit of reading and a previous thread it seems the best bet is to call from the 'id number'. ?

Below is the php code which I currently have and works, but I have no clue what each 'id number' stands for until it's shown on my page?

PHP Code:

<?php
require_once('includes/mysql_connect.inc.php');

// The id should be numerical, not a word. It should be an auto-increment index in the venues table. ( left by some one on a previous thread )

Typically the IDs are set when you create the database, aka a primary key depending on how the database is set up. Typically they are set to unique and auto increment, at least that's one theory. Whoever created the database can explain exactly how the IDs are created, can you provide more detail?

First off, I'm going to counter-argue that using a string for your primary key is not as bad as some claim. If you doubt me, time it yourself - you'll see no appreciable difference between integer keys and varchar or char keys. (Do not, however, use TEXT keys!) I've run benchmarks in MySQL on all three types of keys, using MyISAM, InnoDB, and even MEMORY (aka HEAP) tables, and every time I come up with similar query times for SELECT, DELETE, INSERT, and UPDATE statements ("similar times" means similar within an engine; I've compared various engines as well, but that's a separate matter).

There are many cases where a numerical surrogate key is needed (a "surrogate key" is a key that uses data that is not truly part of the row's data, such as the ID created by an auto_increment column in MySQL), but your case seems to be one where a natural key (i.e. the venue name) can be used (that is, of course, provided that venue names are unique). In database design theory, it is generally accepted that natural keys are better because they have a stronger association with the data they represent; surrogate keys are only used when necessary.

Okay, now that all that's said, here's how you fix your current problem:
I'm assuming that when you generate your list of venue links, you're doing so by pulling them from the database and iterating through the resultset ("SELECT venue FROM venues"). If this is the case, then it's a simple matter of also selecting the ID when you do so:

Quality! Just shed some serious light on where I was heading next, thank you!

There are many cases where a numerical surrogate key is needed (a "surrogate key" is a key that uses data that is not truly part of the row's data, such as the ID created by an auto_increment column in MySQL), but your case seems to be one where a natural key (i.e. the venue name) can be used (that is, of course, provided that venue names are unique).

I was thinking about something like this early, mainly because some venue names have 2-3 words, and i was thinking of using 1 main word as the id name i'll call for, easier again if you get what i mean?

example: venue/venue has three
but the address bar will have one: venue/something

the venue will be shown as a header on the page, so i couldn't really break that down to one word.

I'm assuming that when you generate your list of venue links, you're doing so by pulling them from the database..

I wasn't but i will be doing now thanks to your example, i will still need to type links myself to specific links when not calling from the database though, so if i add a new column (surrogate key) - (iam i understand this properly - as above) with a single word id for that venue could i then call it by that id name?

In the case of multi-word keys, for URL purposes, you would have to do something else... maybe you could replace spaces with underscores '_' for the links, and then before you look them up in your database you replace the underscores with spaces. This would maintain "pretty", SEO-friendly URLs while still allowing you to use the natural keys in your database.

Another option along similar lines would be to use underscores in the database directly, and only replace them with spaces when displaying as headers/link names/etc. I like this one myself, but I guess I'm just strongly biased against spaces except for print meant to be read by humans (coming as I do from a *nix background).

Thus "Venue With Four Words" would appear in your database as "Venue_With_Four_Words", would have the link "venue/Venue_With_Four_Words", and when you display it for the user you run it through str_replace("_", " ", $venuename).

Off Topic:

Just to illustrate my anti-space bias, Windows drives me absolutely bonkers because "My Computer" and "My Documents" and "Program Files" and so many others have spaces!! I'd be so much happier with camel-case ("MyComputer", "MyDocuments").

thanks, really got me thinking in different ways now, more advance that is.. lol cheers
A couple more things then if you don't mind:

1. If i use venuename instead of venue_name, will that have any seo differences?
2. Also you say use venue_name_three, my venue names are first char capital and i really wanted just a lower-case one word for the venue/lower?
3. my php code, which works fine by the way. just wondering with your code in place do i need to keep the previous bit of code in place for everything to function properly?

thanks, really got me thinking in different ways now, more advance that is.. lol cheers
A couple more things then if you don't mind:

1. If i use venuename instead of venue_name, will that have any seo differences?

Edit: See Rudy's post above.
Also, keep in mind that whatever you use needs to be easily translatable into keys for your database, so compacting into a single word when in your database you have 2 or 3 or more words is probably not your best bet.

Originally Posted by computerbarry

2. Also you say use venue_name_three, my venue names are first char capital and i really wanted just a lower-case one word for the venue/lower?

You can run your venue name through strtolower to print the URL, but remember you'll need to re-upper-case the first letter of each word if that's what it is in your database. See the user comments in ucfirst for a function that will do that (or similar, can't quite recall).

Originally Posted by computerbarry

3. my php code, which works fine by the way. just wondering with your code in place do i need to keep the previous bit of code in place for everything to function properly?

My PHP code was meant to help you use surrogate keys. If you instead use your natural keys (which I very strongly recommend) then just adapt your own previous code and ignore mine.

let me get this right, a 'surrogate key' is something you create to replace your id when calling to the database for what given reasons, maybe the venue name issue? and the natural key is a column/id which has your orgianal data?

surrogate key:
If i add another column to my table for the one word lowercase which will show in my address bar? correct? and just show the original venue name on the page, but have a one word id instead for the seo in address bar?

Surrogate keys are keys that are composed of data that is added to the row for the sole purpose of being keys; in general they have no inherent connection with the data itself. An example would be a MySQL auto_increment column.

Natural keys are keys that use part of the data itself. These are preferred because of the inherent strong connection between the key and the data (due to the fact that the key is in fact a part of the data). Examples would be a date (for e.g. a database of holidays) or the name of a venue (in your case).

Rudy and I are both (correct me if I'm wrong, Rudy) suggesting that you ditch any form of surrogate key from your database schema and use your venue names. For the purpose of links, replace spaces with hyphens '-'. My code that I provided is only necessary when a surrogate key is being used; if you follow our suggestion to use the natural key of your venue name, my code is unnecessary as written, but could be modified to pull your list of venues from the database; to do so, drop the id column from the SELECT statement and replace $row['id'] with str_replace(" ", "-", $row['venue']).

Just be sure to run that back through str_replace to make the '-' all be ' '!

An example of a surrogate key would be the ID column you were previously advised to use. The number the database assigned to each of your venues had no inherent connection to the venues - what the hell is venue #14??

A natural key, on the other hand, would be the venue name itself. Since each venue has a name that uniquely identifies it, this serves as a great primary key for the table. The key value itself, then, not only references the row where you can find more information about that venue, it also logically identifies the venue itself ("Carlson Center - I know that venue! That's where such-and-such an event was held last week!").

The general rule of thumb for telling the difference between a surrogate and natural key is whether or not you can look at the key and associate it in your own mind with the data it represents. If that key is e.g. "Carlson Center", well I know that that happens to be a local venue in my town. Thus it's most likely a natural key for the venue table. If, however, I'm looking at 3872, well, what the hell is that? It may in fact reference the "Carlson Center", but looking at it on its own you have no idea.

Here's one: "20071225". Can you discern whether that's a natural or surrogate key? How about if I phrase it this way: "December 25th, 2007". Can you figure it out now? Here's another hint: the table name is "holidays". Holidays are a great example of (potentially) numerical keys that are in fact natural keys. For example, 20070528 would represent the US's observance of Memorial Day in 2007 (2007-05-28 = May 28th, 2007).

Keep in mind that natural keys strongly relate to their respective data, while surrogate keys only make sense in the context of the database itself. With that in mind, you should have no trouble telling the difference.