473,856 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Append master table and update child at same time?

This is something that on the surface seems like it should be simple,
but I can't think of a way to do this. I have a table that is a list
of "jobs", which users create and use. It has a single autonumber key
field. Within a job, there can be multiple orders. Logically, there'd
be an orders table that is a child of the job table. This orders table
has a long integer field that matches it to the master job autonumber
field. Unfortunately, we don't always have control over orders, and
there can often be cases where orders are created that don't yet have a
master job record in our local database. I import data from HQ that
relates to these orders. That data has it's own unique identifiers,
and also contains some job-level info. I can import this data into our
local orders table, so we always have a local order record that relates
to HQ's orders. What I would LIKE to do is append a new job record for
every incoming order record that does not yet have a matching job, and
of course populate the matching long integer field in the orders table
to make it relate to the master record it just caused to be created.
It's easy to append new job records for the "orphaned" orders that are
imported. But since the key field of the parent table is an
autonumber, I don't know how to relate that back to the child table so
that is no longer orphaned.

Does that make sense?

Apr 6 '06 #1
3 3397
Dear Darin:

Perhaps a simpler approach. Put all the "orphaned" dependent table rows
under a SINGLE Job record that is "unassigned ". You'll be able to find all
of them very readily that way, and reassign the long integer link to the
proper Job after you have created it.

If you make the "name" of this account "*** unassigned ***" then you can
always find this special record by that key.

By the way, the Name column of jobs should probably be uniquely indexed.
You wouldn't want a combo box list of Jobs to contain duplicates, would you?
Uniqueness of natural keys is not obviated by the use of autonumber keys.

Now, if you have uniqueness of both the autonumber key and the natural
"name" key, which should be the primary key for the table? Well, the table
will be physically ordered according the the PK when the database is
compacted. So, for which of the keys is such ordering most useful? Well,
how often will you list the Jobs by the autonumber? Wouldn't you always
list them alphabetically by the "name"? So, the "name" would be a good
choice for PK, with the autonumber being a separate unique index. It still
works for relationships that way.

We're almost there. Why even have the autonumber column? If you use the PK
"name" column in the dependent tables what is the penalty? A bit of disk
space, and maybe one extra level in the BTree for the lookup in the index.
What is the gain? You save disk space (eliminating a column in the foreign
table) and, when all you need is the Job name, you won't even need to JOIN
to the Job table to get it. It's already in the dependent table. It's
FASTER!

Not a popular option, but it works perfectly. It isn't always clear which
is faster.

Tom Ellison
"Darin" <go****@darincl ine.com> wrote in message
news:11******** ************@z3 4g2000cwc.googl egroups.com...
This is something that on the surface seems like it should be simple,
but I can't think of a way to do this. I have a table that is a list
of "jobs", which users create and use. It has a single autonumber key
field. Within a job, there can be multiple orders. Logically, there'd
be an orders table that is a child of the job table. This orders table
has a long integer field that matches it to the master job autonumber
field. Unfortunately, we don't always have control over orders, and
there can often be cases where orders are created that don't yet have a
master job record in our local database. I import data from HQ that
relates to these orders. That data has it's own unique identifiers,
and also contains some job-level info. I can import this data into our
local orders table, so we always have a local order record that relates
to HQ's orders. What I would LIKE to do is append a new job record for
every incoming order record that does not yet have a matching job, and
of course populate the matching long integer field in the orders table
to make it relate to the master record it just caused to be created.
It's easy to append new job records for the "orphaned" orders that are
imported. But since the key field of the parent table is an
autonumber, I don't know how to relate that back to the child table so
that is no longer orphaned.

Does that make sense?

Apr 6 '06 #2
Thanks for the reply Tom! Unfortunately, there are several pitfalls,
most of which boil down to the fact that I don't have complete control
over the data. I'm dealing with a lot of existing data... tens of
thousands of records. Many of which do have duplicate names. But for
the sake of just discussing the concept, I have to be honest: the
thought of keying off of a name field does not make me feel good! In a
database with many child tables, repeating a 50 character text field in
each record of every table seems a bit inefficient. I'm also a bit
uneasy at the thought of keying and linking on editable fields. In our
business, there are very valid reasons to have to update the job name.
I know cascading updates should take care of that, but that just
doesn't "feel" as robust as keying off of a reliably unique field that
can't be modified. Maybe I'm just old-fashioned! ;-)

