Originally Posted by VirtuosiMedia

MySQL handles it by allowing an index to be auto incremented, but I know that's not the way everyone else does it. What I think I'm going to have to do is build the database tables from a general map that includes identity columns, primary and foreign keys, uniques, and data types and then write my program so that it handles each in a database specific way.

I'm not sure what your general map is, but it sounds complicated? Sort of like a site map file, but for a db? If that's the case, and necessary information, you'd be wise to figure out how your system can generate them on its own. In SQL Server, this would help for any particular table

Select Name, User_Type_ID, Max_Length, Precision, Scale, Collation_Name, Is_Nullable, Is_Identity, Is_Computed From sys.Columns Where Object_ID = Object_ID(@TableName) Order By Column_ID

You can enumerate sys.Tables to get a list of all of them.

Originally Posted by VirtuosiMedia

PHP has a function that will return the last insert id, but it's not consistent across databases, so I might have to introduce sprocs for some of the DBMS's that don't support it. Is it possible to write a single sproc that would handle getting the last insert id for all tables? Ideally, I'd like to have my insert method to return the last insert id automatically.

A stored procedure can do virtually anything. I'm not sure how it would do that (it needs a knowledge of the db schema), but if you can work that out, multiple result sets is no problem. Some issues people commonly face are that if they use @@Identity instead of Scope_Identity(), if there's a trigger on the table they try to insert to, they might get the identity value for an insert in the trigger, rather than in their code. (This could be an issue with db error logging!) Another is that if an insert failed, or was rolled back, null is typically what people would expect, rather than a non existent value.

Always returning the insert ID value is a good idea. You might run into problems, tho, if people have funny schemas? What if somebody has a smallint, seeded at -32,767, counting to 0, then up to 32,768? Or if they use a GUID instead of an int of any type? There's also the potential case of a compound key, although the bunch of identity functions don't solve that one. If you can use a variant data type (System.Object in .NET) that could let you pass either an int or a GUID string? The issue with negative numbers is that a lot of logic tier programmers give those special meanings, assuming they're invalid from an RDBMS perspective, which often isn't true. (.NET DataSet objects are a good example of this problem.)

Originally Posted by VirtuosiMedia

I'm going to be looking at transactions very soon here as well. I'm not totally sure how I'm going to handle them yet, but I'm fairly certain that PHP a function that will allow me to rollback a transaction.

All data access happens within a transaction. The server will create one implicitly if it needs to. You can control one through pure SQL code. Or, through .NET or PHP code. Under ADO.NET, transactions get their own object, but are more or less exposed at the connection level. I prefer them in SQL code. It sounds trite, but best performance and scalability often come from saving 1 % here, and 1 % there, until they add up to something substantial. Why hold (DB) locks for the extra time it takes to send and compile commands between different application layers?

Originally Posted by VirtuosiMedia

I'm working on the error handling right now. What would you suggest for it? I'm not sure what will be the most useful. I'm thinking of having several different modes of error handling: silent, debug (which will print the errors), and db (which will log the errors into the db), with silent being the default.

How errors are dealt with is a subject that tends to spark Holy Wars. SQL 2005 lets you use the old method of querying an @@Error system global variable, or to use Try and Catch blocks of code. Some people say things like it's not possible to write predictable code with exceptions. I'd recommend seeing what your customers will want, or building in different models, from the start.

Originally Posted by VirtuosiMedia

You mentioned earlier in the thread about good client statistics. Could you elaborate a little on what you mean?

I don't exactly remember, but I must have been talking about either optimizing the data flow between the client and server, or about on the server directly. In the SQL Server client tools, you can be shown both.

I'm not sure what your general map is, but it sounds complicated? Sort of like a site map file, but for a db? If that's the case, and necessary information, you'd be wise to figure out how your system can generate them on its own.

It would be more of a table map rather than a dynamic database map. I would use it more for table creation than anything else, I think. If I were to do an XML implementation of it, a very basic example would look something like:

<table>
    <name>Users</name>
    <description>Stores general user data</description>
    <schema></schema>
    <columns>
          <column>
                <columnName>userId</columnName>
                <datatype>int</datatype>
                <default></default>
          </column>
          <column>
                <columnName>userName</columnName>
                <datatype>varchar(20)</datatype>
                <default></default>
          </column>
          <identity>userId</identity>
          <primary>userId</primary>
          <foreigns>
                 <foreign>userName</foreign>
          </foreigns>
          <uniques>
                 <unique>userName</unique>
          </uniques>
    </columns>
</table>

My script would parse the XML and then compile the create table SQL according to which DB is being used.

Always returning the insert ID value is a good idea. You might run into problems, tho, if people have funny schemas? What if somebody has a smallint, seeded at -32,767, counting to 0, then up to 32,768? Or if they use a GUID instead of an int of any type? There's also the potential case of a compound key, although the bunch of identity functions don't solve that one. If you can use a variant data type (System.Object in .NET) that could let you pass either an int or a GUID string? The issue with negative numbers is that a lot of logic tier programmers give those special meanings, assuming they're invalid from an RDBMS perspective, which often isn't true. (.NET DataSet objects are a good example of this problem.)

