Change Primary Key or rewrite?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mikes1471
Forum Commoner
Posts: 88
Joined: Sat Jan 24, 2009 3:29 pm

Change Primary Key or rewrite?

Post 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
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Change Primary Key or rewrite?

Post 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).
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Change Primary Key or rewrite?

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Change Primary Key or rewrite?

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Change Primary Key or rewrite?

Post 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.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Change Primary Key or rewrite?

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Change Primary Key or rewrite?

Post 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 :?:
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Change Primary Key or rewrite?

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Change Primary Key or rewrite?

Post by John Cartwright »

I think the proper per is a "composite primary key", aka. "multi-column index"
jarofgreen
Forum Commoner
Posts: 71
Joined: Sun Jul 11, 2010 12:40 pm

Re: Change Primary Key or rewrite?

Post 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.
Post Reply