Extensible Database Structure

Here's the thesis:
We want to be able to construct an extensible database structure such that we are able to add fields(content) on the fly using data deploy. There should not be the need to alter the table schemas.

USUAL
Lets take scenario A. We have a DCR with 10 fields. We create a database table with fields corresponding to the record items.
Limitations - were we to add new fields to the templates, we would be stumped.

THESIS
Use a Hashmap structure in the database to store all database items as key value pairs. Actually it would consist of more than 'keys' and 'values'. At this time, I feel it should be field name, field value, record name, environment(multiple servers sharing the same schema).

Thoughts? Inputs? Suggestions?

We plan on using SQL Server 2000. On OD 6.1.1. TS 6.7.1

Find more posts tagged with

Comments

Adam Stoller

Here's the thesis:
We want to be able to construct an extensible database structure such that we are able to add fields(content) on the fly using data deploy. There should not be the need to alter the table schemas.

USUAL
Lets take scenario A. We have a DCR with 10 fields. We create a database table with fields corresponding to the record items.
Limitations - were we to add new fields to the templates, we would be stumped.

THESIS
Use a Hashmap structure in the database to store all database items as key value pairs. Actually it would consist of more than 'keys' and 'values'. At this time, I feel it should be field name, field value, record name, environment(multiple servers sharing the same schema).

Thoughts? Inputs? Suggestions?

We plan on using SQL Server 2000. On OD 6.1.1. TS 6.7.1

The first question is whether the DB supports modification to the schema without requiring that the entire table be dropped and re-created.

Assuming that is possible - I'd probably opt for using Perl DBI (or the Java equivalent if you're so inclined) rather than DD - since DD doesn't have the flexibility you're looking for and my guess is that it would be a non-trivial change to the code so you'd be lucky to see it changed before next year (if that).

RajatChopra

The first question is whether the DB supports modification to the schema without requiring that the entire table be dropped and re-created.

Assuming that is possible - I'd probably opt for using Perl DBI (or the Java equivalent if you're so inclined) rather than DD - since DD doesn't have the flexibility you're looking for and my guess is that it would be a non-trivial change to the code so you'd be lucky to see it changed before next year (if that).

But is it necessary for a solution to have to re-create the tables? The thinking behind the thesis is to utilize a HASH MAP/MATRIX structure to store all relevant data.

Have a table IWOV_REOCRDS with the fields:
1. ItemName (nchar)/varchar
2. ItemValue (nchar)/varchar
3. DCRName
4. DCRType
5. PATH (DD Variable) for deletions

All DCR values are inserted into such a structure using DD scripts. That way we do not customize DD per se.

Will a 'select' statement from a JSP/Java program incur a huge CPU Cycle? The number of records will be in millions, but isn't that what happens in Insurance/Banking sectors anyway? We could index the records and have keys in place to improve performance.

This sort of structure gives me the flexibility to add/delete/modify fields at will, without having to re-create the schemas/tables.

Adam Stoller

But is it necessary for a solution to have to re-create the tables? The thinking behind the thesis is to utilize a HASH MAP/MATRIX structure to store all relevant data.

Have a table IWOV_REOCRDS with the fields:
1. ItemName (nchar)/varchar
2. ItemValue (nchar)/varchar
3. DCRName
4. DCRType
5. PATH (DD Variable) for deletions

All DCR values are inserted into such a structure using DD scripts. That way we do not customize DD per se.

Will a 'select' statement from a JSP/Java program incur a huge CPU Cycle? The number of records will be in millions, but isn't that what happens in Insurance/Banking sectors anyway? We could index the records and have keys in place to improve performance.

This sort of structure gives me the flexibility to add/delete/modify fields at will, without having to re-create the schemas/tables.

Again - what does the DB actually support?

DD used to support a "narrow-tuple" format table that was extensible because it had (if I recall) only 4 columns: path, status, key, value

The main problem with this is the potential for reaching some limitation on the number of rows you can insert into the table.

For a "wide-tuple" format table - you have separate columns for each key (into which the value is stored).

