When inserting a complex object into an SQL database, when should the object be broken up into its respectful tables?

Question

Edit: In short what strategy should one use on insert and select scripts with complex objects (eg. two select calls, one for each table; a single select call with unions)?

We have a database insert (postgresql) that includes a list of objects that is serialized (in text xml), and put it into a cell in a row amongst normal strings and such. We would like to create a new table with those lists with references back to the key of the original item. Where should the object be split off? I don't think it is possible in the SQL query, but if so that would be ideal. Our favorite spot currently is just before we set up our JDBCProcedures.

string name
int id
List<sub-objects>

and currently this is being stored in a DB schema like:

name varchar(20)
id int
subObjs text [or other character type big enough to hold the serialized XML]

Answer 1

Please provide a little more information about the structure of your objects and clarify your question. It's not entirely clear what you're asking here.

That said, let me try to take a stab: If you have objects in Java code with structure somewhat like this:

string name
int id
object[] list_of_sub-objects

and currently this is being stored in a DB schema like:

name varchar(20)
id int
subObjs text [or other character type big enough to hold the serialized XML]

Is that about right?

And then your question is:

We would like to create a new table with those lists with references back to the key of the original item. Where should the object be split off? I don't think it is possible in the SQL query, but if so that would be ideal.

When you say the list-attribute item is "serialized" in your existing system, do you mean as XML? It looks like XML parsing in SQL itself is still in development for postgreSQL, and in any case it's likely to be a lot of trouble to code something like that up if you do not already know how.

But you already have application code which represents your objects in a non-serialized fashion. You could write a function in your application codebase which performs the migration. Load the records from the old database table into application objects according to your existing schema, then write them back into your new pair of DB tables according to your new schema.

This conceptually simplifies the problem down to something you can represent in pseudocode, i.e. "how do I map the structure of my object from the old database schema to the new one?"

I hope this helps! If you can clarify your structure a bit, I might be able to contribute some more specific pseudocode for the solution I'm proposing here.

Answer 2

We ended up splitting the insert into two calls (one for main object, one for sub-object) so that each table would have its own insert, but created a single select so that we could use the advantages of foreign keys in the query.