MySQL has AUTO_INCREMENT, Microsoft's Access and SQL server have their ways to do the same thing.
But such as InterBase, Firebird, they don't have the aut-_increment.
For a web site support database design, the database part is pretty simple, and I can manage to use SQL standards for almost all of the database works.
For the portable reasons, such as if switch the codes for MySQL database or InterBase database, I prefer not to using the AUTO_INCREMENT.
What is your opinion?
Thanks!
You will have to develop you own autoincrement function in your application (i.e. retrieving the max. ID, and increment it). Except if some Database engine does not support something like auto-incrementing (or whichever it is called), I don't see why you would not use existing functionality! Internal DB-functionality will for sure have a better performance than self-developed functions.
JJ Mouris
yes, use the built in function is ideal.
but some clients may give us hard time if they want to host on other platform later on. even on the contract, we can make sure it is clearly said that this is designed for certain platform, the clients have nothing to argue. but still if i can make sure my product is portable as much as it can, i would have less unhappy clients.
they are not tech oriented. we have face this kind situations before. even you explained everything in the very beginning, they don't understand what your are talking about then until the problems pops up when they try to switch the hosting platform.
but i guess, if i use mysql, i can be safe to say it is supported everywhere. if i use other database, then i have to worry if i should use their built in functions.
thanks.
If you change the database, you will have to change all the functions, anyway! Simply changing from msql to mysql, you will have to change your PHP-functions, say, from msql_query() to mysql_query(), and this is only one of them. Functions to get number of rows in reslt set etc. may differ from one DBMS to another.
Or, you need to write somne kind of "wrapper" functions, which use the DB-specific function. But this seems to be a quite weird idea to me!
JJ Mouris
It's a pretty lame db engine that does not support basic autoinc. If a client wants to use something that lame then they have to suffer the consequences.
You can get around the problem by writing your own autoinc system. Do NOT get seduced into relying on SELECT MAX(id), this will fail quite often and issue the same id twice. To replicate the native autoinc systems you create a table that holds the last id issued for each of your autoinc id fields. Simply look it up, add one, save and issue. This is how inbuilt autoincs work.
This will of course require tarnsactions and table locking to prevent concurrrent inserts from getting the same id. Problem is, any lame db that does not have autoinc probably does not have transactions either.
I use this approach for sequences like invoice nos that cannot have gaps, which normal autoincs will easily have. It allows me to rollback the increment if the main table insert fails etc.