Apr 6 '06 #3
Dear Darin:

I've heard all this before. Actually, after 25 years in database business,
I'd say that identity keys are very new-fashioned. But I'm not going to try
to make a convert of you. However, if you make the effort to try this and
see how it affects your work, I'm telling you you'll see something quite
different from what you expect.

Tom Ellison
<go****@darincl ine.com> wrote in message
news:11******** **************@ g10g2000cwb.goo glegroups.com.. .
Thanks for the reply Tom! Unfortunately, there are several pitfalls,
most of which boil down to the fact that I don't have complete control
over the data. I'm dealing with a lot of existing data... tens of
thousands of records. Many of which do have duplicate names. But for
the sake of just discussing the concept, I have to be honest: the
thought of keying off of a name field does not make me feel good! In a
database with many child tables, repeating a 50 character text field in
each record of every table seems a bit inefficient. I'm also a bit
uneasy at the thought of keying and linking on editable fields. In our
business, there are very valid reasons to have to update the job name.
I know cascading updates should take care of that, but that just
doesn't "feel" as robust as keying off of a reliably unique field that
can't be modified. Maybe I'm just old-fashioned! ;-)

Apr 6 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
2455
by: Jan Wieck | last post by:
Dear community, for some reason the post I sent yesterday night still did not show up on the mailing lists. I have set up some links on the developers side under http://developer.postgresql.org/~wieck/slony1.html The concept will be the base for some of my work as a Software Engineer here at Afilias USA INC. in the near future. Afilias is like many of you in need of reliable and performant replication solutions for backup and failover...
2
1862
by: Danny | last post by:
I want to extract a subset of fields from one table into another the master table has many fields the subset has about half, but still many. Is there a way I can just append the master into the subsets structure and this way only the existing fields in the destination will be filled? That would be great, but it seems that the append query wants you to specify the fields. is there another way around this? I want to do it in code and it...
3
8487
by: Mark C | last post by:
I have a unbound form with a tab control with four tabs in an Access 97 database. On each tab I have a sub form each form on the sub forms is bound to its own table. Each table has a field that can link them all together. I have put an invisible unbound control on the master form that gets populated on open. I set the link child and link master of each sub form to the unbound control on the master form. When the form opens all the subforms...
4
2318
by: Michael Rodriguez | last post by:
Suppose I have a data entry screen with two strongly-typed datasets, dsCustomers and dsOrders, as the master and child, respectively. Because the two tables in the relationship are in two different datasets, I don't think I can use the DataRelation object to link them. If not, what is the best way to set up the master/detail relationship for the Windows form for these two tables? Should I just use a filter on the child table and...
0
340
by: Darin | last post by:
This is something that on the surface seems like it should be simple, but I can't think of a way to do this. I have a table that is a list of "jobs", which users create and use. It has a single autonumber key field. Within a job, there can be multiple orders. Logically, there'd be an orders table that is a child of the job table. This orders table has a long integer field that matches it to the master job autonumber field. ...
7
4550
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent records. Should I design a new form for this or can I somehow make this work in the same form. Thanks in advance, john
0
1733
by: Mike Wilson | last post by:
Dear group, I have an invoice entry form, which is a simple Master fields / Detail grid. The main summary information of the invoice are stored in one table in a dataset, which is bound using a BindingSource (InvoiceBindingSource). The line details of each item in the invoice is held in a child table which is bound to another bindingsource (InvoiceRowsBindingSource). I took the basic design from an MSDN article, and all works well....
2
4133
by: sj | last post by:
Situation: I have 2 tables, parent table (Invoice) and child table (InvoiceDetails) that is link by InvID in the child. Requirement: Need to do one-time append of information to another table of the similar structure. Problem: When main table is append to the new table, it will bear a new ID.
3
2466
by: jonceramic | last post by:
Hi, I have a 2 subform form, which uses the selection of a row in subform 1 to show a set of rows in subform 2. I have my queries pointing directly to fields on my form to choose what data is shown. However, when I first populated this form with my subforms, I was dumb (apparently) and had it accidentally pick "List_ID" as my child/master
0
9916
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9762
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11057
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10782
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10384
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9531
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7094
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4575
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.