IT is a very rare case when I have do disagree with your answer almost completely:

a) "is there anything wrong? There is something seriously wrong when the application tool dictates a data model."
It is true that it is wrong when the application tool dictates data model - but it is not a case, Hibernate does not do it, it nicely supports natural keys, including composite ones. Another story is a choice between surrogate and natural keys. It is one of the most discussed area in the database design area (and it was discussed here more then once). It is not so simple decision as it looks at first glance - the main problem with natural keys is that they are mutable (exceptions from this rule exist but they do not constitute majority). Even the keys that looks and are suppose to be absolutely immutable mutate earlier or later (e.g. individual tax numbers recently in Germany). So ask yourself what it would cost you to update the whole database with a new keys (or not the whole DB but sizable chunk of it).So it depends - as usually.

b) Statement is untrue, but I guess it is quoted incompletely - if is formulated as "surrogate key should be numeric" it would have sense.

c) Answer depends on "how composite" this composite key is (how many columns, which types and sizes of those columns).

d) Once more - it depends, if it is OLTP system, that such habit is a pure nonsense, if it is DWH - answer might differ (especially, because it is not clear what "heap table" means in this context, it may as well be materialized view.

Following are concerns on Db Model for Web Based OLTP Application
a)There will be aroud 1000 tables some of these are transcational and some are reference data. We need to keep detail logging of it. If a portion of record is changed by one user and other portion by other user then we should have change track by individual user. Please suggest which option we use for logging.
1. Put logging in text file.
2. Put loggin in proper history tables.

have a fear that storing this logging information in tables will reduce performance.

If you suggest loggin in history tables then should these be populated through triggers or stored procedures?


b)Where we store large objects including pictures/audio etc. In databae or file system.

c)We are planning to persist db tables through Hibernate in order to speed up development. What you suggest should we do it or use stored procedures?

Tom:

wow, just curious have you seen a 1000 table schema before?

everyone talks about this java hibernate framework. Does this really do something differnt than something simple like apex or pl/sql procedures.

I think IT managers hear of Struts/Hibernate/JSF and say that is the latest cool thing and that is what we should use. No wonder 50% of IT projects end up with total failure

Java frameworks are just too complex and overkill.

do you agree?

sys%ORA10GR2> select count(*) from user_tables;

  COUNT(*)
----------
       712

"everyone talks about this java hibernate framework. Does this really do something differnt than
something simple like apex or pl/sql procedures."

Having developed in both imperative and functional languages, I have never used Hibernate, and have never had a reason to believe that I should consider using it. Similarly, I have never used LINQ, and believe it too, like Hibernate, offers no real enhancements. This view is based upon my experience as a database developer - using both primarily disk-based and in-memory databases - within various development teams in the financial services sector. The largest part of my role involves implementing in one of aforementioned types of databases, financial algorithms that draw upon several sets of data, and over which are applied a set of calculations.

Given the experience of my team and I in several distinct programming environments, including the use of SQL and procedural languages and frameworks (Java, .NET, Hibernate), we all strongly agreed at the outset that the development of the majority of the algorithms would be done in SQL. There are, of course, exceptions, and in such cases we do develop in an imperative language, such as Java.

What have we realised to be a key capability of SQL, which does not exist in imperative languages, in relation to our development requirements?

First, SQL offers a very powerful model of abstraction. Having once developed in assembly language, I certainly appreciate the level of abstraction offered by SQL. As an example, the ability to perform complex analytical functions over large volumes of data using only a few lines of SQL code is a fundamental capability and advantage of the SQL language. I would be very interested to see somebody write an equivalent function in an imperative language in the same of less amount of time.

Second, and perhaps most importantly, is the concept of abstraction with respect to system integration. This is itself an advantage that is difficult to match with imperative languages. Suppose we have a calculation procedure that involves, say, seven stored procedures, and which draws upon a set of no fewer than, say, 20 tables. Further, suppose the research team would like to implement a change to one or more of the calculations that are performed by this process. Using the configuration that I have just described, we would amend one or more of the stored procedures to incorporate the requested changes. There is no other aspect of the system that would be subject to a technical amendment. Our set of applications would remain unchanged.

"I think IT managers hear of Struts/Hibernate/JSF and say that is the latest cool thing and that is
what we should use. No wonder 50% of IT projects end up with total failure"

This is a view of which I am unfamiliar. When I first joined my firm, the manager of my team informed me that I would be asked to develop the solution using the approach that I considered most appropriate based on my experience in, and knowledge of database development and data analysis. The manager does not understand the intrinsic detail of the database, nor does he understand the mechanics of the solutions that we have developed in C# - to problems to which SQL would be considered wholly inappropriate. Of course, the manager could understand the internal workings of our system. But he chooses not to. And this is the right thing to do. Rather, he hires people who have proven experience and knowledge in development. It is owing to this approach that we can attribute our success as a team.

