Change Primary Key or rewrite?
Moderator: General Moderators
Change Primary Key or rewrite?
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
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
- flying_circus
- Forum Regular
- Posts: 732
- Joined: Wed Mar 05, 2008 10:23 pm
- Location: Sunriver, OR
Re: Change Primary Key or rewrite?
Mike,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?
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?
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
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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Change Primary Key or rewrite?
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.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.
“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
- flying_circus
- Forum Regular
- Posts: 732
- Joined: Wed Mar 05, 2008 10:23 pm
- Location: Sunriver, OR
Re: Change Primary Key or rewrite?
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.social_experiment wrote:Two primary keys would mean two tables.
Re: Change Primary Key or rewrite?
Manual to the rescue!! We should put a mask and cape on it: http://dev.mysql.com/doc/refman/5.5/en/ ... -keys.html
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Change Primary Key or rewrite?
The SQL Manual wrote:A table can have only one PRIMARY KEY.
1keydata.comA 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
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
Re: Change Primary Key or rewrite?
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Change Primary Key or rewrite?
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?
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.
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.