Board index » delphi » Master/Detail common field sync

Master/Detail common field sync

Is there any easy way to snychronize the common field between
master/detail tables? In other words, if the common field in the master
table is changed, is there any way to make sure the same change occurs
to the detail table? For example, if the common field is company_name in
the master table and that is changed how can I assure that the detail
table has the same change made to company_name for all existing records?

--
Jim Voelker
jvoel...@hrva.com

 

Re:Master/Detail common field sync


Quote

> Is there any easy way to snychronize the common field between
> master/detail tables? In other words, if the common field in the master
> table is changed, is there any way to make sure the same change occurs
> to the detail table? For example, if the common field is company_name in
> the master table and that is changed how can I assure that the detail
> table has the same change made to company_name for all existing records?

Jim,

if you link tables on fields that can change you have a big referential
integrity problem. How to deal with it depends on the database type you
use. Paradox tables have a referential integrity feature build in that can
propagate changes to a field in the main table (the one that all others
refer to for this field) to all records in other tables that are dependent
on this field. For SQL databases one has to program this kind of stuff in
stored procs.

But generally having the problem at all is a symptom of insufficient
database normalization. The company_name should only appear in the company
table, all tables makeing reference to companies should use company_id
instead, which will never change for a specific company. This changes to
the company_name in the company table will be transparent to all other
tables.

Peter Below (TeamB)  100113.1...@compuserve.com)
No e-mail responses, please, unless explicitely requested!

Re:Master/Detail common field sync


Thank you for the response.

I am using Paradox tables. I tried to use referential integrity pursuant to
your suggestion. The problem I had with that is that the database desktop is
forcing a match between all the keys in the parents table and fields in the
child table. I only want to match one key field in the parent table (which has

multiple key fields) with one field in the child table. The database desktop
is
not allowing me to do this. Am I missing something? If not then I will have to

write code as suggested in the last paragraph of your message.

Do you have any suggestions for a good book that explains these facets of
Delphi programming?

Thanks for your help!

Quote
Peter Below wrote:

> > Is there any easy way to snychronize the common field between
> > master/detail tables? In other words, if the common field in the master
> > table is changed, is there any way to make sure the same change occurs
> > to the detail table? For example, if the common field is company_name in
> > the master table and that is changed how can I assure that the detail
> > table has the same change made to company_name for all existing records?

> Jim,

> if you link tables on fields that can change you have a big referential
> integrity problem. How to deal with it depends on the database type you
> use. Paradox tables have a referential integrity feature build in that can
> propagate changes to a field in the main table (the one that all others
> refer to for this field) to all records in other tables that are dependent
> on this field. For SQL databases one has to program this kind of stuff in
> stored procs.

> But generally having the problem at all is a symptom of insufficient
> database normalization. The company_name should only appear in the company
> table, all tables makeing reference to companies should use company_id
> instead, which will never change for a specific company. This changes to
> the company_name in the company table will be transparent to all other
> tables.

> Peter Below (TeamB)  100113.1...@compuserve.com)
> No e-mail responses, please, unless explicitely requested!

--
Jim Voelker
jvoel...@hrva.com

Re:Master/Detail common field sync


Quote
> I am using Paradox tables. I tried to use referential integrity pursuant to
> your suggestion. The problem I had with that is that the database desktop is
> forcing a match between all the keys in the parents table and fields in the
> child table. I only want to match one key field in the parent table (which has
> multiple key fields) with one field in the child table. The database desktop
> is not allowing me to do this. Am I missing something?

Jim,

i think you do but i'm not enough of a database expert to tell you exactly what,
sorry. The only Delphi DB oriented book that i have is in german and thus
probably not much use for you. Try to post in the database.desktop newsgroup.

Peter Below (TeamB)  100113.1...@compuserve.com)
No e-mail responses, please, unless explicitely requested!

Re:Master/Detail common field sync


Jim,

We changed our approach to linking tables in relational databases to the
following precisely to avoid what you are describing.

We now use a 32-bit Integer for the link field between tables. The user
NEVER sees this number. We call this our record Id. This completely
eliminates the need to cascade changes down through the table hierarchy.
It also frees the user from having to remember codes or keeping a paper
list of codes by their desk.

We use codes such as customer numbers only under protest. Usually our
users prefer incremental-searching combo-boxes and the like to get to
real names of things. For more complex searches we create a wizard that
returns a list of the closest matches from which the user can select the
exact match.

With all of this, the user never has to type a code or correctly spell a
name to find the record they want.

Also, For main tables (at the top of the relational hierarchy) we use an
auto incrementing field (a long integer in Paradox and Access) as the
record Id. For detail tables we might use an auto incrementing field, or
we might use a compound key made up of the parent table's record Id and
a simple long integer record Id in the detail table. The detail table
record Id would always number its records from 0 to x  with the parent
table record Id for uniqueness.

This way of assigning record Ids to the detail table gives you the
advantage of being able to use the record Id to set the display order of
the detail records.

The disadvantage of this method is that when you want a company name -
say in a report - the only way you can get it is to go to the company
table. But, that's just the way it is in a well designed relational
database. You never (almost) store the same data in two places (except
linking fields).

Hope this will be helpful
Glen Stone

Re:Master/Detail common field sync


James M. Voelker a crit dans le message <35EE9320.B3D95...@hrva.com>...

Quote
>...
>I am using Paradox tables. I tried to use referential integrity pursuant to
>your suggestion. The problem I had with that is that the database desktop
is
>forcing a match between all the keys in the parents table and fields in the
>child table. I only want to match one key field in the parent table (which
has

>multiple key fields) with one field in the child table. The database
desktop
>is
>not allowing me to do this. Am I missing something?
>...

James,

Try to use a secondary index with only the fields that will be used in the
relation. The database desktop will allow to you build the relation with
these fields them.

Hope I have correctly understood your pb...

Regards
Eric

Other Threads