Flexible Database Design

Greetings. I'm working on designing a database that provides a great deal of
flexibility in that it stores calls to .NET classes and SQL procedures. Was
curious if any of you had any ideas or experience you'd like to share.

The system has to be able to handle a number of different types of jobs.
Each job could have a number of different processes which may include
printing reports, sending emails, updating tables, etc. I want to be able to
have it so that each time a new job with a completely different set of
processes is required, new deployments of the system aren't required (or if
they are they are minor), only updates to database are needed. Rather, some
sort of template would be created that would allow administrators to
configure the jobs and their processes and details behind the jobs would be
stored in the database architecture.

What I'm thinking is having a series of tables that may look something like
this. One is a template table that identifies the high level parts related
to a job. Then a process table which is related to a given template. This
would include each different process a job may go through (i.e. print report
A, send email, print report B). Related to the process table would be a
table or tables that include the procedures or reference to code that would
be fired off specific to the current process being run for that job.

I don't think I will have problems conceptualizing any sql that may be
executed as a result of a given process. However, certain aspects will have
to be written in C#.

Finally, do any of you have ideas or have worked where you store references
to a .NET class within a table. So when a certain process is reached the .
NET class is called?

I was told that this isn't terribly uncommon and I think the person who told
me said it was called a pipeline or routing architecture but haven't had much
luck finding any information.

Any direction or high level ideas would be apprecaited.

Thanks.

--
Message posted via http://www.sqlmonster.com

What you are attempting is - in my experience - not all that common. But, I
have seen it implemented fairly successfully when done right.

One of the things that you will need to be aware of is that getting a system
like this to perform well - especially when you have large amounts of data -
requires high-end hardware - and lots of it. A two core machine with 4GB of
RAM and an on-board disk controller probably isn't going to cut it. Most of
the time when I hear people complaining about this type of system it is
because they haven't thrown enough hardware at it.

Sincerely,

Chuck Heinzelman
SQL Server MVP

"doverj via SQLMonster.com" wrote in message
news:87326a65e6871-at-uwe...
> Greetings. I'm working on designing a database that provides a great deal
> of
> flexibility in that it stores calls to .NET classes and SQL procedures.
> Was
> curious if any of you had any ideas or experience you'd like to share.
>
> The system has to be able to handle a number of different types of jobs.
> Each job could have a number of different processes which may include
> printing reports, sending emails, updating tables, etc. I want to be able
> to
> have it so that each time a new job with a completely different set of
> processes is required, new deployments of the system aren't required (or
> if
> they are they are minor), only updates to database are needed. Rather,
> some
> sort of template would be created that would allow administrators to
> configure the jobs and their processes and details behind the jobs would
> be
> stored in the database architecture.
>
> What I'm thinking is having a series of tables that may look something
> like
> this. One is a template table that identifies the high level parts
> related
> to a job. Then a process table which is related to a given template.
> This
> would include each different process a job may go through (i.e. print
> report
> A, send email, print report B). Related to the process table would be a
> table or tables that include the procedures or reference to code that
> would
> be fired off specific to the current process being run for that job.
>
> I don't think I will have problems conceptualizing any sql that may be
> executed as a result of a given process. However, certain aspects will
> have
> to be written in C#.
>
> Finally, do any of you have ideas or have worked where you store
> references
> to a .NET class within a table. So when a certain process is reached the
> .
> NET class is called?
>
> I was told that this isn't terribly uncommon and I think the person who
> told
> me said it was called a pipeline or routing architecture but haven't had
> much
> luck finding any information.
>
> Any direction or high level ideas would be apprecaited.
>
> Thanks.
>
> --
> Message posted via http://www.sqlmonster.com
>

Thank you Chuck! That's good to know because there will in fact be very
large amounts of data. I will have to continue to look into the feasability
of doing this. Your help as always is much appreciated.

Chuck Heinzelman wrote:
>What you are attempting is - in my experience - not all that common. But, I
>have seen it implemented fairly successfully when done right.
>
>One of the things that you will need to be aware of is that getting a system
>like this to perform well - especially when you have large amounts of data -
>requires high-end hardware - and lots of it. A two core machine with 4GB of
>RAM and an on-board disk controller probably isn't going to cut it. Most of
>the time when I hear people complaining about this type of system it is
>because they haven't thrown enough hardware at it.
>
>Sincerely,
>
>Chuck Heinzelman
>SQL Server MVP
>
>> Greetings. I'm working on designing a database that provides a great deal
>> of
>[quoted text clipped - 49 lines]
>>
>> Thanks.

--
Message posted via http://www.sqlmonster.com