Good points. I'd forgotten about the whole negative numbers thing.

All data access happens within a transaction. The server will create one implicitly if it needs to. You can control one through pure SQL code. Or, through .NET or PHP code. Under ADO.NET, transactions get their own object, but are more or less exposed at the connection level. I prefer them in SQL code. It sounds trite, but best performance and scalability often come from saving 1 % here, and 1 % there, until they add up to something substantial. Why hold (DB) locks for the extra time it takes to send and compile commands between different application layers?

More good points. I'm going to have to look at how the different DBs handle this to see if it's feasible to pull it out of the server code and still have it work portably.

How errors are dealt with is a subject that tends to spark Holy Wars. SQL 2005 lets you use the old method of querying an @@Error system global variable, or to use Try and Catch blocks of code. Some people say things like it's not possible to write predictable code with exceptions. I'd recommend seeing what your customers will want, or building in different models, from the start.

I'll build in a few different models from the start, I think, as I'm not sure what they will want.

The XML you've shown might not be complete. Are you familiar with compound keys? They're far too important not to support.

Thanks for that. I wasn't familiar with them, but would this solve that problem?

 <primaries>
     <primary>userId</primary>
     <primary>userName</primary>
</primaries>

Something else that I didn't include was any partitioning options.

That would solve it. So would <primary>UserId, userName</primary> which is why I said might. I wasn't sure if it was just that particular example, or something I ought to bring up.

Partitioning can be a wonderful thing, but then the word can refer to at least 2 database engine concepts I'm aware of. One is ranking and partitioning functions, which are also a good thing. But the other is when you have a gianormous table, and need to split it out into lots of smaller tables. You make a partition function, maybe by date, maybe by whatever your primary key is, that says "this row belongs in this table". That lets the SQL engine know that when you insert to the unified table, any given row needs to be redirected to wherever it belongs. And what that allows, is for you to run a query (the "search" type - one that finds a subset of the rows in the table, rather than summing all of them) that the database can satisfy without looking at so many rows, thereby performing less disc input.

If that's the type you're talking about, tho, Microsoft recommends that this be done when a table grows beyond 100 GB!!! I know, M$ recommendations aren't worth much to most people. Most programmers think Microsoft recs begin with "Hey, lemmings!" and have a map to the nearest cliff. But keep this in mind, SQL Server has been called (many times) Microsoft's best software product. It's in their interest for people to do well with it. In this case, I think their advice is worth considering, even for MySQL.

The reasons are twofold. One is that indexes are pretty efficient, so it takes a lot of data before, even with their help, you're still in a world of hurt. Also, partitioning a table tends to freak people out, and make for a little more overhead to manage. In some research (which might be outdated at this point?), they found 100 GB to be the break even point where the perf gains outweigh the headache that comes with them.

So, even if I can't believe I'm saying it, if that's the type of partitioning you're considering, I think you could leave that out, and still be fine.

Originally Posted by VirtuosiMedia

I'm working on the error handling right now. What would you suggest for it? I'm not sure what will be the most useful. I'm thinking of having several different modes of error handling: silent, debug (which will print the errors), and db (which will log the errors into the db), with silent being the default.

Swallow exceptions, and use return values to indicate success or failure. People don't want the world to end while they fix something, unless it's absolutely necessary. It sounds like you're making a database connectivity layer; it's probably safe to cache errors and warnings in an array in memory.

I've seen error log tables in the database grow to hundreds of thousands of rows...

Thanks Forrest. I think I'll have the errors handled silently by default, with the option of changing it later.

I'm looking at views currently. Do either of you use them very much? They seem similar to stored procedures, but I'm not sure what the differences are exactly.

Views are vastly different from stored procedures. The similarity is that both are SQL code stored in the database, but it ends there. I guess you could use sprocs in place of views, sometimes, but the semantics are so different. This brings up a point, tho. You'll need to support the no expand query hint for indexed views.

A view is a virtual or derived table. You select from them. You can do this for security reasons, to hide a column, or to hide your schema. A lot of people use them to simplify db usage, and to help avoid deadlocks.

A stored procedure is a batch (can be multi statement) of SQL code that performs an action. That action can be reading data, but it doesn't have to be. It can take input parameters, and give return values, even output parameters. A view does none of this, because it isn't "executed", at least in anything like the same way.

The S in SQL means structured, which in turn means recursive. So this is entirely possible

Select * From (Select * From myTable Join yourTable On xxx)

That's essentially what a view is. Like a proc, the code definition is stored in the database, and you can refer to it by name instead of by retyping all the same code in many places. They're very widely used because this simplifies change management.

Somebody once asked me why we don't build all our systems with table valued functions instead of views.

Thanks for the explanation, John, that cleared up the difference for me. I don't think I'll be writing that blog article that I was talking about earlier because I found a good resource that compares a lot of different basic database syntaxes.