Hi Guys,
   Having 70+ tables in the Application suite I am working on, many fields are reproduced across many tables.  i.e.
JobNo, AccountNo, etc.

   Additionally I have JobTickets, AJobTickets & _AJobTickets for levels of archiving records.  Each is exactly the same
Table structure.

   If I decide to change say JobNo from an Integer to a SmallInt, I have to change those three tables plus wherever else
JobNo appears.

   It would be really nice to have as part of the Data information, a repository for fields and their definitions.  

   The idea being that if I changed JobNo per above I would only have to change it in the repository and the change would
automatically reflect in the relevant Tables.

   Discussion/Comments invited.

Regards,
Ian

Ian


Hmm. Interesting and dangerous.

It would need a rework of EDBManager and a rewrite of the SQL engine so users / developers were forced into using the repository otherwise it would just be a matter of time (especially in multi dev environments) before someone had JobNo as integer, and someone else as VARCHAR(50) and woops!

NOTE: I do not exclude single developer shops from this happening.

However, as I said interesting so you may want to implement a halfway house for yourself - it would be a good exercise in learning SQL

What you need is a script that will take as parameters a column name and its definition. It would use Information.TableColumns to generate a list of all tables using the column name and create a piece of SQL to alter the appropriate tables probably using EXECUTE IMMEDIATE.

Roy Lambert

Ian

I think you are talking about starting to build your own data-framework. Roy is right to be cautious. There are a lot of issues, this is a huge undertaking.

As a simple example, you seem to be saying that you would change all fields with the same field-name to the same data-type, if you changed the datatype in a repository. It is not at all clear to me that this would really be useful, and (if field-names were re-used) could easily cause issues.

I very, very rarely change the datatype of a field. Therefore I would not put effort into a system to automate such a process.

Over a lot of time working with database systems I have built up quite a rich set of my own components for managing change to data. Some of these are fairly global, others are really bespoke to particular things that my own systems use.

Elevate comes with a very rich set of its own tools to help in this regard, for example there is code to allow comparison of different versions of databases, and to create change-scripts to update one database so it has a structure identical to another for the purpose of upgrades.

There are also numerous information and configuration tables to allow analysis of the structure of your database, and of course it is possible to build a Delphi "sys-admin" app which can do useful tasks, based on your own rules.

I think a critical issue here is that we all code differently, so it is hard to develop general purpose tools to do the type of job you describe.

For example, you describe in your schema creating a "Job" data-record and then "copying it into a new table". I never do this. Once data has been created, I keep it in one place. If a record goes through stages of use in an application I add "child" records ("leaves" as in leaves of a tree) to indicate incremental change to the record. These leaves only feature additional fields that need to be added, they do not take copies of the original core data of the record (ie customer, job-number, job-description).

Given our different ways of working we need different tools for the job.

Adam


>I think you are talking about starting to build your own data-framework. Roy is right to be cautious. There are a lot of issues, this is a huge undertaking.

Done "properly" you're right it would be huge, but I can think of a trivial approach for use only in development where it matters a lot less if you screw the data. It would mean three tables and a smallish amount of Delphi and SQL. Usage would require discipline to never use EDBManager to build or alter tables.

>As a simple example, you seem to be saying that you would change all fields with the same field-name to the same data-type, if you changed the datatype in a repository. It is not at all clear to me that this would really be useful, and (if field-names were re-used) could easily cause issues.

In my case pretty much all single column primary keys are called _ID, generally autoinc but also GUID and VARCHAR. I think there's even a TIMESTAMP in an old project.

>I very, very rarely change the datatype of a field. Therefore I would not put effort into a system to automate such a process.

Not once I've moved out of development.


>For example, you describe in your schema creating a "Job" data-record and then "copying it into a new table". I never do this. Once data has been created, I keep it in one place. If a record goes through stages of use in an application I add "child" records ("leaves" as in leaves of a tree) to indicate incremental change to the record. These leaves only feature additional fields that need to be added, they do not take copies of the original core data of the record (ie customer, job-number, job-description).

Out of interest are you talking extra tables, or spare columns or (one of my favourites) a CLOB column with variable data.

I can see a use for an archive table (I have a couple). You may want to occasionally reference the archive data but shifting it out of the main table helps to speed things up.

Roy

Roy

>For example, you describe in your schema creating a "Job" data-record and then "copying it into a new table". I never do this. Once data has been created, I keep it in one place. If a record goes through stages of use in an application I add "child" records ("leaves" as in leaves of a tree) to indicate incremental change to the record. These leaves only feature additional fields that need to be added, they do not take copies of the original core data of the record (ie customer, job-number, job-description).

>>Out of interest are you talking extra tables, or spare columns or (one of my favourites) a CLOB column with >>variable data.

I almost always use a child table either 1=1 or 1=many, depending on the nature of the data. Within this child table there is fairly often a CLOB column, usually for XML (ug) JSON (slightly better?) or HTML data.

>>I can see a use for an archive table (I have a couple). You may want to occasionally reference the archive data >>but shifting it out of the main table helps to speed things up.

I do not particularly find EDB slows down on larger table sizes provided that you index it carefully. I have tables with millions of rows and well formed SQL performs fast.

The problem if you have a huge table is when you write SQL badly, you can have to wait around a long time ...

I agree that Archives can be useful, but they also add complexity if users want reports or data that span back over archived materials.

Adam Brett wrote:

> For example, you describe in your schema creating a "Job" data-record and then "copying it into a new table". I never
> do this. Once data has been created, I keep it in one place. If a record goes through stages of use in an application
> I add "child" records ("leaves" as in leaves of a tree) to indicate incremental change to the record. These leaves
> only feature additional fields that need to be added, they do not take copies of the original core data of the record
> (ie customer, job-number, job-description).

Hi Adam,

   So, the Customer, fairly quickly, builds up a large database with, in time, most of the records are completed/finished
with.  The Log tables, Changes & Users, also get very large quickly.  A single set of Job records may go through up to
a dozen changes in its 'lifetime' until closed, echa time a change being record being written.

   The mechanism is that after 12months, or more at the Customer's discretion, they archive all the completed/finished
jobs older than say 12 months, or more at the Customer's disctretion.  They are still accessible/reportable/etc via the
app, just a diiferent set of tables.  Need to access is rare.

   After 7 years, TAX record retention period, att the Archive tables can hold several 100k records, the Customer can
Archive any 'Archived' records into a _Archive set of tables.  They are still available via the Reporter but at this
age generally never accessed.

   All horses for courses.

Regards,
Ian

Ian,

<< It would be really nice to have as part of the Data information, a repository for fields and their definitions. >>

What you're referring to are user-defined types, which are part of the SQL specification, but not implemented in EDB.  They can be very powerful because they remove a lot of boilerplate SQL used with DDL statements.

Tim Young
Elevate Software
www.elevatesoft.com

Ian,

I use Context Database Designer from www.contextsoft.com/ which allows you to document your databases. It is a great system.

There is the ability to reverse engineer existing databases but I have not been able to get it to work for ElevateDB.

It does allow you to define DOMAINS which is what Tim referred to.  Domains are a subset of an existing data type.

If you wish to document your database then this is a very useful tool.

Richard

Hi Richard,
   Yes, I have it as well and havn't been able to get it working with EDB.  I don't know about your environment but I
don't use the standard EDB file names & extents and the Context stuff expects standard file names/extents for EDB.

   I have spoken to Michael at Context and he is considering adding a mechanism to accomodate 'alternative' file Names &
extents.

Regards,
Ian

Follow up.  It does partly work using C/S.