nightwatch77 napisa³(a):
[quoted text, click to view] > Piotr Dobrowolski wrote:
>> helpful sql napisa³(a):
>>
>>> Hi all,
>>> Are there any good Sql code generation tools out there in the
>>> market? If not can you please give me tips or sample code for
>>> creating one?
>>>
>>> I need to automate code generation for data integration. Here is what
>>> I repeatedly need to do...
>>>
>>> We have a table called CONTSUPP in our Sql Server database. All of
>>> our clients have the same database structure so they all have the
>>> CONTSUPP table. I need to create different kinds of views on this
>>> table for different clients. I also need to generate Instead of
>>> Triggers on these views. Then I need to write stored procedures to
>>> import data from another source table into my views. The structure of
>>> the source table can vary by clients.
>>>
>>> Thanks in advance...
>>>
>> [PD] Try MyGeneration - it's free and you can edit templates so that
>> they will suit your needs. BTW are you sure that it's a good idea to
>> have different table structure for every client? I don't know your
>> product so I'm not saying that it's wrong but in many cases it's
>> better to create more general table structure, let's say something
>> like this:
>> maintable - id, name, address, etc
>> attributestable - id, maintableid, attributekind, attributevalue
>> It can save you some work, but of course has some disadvantages
>> (mainly speed :( )
>>
> Well, Piotr, are you sure you haven't gone too far in generalization?
> You have invented an universal relational database structure capable of
> storing any data in a form of name-value pairs. Have you thought about
> its usability and performance? Non-trivial queries will be a nightmare -
> too complicated to write and very slow to execute, and there will be no
> control on database structure, data types, constraints etc.
>
[PD] I defenitely recommend using "standard" columns for data you have
in every deployment. I agree that there is absolutely no sense to
reinvent relational database :)
But, if you have a problem described above it's good (in some cases) to
use attributes stored in external table. Please note that solution I
described above is very simplified - in your design you can have
additional table for atrribute types and names and you can use couple of
tables for storing atributes depending of their type (like
intattributes, varcharattributes etc.). This solution has some
advantages - you can write triggers and reports once and use them in all
of your deployments, version upgrade is much easier etc.
As I wrote before - I think that the main disadvantage of this solution
is performance. But if you can have small performance overhead (I don't
think that if you have let's say 2-3 additional attributes it would slow
your query a lot - it's just a simple join :) ) this solution can be
applied succesfully. Actually I have worked in a team creating biggest
ERP system on polish market and we have used this solution to allow
customer to describe most of the objects in the database with custom
attributes - it was a great success, one of the most widely accepted and
used feature and the performance was acceptable.
--
Piotr Dobrowolski
