Page 1 of 1

Change Primary Key or rewrite?

Posted: Sun Jun 05, 2011 8:58 pm
by mikes1471
Hi guys

I have the beginnings of a social community website - Not based on a CMS

The website uses a primary key of 'id' and I have decided I would like 'mydomainname.com/username' to be displayed in the URL rather than 'mydomainname.com/profile.php?id=3' for instance.

My question is, is a primary key change needed to make 'username' primary or can the 'id' remain the primary key and I create some kind of rewrite script to 'mask?' the URL?

Mike

Re: Change Primary Key or rewrite?

Posted: Sun Jun 05, 2011 10:29 pm
by flying_circus
mikes1471 wrote:My question is, is a primary key change needed to make 'username' primary or can the 'id' remain the primary key and I create some kind of rewrite script to 'mask?' the URL?
Mike,

A primary key in a database is basically a unique field. I do not understand the correlation between a database primary key and a rewritten url.

A rewritten URL like you have suggested typically involves url rewriting on the web server, such as apache's mod_rewrite. You can use a .htaccess file to rewrite the typed URL http://www.example.org/myPage into the logical equivalent (as php see's it) of http://www.example.org/index.php?page=myPage

your index.php would act as a front controller, pull the page var from the URL querystring, and run a database query to look up the user registered in your system and display the relevant information.

None of those actions are affected by the primary key. You should index the database fields that you would query against frequently (such as the username).

Re: Change Primary Key or rewrite?

Posted: Sun Jun 05, 2011 10:40 pm
by twinedev
You can have BOTH the id and username fields be a primary key, this way they will both be indexed for faster lookup of the username from the URL.

If you do not decided to make username a primary key, make sure your code for when someone signs up checks to make sure it doesn't already exist. Also, if you haven't already, consider what characters you are allowing in your username field compared to what you would want in your URL (while "greg k" could be used in the url as "greg%20k", I find it better to limit what characters will display for easier URL reading ;-)

-Greg

Re: Change Primary Key or rewrite?

Posted: Thu Jun 09, 2011 11:44 am
by social_experiment
twinedev wrote:You can have BOTH the id and username fields be a primary key, this way they will both be indexed for faster lookup of the username from the URL.
If you do not decided to make username a primary key, make sure your code for when someone signs up checks to make sure it doesn't already exist.
Two primary keys would mean two tables. The OP can always make (if he hasn't already) the username field unique (an extra precaution) which would stop any similar username entries or check for names with a similar existing value as you mentioned.

Re: Change Primary Key or rewrite?

Posted: Thu Jun 09, 2011 11:53 am
by flying_circus
social_experiment wrote:Two primary keys would mean two tables.
You can have 2 primary keys in 1 table, its called a composite key. Unfortunately, it my "just know enough about sql to be dangerous" experience, that's all I can tell you about it.

Re: Change Primary Key or rewrite?

Posted: Thu Jun 09, 2011 12:02 pm
by Jade
Manual to the rescue!! We should put a mask and cape on it: http://dev.mysql.com/doc/refman/5.5/en/ ... -keys.html

Re: Change Primary Key or rewrite?

Posted: Thu Jun 09, 2011 12:05 pm
by social_experiment
The SQL Manual wrote:A table can have only one PRIMARY KEY.
A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key
1keydata.com

Ok, I wonder if it is 1 field defined as primary key and other just also containing unique, not NULL values or lets say 2 fields declared as primary key, this however would contradict the sql manual :?:

Re: Change Primary Key or rewrite?

Posted: Thu Jun 09, 2011 12:08 pm
by Jade
I think this is a terminology issue. In MySQL if you create a "primary key" on more than one fields it stores it as a unique multi-column index. However MySQL is still treating that as a "primary key" to the table.

Re: Change Primary Key or rewrite?

Posted: Fri Jun 10, 2011 10:52 am
by John Cartwright
I think the proper per is a "composite primary key", aka. "multi-column index"

Re: Change Primary Key or rewrite?

Posted: Sat Jun 11, 2011 6:39 pm
by jarofgreen
You can't have more than one primary key.
You can have a multiple column primary key (both ID and Username), but that is not what you want, cos then this is valid data:

ID Username
1 Bob
2 Bob
2 Jeff

What you probably want is a Primary Key on ID and then a NOT NULL Username column that has a unique index on it.

That way your ID works as it did (with AUTO INCREMENT, if your using that), and you DB will enforce unique usernames for you, as well as having fast username lookups.