To repeat myself: if you're using a particular DB - you have to find out if the DB supports modification of the schema (for a wide-tuple format) without requiring the dropping and re-creation of the table. If it does, you will probably be able to utilize it via your own code more easily and quickly than you will through DD.

If you're looking to design and develop your own DB - that's a whole other ball game - but it didn't sound like that was what you wanted to do.

RajatChopra

Thanks a lot for your insights.

Again - what does the DB actually support?

DD used to support a "narrow-tuple" format table that was extensible because it had (if I recall) only 4 columns: path, status, key, value

The main problem with this is the potential for reaching some limitation on the number of rows you can insert into the table.

Have you come across this problem somewhere? Do you know a limit for SQL Server or Oracle DB? Can you point to any RTFM, Support Cases, Techlib Articles?

For a "wide-tuple" format table - you have separate columns for each key (into which the value is stored).

To repeat myself: if you're using a particular DB - you have to find out if the DB supports modification of the schema (for a wide-tuple format) without requiring the dropping and re-creation of the table. If it does, you will probably be able to utilize it via your own code more easily and quickly than you will through DD.

Probably I am unable to explain this concept. Or I am unaware of how updates to DB's are achieved. In either case, I will try to again explain my logic.

...you have to find out if the DB supports modification of the schema (for a wide-tuple format) without requiring the dropping and re-creation of the table....

Would this be required if all I am doing is
1. Insertions of rows not columns
2. Updates to existing rows
3. Deletes on existing rows

The narrow-tuple seems to be the sort of solution I am trying to envisage for the implementation, but probably expanding on that to include more columns. Once the structure of the tables is finalized, i.e. columns have been decided, we will not add any more columns to the schema. The records will be individual rows in that schema at a per DCR level.

DD will only deploy the records as rows to the schema and delete them from the schema, for which I accept including the 'path' variable in to the schema structure.

I do not hope for DD to re-create the table ever. All it does is push records. No columns on the table should change once the structure is finalized.

If you're looking to design and develop your own DB - that's a whole other ball game - but it didn't sound like that was what you wanted to do.

Nopes. You are correct in assuming that I wish to do nothing of this sort.

Hopefully I am able to get the concept across. Thanks again for your inputs on this.

Amlan

Rajat,

Just a suggestion,

I think you have a set of fields that will be more or less constant. Keep them on one table and add any new fields (on the fly) on a second table that will be connected to the first table through a foreign key.

Now,

In the second table, keep the columns something like this

Foreign Key --- Prop-ID Field-Name(on the dcr) value

I think this will allow a simpler approach to the design

Regards,
Amlan

RajatChopra

Rajat,

Just a suggestion,

I think you have a set of fields that will be more or less constant. Keep them on one table and add any new fields (on the fly) on a second table that will be connected to the first table through a foreign key.

Now,

In the second table, keep the columns something like this

Foreign Key --- Prop-ID Field-Name(on the dcr) value

I think this will allow a simpler approach to the design

Regards,
Amlan

Thanks for your response Amlan. Just need a few clarifications on your approach:
1. What field should be the foreign key? The Prop-ID? What is that? Just an identifier to connect the 2 tables?
2. Add on the fly? I really hope I can get by without adding fields (table columns) on the fly. All I want to add are rows into existing tables...
3. How would the foreign key implementation affect performance? We are talking about records in thousands.
4. DD is notorious with FKEY implementations and it hasn't been the happiest of development grounds for me.

I am attaching herein a structure similar to what I am trying to achieve. This is the manner in which the table would keep on progressing. Once the fields are finalized, I don't see us adding any more columns, only rows of data.

Adam Stoller

I am attaching herein a structure similar to what I am trying to achieve. This is the manner in which the table would keep on progressing. Once the fields are finalized, I don't see us adding any more columns, only rows of data.

The image you attached shows something of a cross between DD's wide-tuple and narrow-tuple formats - thus it falls in the category of UDS (user-defined-schema) for DD - and I believe DD only supports wide-tuple formats for that - so you would have trouble getting DD to do what you want.

Using Perl DBI - the schema you showed should be fairly simple, because you can take care of the looping through the fields and issuing the SQL calls to insert the values you want into the columns you want.