also, let me state that i’m not disagreeing that an integer (surrogate) key can be useful
but keep in mind that you would also have a UNIQUE index on some other column(s) along with the surrogate key
so using a surrogate key is additional space in the row, right?
you would only do that if necessary, namely when (1) using it externally, like in a url http://example.com/showproduct.html?id=937, or (2) if the table has child tables, i.e. a foreign key references it
To be clear, I said that integer comparison are “a lot” faster than string comparisons and did not say that a JOIN with integers will be “a lot” faster than a JOIN with strings (which is what I think your asking me to show).
As for any source on JOINs, I don’t have any, and have no idea how to set up a fair test.
same problem – i object to the “a lot” part
Using CHAR keys for joins, how much is the overhead ? - MySQL Performance Blog
I don’t know about you, but for me “over 6 times slower” constitutes “a lot”
Well, even if it’s .00001 seconds difference per operation, over the span of a million operations, that’s still 1 second. =p
okay, rémon, i concede – it’s actually A LOT
Be cautious about “id” because it is significant to javascript and you could run into this gotcha.
<form id="myForm">
<input type="hidden" name="id" value="23">
</form>
The form id of “myForm” gets overwritten by “23” in this form in many browsers and not a few javascript frameworks. There are multiple ways around the problem on both view and database side, but it’s worth pitching this out there so you can be aware of it.
On the subject of natural versus artificial keys - it’s just too easy to get things wrong with natural keys. A key needs to be immutable and unique. About the only thing I commonly see that fits that criteria is social security numbers – but those often need to be encrypted which makes them a poor candidate for a key.
I didn’t read any of the post above but generally ID is better than specifying as a name. For example, let say a user has bookmarked
http://blah?productName=ABC
But, later that day… that productName was misspelled or changed for whatever reason then your client’s bookmarked link is no longer correct. Also, it enhances a bit in security side as well. If some nasty network admin decides to sniff HTTP request then he’ll see things like
then it may peak the interest of hackers to hijack the session. Just my 2 cents. In general, I always use “ID” and chances are you’re going to be “ID” column for some other tables anyways. So, why not make it as a standard?
I was with you up until this statement.
The reason you dont ‘make it a standard’ is the same reason you create a synthetic key in the first place - overhead.
I always create ID’s because everything else on that row is probably data and might change. The ID is not data and can remain consistent.
If you start using natural keys then changing the key becomes very heavy if you have references to that data elsewhere. Changing the primary key is likely to need transaction locking, and the ability to roll back if something goes wrong.
Any time you use transactions you run the risk of a deadlock?
If I can keep things simple I will. This might mean I have an extra integer column on my table - oh dear, how sad.
^ heh heh, yep.
BTW, if you don’t define an index, MySQL does it anyway but hides it. Internally the system MUST have a unique identifier for all rows.
BTW, id’s should be named according to the same convention throughout the database. Further, as foreign keys they should have the same name regardless of the table they occur on.
For instance, ‘id’ on table ‘user’. On any other table a foreign key reference to this should be named ‘userid’ or ‘user_id’. Be consistent with whichever name is used - don’t call the foreign key ‘userid’ on one table ‘user_id’ on another, and ‘usernumber’ on another.
I’m currently dealing with a database where the original programmers did crap like that. It’s a huge headache.
As DaveMaxwell says a URL like “http://www.sitename.co.uk/productname” would be best. If I use an ID this would be difficult if not impossible. Or is there a way to convert an ID within a URL into a product name using MOD Rewrite? I dont think it would be wise to use the ‘get’ function in the original URL to get both the ID and product name… To achieve the simple domain above I would need to pass the product name ONLY in the URL. Correct?
Matt.
according to petitio principii, that wouldn’t be possible unless there is a one-to-one mapping of id to product name
in which case you have no reason to use an id in the url in the first place, just go ahead and make the product name a unique key in the table, put it into the url, and bob’s your uncle
An issue with using ID numbers is also a problem with new entries. If I want to add a new entry into a table how easy is it to add it in between 2 current entries. Would the ID’s change automatically??
If it is not possible to add new entries between two current entries the table will look like a disorganised mess of products added at the bottom of the table as the new products get launched. What is the normal way to get around this problem when using ID’s?
Matt.
absolutely not – you’d have planes falling out of the sky if they did
it is, but you have to supply the id number yourself, and it can’t already exist
my advice is, don’t look at them and you’ll be fine
no, srsly
step number 1 is realizing that it is ~not~ a problem
When you query the database with your SELECT statement, you will add an ORDER parameter to it so that the records come back in the sequence you want and not the order they were entered in.
for instance, SELECT id,productname,price FROM products ORDER by productname ASC
look these up on w3schools.com if you are unfamiliar with this concept
The ordering would certainly make things easier. I will probably have a column that is numbered and then I can change the order by changing the numbers. Problem with this though is if there are 30 things in the navigation bar and a new product should list first then I will need to add 1 to all 30 so that the new number 1 lists first. This is the best method I can think of though??? And if the new product should appear in position 8 then the current 8 and above will need 1 adding to them, etc… a bit dodgy but it is the only way I can think of… any ideas about that??
Secondly, can you change the structure of a table in PHPMyAdmin? If I realise I want to add a new column between column 3 and 4, or I want to move the price column from column 8 to column 5 can this be done…or is the structure unchangeable from day 1 when it is first created??
Matt.
numbering – things will go easier if you number the rows in increments of 100, then you can “move” one entry in between two others easily, without renumbering them all
changes in phpmyadmin – yes, i believe these are possible, but i gave up phpmyadmin years ago in favour of heidisql, where most things are a lot easier
As R937 says, If you must have a sort column then if you are manually adding to the table then just leave gaps so that you can insert later. If programmatically adding then you will need to renumber all rows with a number higher than the row you are inserting.
Yes, most tools allow you to alter the design of the database. The order of the columns should be irrelevant to your code. You may need to go back and fix the existing rows which will now have an empty cell in the new column.
Matthew. You might find this course useful:
https://learnable.com/courses/php-mysql-web-development-for-beginners-13