I also don't understand why a single SP would be the 'easiest' way for anything that has to deal with 'multiple tables'. That SP has to obtain the source data for the insert/update from somewhere and that 'somewhere' would seldom be a source that has data for multiple tables. If it was that would suggest a very flawed data model.

Forgetting DAOs for the moment it is rarely good practice to encapsulate multiple operations (e.g. DML on multiple tables) in a single SP anyway. Standard practice would be an SP for each data source/target. There might be a 'control' SP that controls the process of executing the datasource SPs in the proper order. So, again, I'm not sure why you would have one SP for multiple tables especially if the data is being passed into/out of that SP.

Architecturally speaking each table would have its own DML process tailored to its own unique data projection. For normalized data multiple tables would NOT be used for data relating to the SAME entity at the same level. Multiple tables for related data would typically be hierarchical (e.g. Parent/Child).

For a Parent/Child set of data you would have one DAO for the parent and another DAO set for the child data. The DML for those DAOs would either be handled individually or both sets passed to the SP so it could iterate through the parent/child data to deal with surrogate key generation or issues like that.

For a Parent/Child set of data you would have one DAO for the parent and another DAO set for the child data. The DML for those DAOs would either be handled individually or both sets passed to the SP so it could iterate through the parent/child data to deal with surrogate key generation or issues like that.

To illustrate an example .. Lets say a Person{pk_person, fname, lname, fk_address} table and an Address{pk_addr, Street,city, zip} table. Now they are not quite parent/child, they are to separate and independent entities altogether, related by the FK_ADDRESS.

Now one way to insert a data set would be to Insert to the Address table and use the pk_addr in the Person table, where both pk_person & pk_addr can be a Sequence from the DB.

In that case it would be simpler to pass the fname, lname, Street,city, zip from the Business Component to a DAO - calling an SP.

Now what I meant to ask was - is that the right approach OR is it recommended to have the Business Component call the PersonDAO and an AddresDAO seperately (and I've no clue if that can be made to work).

To illustrate an example .. Lets say a Person{pk_person, fname, lname, fk_address} table and an Address{pk_addr, Street,city, zip} table. Now they are not quite parent/child, they are to separate and independent entities altogether, related by the FK_ADDRESS.

Now one way to insert a data set would be to Insert to the Address table and use the pk_addr in the Person table, where both pk_person & pk_addr can be a Sequence from the DB.

In that case it would be simpler to pass the fname, lname, Street,city, zip from the Business Component to a DAO - calling an SP.

Now what I meant to ask was - is that the right approach OR is it recommended to have the Business Component call the PersonDAO and an AddresDAO seperately (and I've no clue if that can be made to work).

I don't consider that 'simpler' at all. It also isn't scaleable or object-oriented.

There are two 'standard' approaches used depending on how the keys are generated. Both of those use separate DAOs.

One method generates the keys using methods in the persistence layer; that might call a sequence generator.

The Address DAO would be used to create an address row. If the keys are generated by a method in the persistence layer that method is called to obtain a new key, the key used to update the DAO and then the new address row created.

That same key is then used to update the personDAO and then that DAO creates a new person row.

In the other method a DB trigger generates the key on INSERT. So the address DAO creates a new address row and the new key is returned (e.g. Oracle procedure does an insert with a RETURNING clause). That new key is used to update the addressDAO and also the personDAO. Then the personDAO can create it's new row.

Person addresses can be easily changed; all you need is the new address key you want to use. That key updates the personDAO value and then an update is performed.

Many frameworks (hibernate, toplink) can handle all of that easily because when you define the data model all of those foreign key relationships are specified as is the key generation method to use.

Which is 'best'? There is no 'best'. And both of those techniques, although commonly used, are severely non-performant for bulk loading.

It all hinges on how the source data is obtained to begin with. That is, if there are two data files one for person and one for address how is the 'address' for a person determined for that file data? A person might even have multiple addresses (e.g. work, home, vacation).

So the key for NEW data is where that data comes from, what format it is in and how those links are encoded in the actual source data. For one-at-a-time data entry GUIs it it rather trivial. It is that bulk stuff that can get complicated.

But I have never seen a stored procedure such as what you suggest used. That is not scaleable and also non-performant.

And even passing custom Oracle datatypes involves packing/unpacking the data into those types when simple DML can get the job done much quicker.

There is always a trade-off between generality and performance. Small volume processing such as between a GUI and the backend works fine using all of those layers and DAOs. High volume performs very poorly. You generally need a mix to handle the different major use cases.

Hopefully you noticed the sections in that article that talked about the different types of persistence: CMP, BMP, etc.

Typically, applications use shared distributed components such as entity beans to represent persistent data. An application is considered to employ bean-managed persistence (BMP) for its entity beans when these entity beans explicitly access the persistent storage-the entity bean includes code to directly access the persistent storage. An application with simpler requirements may forego using entity beans and instead use session beans or servlets to directly access the persistent storage to retrieve and modify the data. Or, the application could use entity beans with container-managed persistence, and thus let the container handle the transaction and persistent details.

. . .

Not Useful for Container-Managed PersistenceBecause the EJB container manages entity beans with container-managed persistence (CMP), the container automatically services all persistent storage access. Applications using container-managed entity beans do not need a DAO layer, since the application server transparently provides this functionality. However, DAOs are still useful when a combination of CMP (for entity beans) and BMP (for session beans, servlets) is required.