Re: Databases Part 1

Finally responding on this great post. Waiting on part deux.

A table can have a primary key.

When you say "can" dyou mean a primary key is NOT necessary? I thought it would be.

Things get a bit messier in real life scenarios. Consider Age was one of the fields in the Employee table. Now Age could be modelled as an Integer data-type. An Integer can be negative, but Age can definitely not be. So such simple rules are enforced by using a CHECK's. The definition of the Table is called a Schema. This Schema is mentioned using the CREATE statement. PK's and FK's are mentioned along with the Field names and this is where CHECK's can be applied too. I'll not get into the syntax here. A simple line like CHECK ([AGE>0]) will enforce the integrity of the Age field.

One thing that needs to be considered is where the error checking happen? Does it happen in the "business logic" layer or directly in the data layer? I would think a lot of times the database should ONLY be conserned with storing information. All the validation should happen elsewhere. Wouldn't it be more convenient that way?

Why more convenient? Because your business logic layer interacts with both presentation and data layers. If something doesn't get validated it's easy to present the error to the user. I'm not sure how this would be handled with the database. What happens when the CHECK([AGE] > 0) fails?

I guess the question comes down to what responsibilities the database should take on. It seems the trend is to make more and more things happen in the data layer. The next version of SQL Server is being integrated with .NET. So you can apparently write stored procedures in the .NET languages rather than SQL. I believe Oracle has had this capability with Java for a while. Also, another big trend is SOA - Service Oriented Architecture. The idea is to make each layer completely separate with each providing services. You call those services instead of directly making SQL calls. I suppose these will be Web Services, so you can invite others to call them - not just your app.

Each trigger is associated with a TABLE on some event.

This is quite cool. I didn't know this was possible. You can basically keep a log/trace of everything that's going on in your database this way.

I'll post later on Stored Procedures, Prepared Statments and simple Queries. If you guys want some other info, I'll try. Was this blog too complex/easy?

I'm interested in Stored Procedures just because I've read that they are beneficial to app performance. Is that really true?

What db are you working with? I've had basic experience with Access and SQL Server.