"Java frameworks are just too complex and overkill."

They are certainly not complex. I would say that five lines of SQL code can be considered to be more complex than several entire Java frameworks. Why? Because the former will often represent an innovate solution to a problem, and will often utilise the power of abstraction. It is worthwhile to remember that solving a problem using only a few lines of code is significantly more complex than to write a solution of a couple of pages.

Analyst from Australia

I totally lost you in your last paragraph.

First you say you try to use SQL insted of procedural code like JAVA. Then at the end, you say SQL is too complex and wiriting 2 lines of code does not mean it is easier to read/maintain than writing 2 pages of code.

How could this be true?

To me the less code, the better the program especially if it runs faster and both give the same result.

I also thought that JAVA, .NET, etc all use SQL functions.
It is only after you get the data using SQL what do you use: PL/SQL, C#, java, etc..

It would be really stupid for someone to write a function in JAVA like a SELECT, MAX, MIN, etc in SQL. Why reinvent the wheel and create proprietary software. SQL is a STANDARD.

The main problem with application developers is that they do not know to write SQL very well. You are not going to find that many Tom Kytes out there.

I should apologise for the fact that my last paragraph is not as clear as it should have been.

"First you say you try to use SQL insted of procedural code like JAVA. Then at the end, you say SQL
is too complex and wiriting 2 lines of code does not mean it is easier to read/maintain than
writing 2 pages of code."

What I meant to say was that often to solve a problem with only a few lines of SQL will often be more complex to design and develop conceptually. However, once implemented, it will appear as very simple and easy to understand. That was the key point I was trying to make.

Indeed, it is very easy to write pages of procedural code to solve a complex problem. However, do solve it using only a few lines of SQL code, will be more complex, but will deliver an easier to understand solution, and which will, most likely, perform better than the procedural solution.

And while procedural code is not difficult to understand (remember: it is only loops, at the end of the day), I do find it more time consuming to analyse given the way in which solutions are often developed in procedural languages. On the other hand, solutions developed in SQL tend to reflect very closely the original requirement.

"To me the less code, the better the program especially if it runs faster and both give the same
result."

I completely agree.

"I also thought that JAVA, .NET, etc all use SQL functions.
It is only after you get the data using SQL what do you use: PL/SQL, C#, java, etc.."

There are no SQL functions in procedural languages (JAVA, .NET). Programs written in these languages may invoke SQL functions that exist within a database. For example, JDBC offers this capability in Java.

"It would be really stupid for someone to write a function in JAVA like a SELECT, MAX, MIN, etc in
SQL. Why reinvent the wheel and create proprietary software. SQL is a STANDARD."

I do not understand what you meant to say in this paragraph.

"The main problem with application developers is that they do not know to write SQL very well. You
are not going to find that many Tom Kytes out there."

There is an even bigger problem. That is there are many developers who believe that the data, and data processing, is secondary to the application. This is the single biggest misunderstanding. I am currently developing yet another application (a small web-based application) that will provide access to a small subset of the functions that exist in the database, and which had been developed earlier this year.

I have been involved in several projects where all the data access was done through JPA, specifically we utilized EclipseLink as the JPA Provider implementation. However the key difference is that we created the Database first, we spent quite a bit of time working on the ERD to make sure the Database was clean and as normalized as possible, with all foreign keys. Then we used tools to generate the Java classes based on the database tables. I do a lot in both SQL and Apps (people can do both if you put the effort in to learning the proper techniques in each environment). I have found that for most apps using and ORM's as data access layers against properly designed databases dramatically reduces the amount of code, can perform very very well, and makes maintenance easier. As with almost anything in software proper implementation and use of technology is what makes you successful.

Tom:

<<<No, I think there are problems of varying degrees. And I think everything is just a tool. And you need a toolbox of various tools and the knowledge of when and how to use each one in order to call yourself a software engineer, a programmer, a coder, whatever. >>>

Let us face it. How many coders or engineers know JAVA, PL/SQL, SQL, Pythoon, Perl, Ruby, .NET, C, C#, C++ etc.
to be able to know/determine what is the best to use.

Most people struggle with one language and do not even know what it offers.

Now, many tools accomplish one purpose. For example if you want to develop web application anything will work: PL/sql, Java, Coldfusion, Perl, ASP, JSP, PSP, PHP, etc....

It just a matter of what the prorammer knows and what tools exist already in place.

This IT industry needs to be regulated and many people be flushed out. Too many incompetent people in it from so called techies to managers. One manager once suggested to a customer to migrate from oracle enterprise to flat EXCEL files (because he can sort data in EXCEL easily). Imagine a 50 giga database with 500 tables and 1000s users running with Excel flat files these days!!