Data in different tables.

[Wilfried]

Hi,

I have 5 tables:

main

data1

data2

data3

data4

Each table has the field "name".

In a form, I want to write data in the "name" field to all the tables at the
same time.

Is this possible without complicated programming?

Regards, Wilfried (Belgium)

[pietlinden]

You could... but why? What data do data1-4 contain? Are they really
child tables of main?

[Mike Preston]

On Thu, 30 Jun 2005 18:43:25 GMT, "Wilfried"
<wi***************@pandora.be> wrote:

Hi,

I have 5 tables:

main

data1

data2

data3

data4

Each table has the field "name".

In a form, I want to write data in the "name" field to all the tables at the
same time.

Is this possible without complicated programming?

Yes. It is extremely easy.

You remove the "name" field from each of the data tables and then you
only need to replace it once, in the main table.

Do a search in google on "normalization". It will explain why you
will want to do it this way.

mike

[Bob Quintal]

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42*****************@news.INDIVIDUAL.NET:

On Thu, 30 Jun 2005 18:43:25 GMT, "Wilfried"
<wi***************@pandora.be> wrote:

Hi,

I have 5 tables:

main

data1

data2

data3

data4

Each table has the field "name".

In a form, I want to write data in the "name" field to all the
tables at the same time.

Is this possible without complicated programming?

Yes. It is extremely easy.

You remove the "name" field from each of the data tables and
then you only need to replace it once, in the main table.

Do a search in google on "normalization". It will explain why
you will want to do it this way.

mike

What about if the "name" is the foreign key he wants to
propagate to the additional records?

--
Bob Quintal

PA is y I've altered my email address.

[Mike Preston]

On Thu, 30 Jun 2005 23:03:31 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42*****************@news.INDIVIDUAL.NET:

On Thu, 30 Jun 2005 18:43:25 GMT, "Wilfried"
<wi***************@pandora.be> wrote:

Hi,

I have 5 tables:

main

data1

data2

data3

data4

Each table has the field "name".

In a form, I want to write data in the "name" field to all the
tables at the same time.

Is this possible without complicated programming?

Yes. It is extremely easy.

You remove the "name" field from each of the data tables and
then you only need to replace it once, in the main table.

Do a search in google on "normalization". It will explain why
you will want to do it this way.

mike

What about if the "name" is the foreign key he wants to
propagate to the additional records?

Oh, gosh. Here we go again.

A pox on keys that have real world meaning. A pox I say.

mike

[Wilfried]

The table "main" contains the names and addresses.

tables "data1-4" are used to store data of 4 different contests

to make an overall calculation with the 4 data-tables, it is necessary that
they all contain the same names (with "0" in the other fields).

Now I do this myself, for each new name I open the 4 data-tables and type a
zero in the first field (the name is filled in automatically).

<pi********@hotmail.com> schreef in bericht
news:11**********************@g47g2000cwa.googlegr oups.com...

You could... but why? What data do data1-4 contain? Are they really
child tables of main?

[Wilfried]

Yes. It is extremely easy.

You remove the "name" field from each of the data tables and then you
only need to replace it once, in the main table.

Do a search in google on "normalization". It will explain why you
will want to do it this way.

mike

I can't do that Mike, the tables are linked together with the "name"-field.

To make some calculations with the data-tables, it is necessary to have the
names in all four the tables

(even when there is no data in the other fields)

thanks anyway,

regards, Wilfried

[Bob Quintal]

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42*****************@news.INDIVIDUAL.NET:

What about if the "name" is the foreign key he wants to
propagate to the additional records?

Oh, gosh. Here we go again.

A pox on keys that have real world meaning. A pox I say.

mike

The O.P. would have the same problem updating a number into the
foreign key field of the four tables, whether it's a number or a
name

A pox on autonumbers and a pox on those who use them instead of
a significant primary key.

And I'm not saying that the primary key should not be a numeric
ID, just that it should never be an autonumber.

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

"Wilfried" <wi***************@pandora.be> wrote in
news:Or**********************@phobos.telenet-ops.be:

The table "main" contains the names and addresses.

tables "data1-4" are used to store data of 4 different
contests

to make an overall calculation with the 4 data-tables, it is
necessary that they all contain the same names (with "0" in
the other fields).

Now I do this myself, for each new name I open the 4
data-tables and type a zero in the first field (the name is
filled in automatically).
What you need to do is go to the relationships view and set the
four "name" fields to relate to the name in your main table.
Mike Preston is correct in that you should assign a NameID field
to that table and use it instead, because you may occasionally
run into 2 John Smiths. There are 5 Robert Quintals in the local
phone directory.

Once you have your relationships, you set up a form to enter the
data in the main table, and you then add a subform for each of
the other four forms. The wizard should take care of
automatically populating the sub-tables.
<pi********@hotmail.com> schreef in bericht
news:11**********************@g47g2000cwa.googlegr oups.com...

You could... but why? What data do data1-4 contain? Are
they really child tables of main?

--
Bob Quintal

PA is y I've altered my email address.

[Wilfried]

> What you need to do is go to the relationships view and set the

four "name" fields to relate to the name in your main table.
Mike Preston is correct in that you should assign a NameID field
to that table and use it instead, because you may occasionally
run into 2 John Smiths. There are 5 Robert Quintals in the local
phone directory.

Once you have your relationships, you set up a form to enter the
data in the main table, and you then add a subform for each of
the other four forms. The wizard should take care of
automatically populating the sub-tables.

Why didn't I think of that ;-)

It's so simple and I think you are absolutely right.

I'm going to try it out as soon as possible.

Many thanks to all of you.

Wilfried.

[Mike Preston]

On Fri, 01 Jul 2005 15:18:48 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42*****************@news.INDIVIDUAL.NET:

What about if the "name" is the foreign key he wants to
propagate to the additional records?
Oh, gosh. Here we go again.

A pox on keys that have real world meaning. A pox I say.

mike

The O.P. would have the same problem updating a number into the
foreign key field of the four tables, whether it's a number or a
name

Not necessarily. I think the OP would have a much more difficult
time. <g>

That might allow the light bulb to go on.
A pox on autonumbers and a pox on those who use them instead of
a significant primary key.
:-)
And I'm not saying that the primary key should not be a numeric
ID, just that it should never be an autonumber.

Autonumber and random, IMO. The more difficult it is for folks to
think that it might mean something, the better. Besides, if
autonumber and random, if and when the database is replicated, you
avoid the: "I replicated my database and now the autonumbers are no
longer in sequence. Help!" requests.

mike

[Bob Quintal]

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42*****************@news.INDIVIDUAL.NET:

On Fri, 01 Jul 2005 15:18:48 GMT, Bob Quintal
<rq******@sPAmpatico.ca> wrote:

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42*****************@news.INDIVIDUAL.NET:

What about if the "name" is the foreign key he wants to
propagate to the additional records?

Oh, gosh. Here we go again.

A pox on keys that have real world meaning. A pox I say.

mike

The O.P. would have the same problem updating a number into
the foreign key field of the four tables, whether it's a
number or a name

Not necessarily. I think the OP would have a much more
difficult time. <g>

That might allow the light bulb to go on.

A pox on autonumbers and a pox on those who use them instead
of a significant primary key.

:-)

And I'm not saying that the primary key should not be a
numeric ID, just that it should never be an autonumber.

Autonumber and random, IMO. The more difficult it is for
folks to think that it might mean something, the better.
Besides, if autonumber and random, if and when the database is
replicated, you avoid the: "I replicated my database and now
the autonumbers are no longer in sequence. Help!" requests.

mike

Alphanumeric and meaningful. Take the case of a bank. Each
account number is a primary key describing that account. the
account number is also a foreign key in the transactions table.
If you suggest using an autonumber, the auditors will tar and
feather you.

Besides, the users should never see the table. all they should
see on the form or report is a field with a label.

Different example. Invoices and invoice lines. the pk of the
invoice must never change. It should not be an autonumber but
may easily be a set of sequential alpha-numeric characters.

I'm maintaining a system where the clown who wrote it used
autonumbers as keys. He could have used three other candidates.
If you have two or more candidate keys in a table you are not
normalized.

That's my opinion, and its not humble after 25 years designing
databases.

--
Bob Quintal

PA is y I've altered my email address.

[Mike Preston]

<and we're off to the races....>

On Fri, 01 Jul 2005 22:51:30 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

Oh, gosh. Here we go again.

A pox on keys that have real world meaning. A pox I say.

mike

The O.P. would have the same problem updating a number into
the foreign key field of the four tables, whether it's a
number or a name
Not necessarily. I think the OP would have a much more
difficult time. <g>

That might allow the light bulb to go on.

A pox on autonumbers and a pox on those who use them instead
of a significant primary key.

:-)

And I'm not saying that the primary key should not be a
numeric ID, just that it should never be an autonumber.

Autonumber and random, IMO. The more difficult it is for
folks to think that it might mean something, the better.
Besides, if autonumber and random, if and when the database is
replicated, you avoid the: "I replicated my database and now
the autonumbers are no longer in sequence. Help!" requests.

mike

Alphanumeric and meaningful. Take the case of a bank. Each
account number is a primary key describing that account. the
account number is also a foreign key in the transactions table.
If you suggest using an autonumber, the auditors will tar and
feather you.

Then the auditors need an attitude adjustment.

Then again, perhaps this is why, when banks merge, they constantly
screw things up. If the account number wasn't tied to the primary
key, then they could merge bank records quite simply. Amazing, huh?
Besides, the users should never see the table. all they should
see on the form or report is a field with a label.
This we do not disagree with. But if they shouldn't see it, why does
it need to take any particular form? One ghost is as good as another.
Why do you want to put requirements on the ghost?
Different example. Invoices and invoice lines. the pk of the
invoice must never change.
This applies to all pk's, imo.
It should not be an autonumber but
may easily be a set of sequential alpha-numeric characters.
Why not an autonumber? If it doesn't change, and users don't see it,
then why does it matter what it looks like?

There is nothing that stops a business rule from applying to another
field. You want to use the database engine to enforce a relationship
between record creation and reporting. There simply is no need.
I'm maintaining a system where the clown who wrote it used
autonumbers as keys. He could have used three other candidates.
If you have two or more candidate keys in a table you are not
normalized.
Maybe, but irrelevant. I don't think we are strictly talking about
normalization, here. We are also talking about changes in
information. A normalized piece of data can change, such as the
maiden name of an individual, recorded only once and used by all
processes that require use of said maiden name. A pk should not.
That's my opinion, and its not humble after 25 years designing
databases.

I yield to your experience. But the conclusion makes no sense to me,
except in the context of ensuring an application conforms to an
outside standard (such as an auditor's requirement), whether or not
that standard makes sense from a design perspective.

mike

[Larry Linson]

"Mike Preston" wrote

That's my opinion, and its not humble
after 25 years designing
databases.

I yield to your experience. But the conclu-
sion makes no sense to me, except in the
context of ensuring an application conforms
to an outside standard (such as an auditor's
requirement), whether or not that standard
makes sense from a design perspective.

Well, having taken my first programming class in 1958 and worked full-time
as a programmer and developer since 1959, I've got a few years even on Bob.

In my (also) not so humble opinion: Surrogate keys make sense, just for
convenience, when the number of fields required to uniquely identify the
item is "many" or, out of necessity, when there aren't a unique set but
processing requirements demand a unique key. Natural keys make sense when
the number of fields required to uniquely identify the record is "few" or
"manageable".

Use the appropriate approach depending on the circumstances and the people
doing the work. And, as the very knowledgeable Quality Assurance Manager of
a major computer company's contract services group used to tell us, "Be
very, very careful about using words like 'never', 'none', 'all', or
'always'." If you pressed him hard on the subject, he'd admit that "being
very, very careful" meant pretty close to "never" using those all-inclusive
terems. <GRIN>

Larry Linson
Microsoft Access MVP

[Bob Quintal]

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42**************@news.INDIVIDUAL.NET:

<and we're off to the races....>

On Fri, 01 Jul 2005 22:51:30 GMT, Bob Quintal
<rq******@sPAmpatico.ca> wrote: This applies to all pk's, imo.

It should not be an autonumber but
may easily be a set of sequential alpha-numeric characters.
Why not an autonumber? If it doesn't change, and users don't
see it, then why does it matter what it looks like?

There is nothing that stops a business rule from applying to
another field. You want to use the database engine to enforce
a relationship between record creation and reporting. There
simply is no need.

I'm maintaining a system where the clown who wrote it used
autonumbers as keys. He could have used three other
candidates. If you have two or more candidate keys in a table
you are not normalized.

Maybe, but irrelevant. I don't think we are strictly talking
about normalization, here. We are also talking about changes
in information. A normalized piece of data can change, such
as the maiden name of an individual, recorded only once and
used by all processes that require use of said maiden name. A
pk should not.

It is a normalization issue as well. You have introduced
duplicate information,(a unique record identifier where a unique
identifier already exists) since you have added a duplicate
primary key.

That's my opinion, and its not humble after 25 years designing
databases.
I yield to your experience. But the conclusion makes no sense
to me, except in the context of ensuring an application
conforms to an outside standard (such as an auditor's
requirement), whether or not that standard makes sense from a
design perspective.

Well it does make sense from a design perspective too. It makes
the database easier to undewrstaand for whoever is going to
maintain it after you are gone. It also means that you don't
have to go to extraordinary measures to protect the uniqueness
of the field that should have been the primary key.

There are times to use a surrogate key. I have a table where the
real primary key is spread across 5 fields. (two text, two
numeric and a datetime. I copped out and used an autonumbe5r to
simplyfy the constraints. And that is the issue, in essence. If
it is simpler without the autonumber, don't use an autonumber.
mike

--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

"Larry Linson" <bo*****@localhost.not> wrote in
news:NGoxe.5879$mr4.5653@trnddc05:

In my (also) not so humble opinion: Surrogate keys make sense,
just for convenience, when the number of fields required to
uniquely identify the item is "many" or, out of necessity, when
there aren't a unique set but processing requirements demand a
unique key. Natural keys make sense when the number of fields
required to uniquely identify the record is "few" or "manageable".
I would say that a multi-column natural key makes sense *only* when
it's never stored as the foreign key in another table. If it is,
that makes joins very hard to write.

I'd say the same thing about any multi-column key, natural or not. I
once designed a schema that had two columns in ever PK, an
Autonumber plus a text field to identify which database a record
originated in (there was data shared between a web-based MySQL
database and an office-based Access database; the Access app was a
superset of the data on the website). It was an absolute nightmare.
Some of the join tables were truly nightmarish, with two columns
from each table. The really scary part was that there was also a
table that joined this *join* table to a third table, so that it had
4 columns from the join table and, thankfully, a single foreign key
from a different table (which could not originate records anywhere
but on the website, so it didn't need to have the second field).

It was horrendous.

I would *never* do such a thing again.

And it soured me forever on compound PKs.
Use the appropriate approach depending on the circumstances and
the people doing the work. And, as the very knowledgeable Quality
Assurance Manager of a major computer company's contract services
group used to tell us, "Be very, very careful about using words
like 'never', 'none', 'all', or 'always'." If you pressed him hard
on the subject, he'd admit that "being very, very careful" meant
pretty close to "never" using those all-inclusive terems. <GRIN>

I would say that there are simply very few tables where there is a
good candidate for a natural key, since in the real world we often
don't have complete information at the time we create a record,
which means we can't fill out all the fields for the natural key.

Tables with people in them are the most obvious kind that are almost
impossible to create with a natural key. And the only way to do it
would probably require a great deal of duplication (you'd need to
have the name and the address all in the PK, and address really
belongs in a separate table, so you'll be joining to that, but, of
course, the natural PK for that table will have several fields in
it; and so forth).

So, removal of data duplication is also an argument for using
surrogate keys -- that's one I'd never really thought of before.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Bob Quintal <rq******@sPAmpatico.ca> wrote in
news:1120258290.5b33c7f3bb86a0a15ab36863ee4deacd@t eranews:

Different example. Invoices and invoice lines. the pk of the
invoice must never change. It should not be an autonumber but
may easily be a set of sequential alpha-numeric characters.

You're arguing two different questions, Autonumber vs. surrogate
keys.

Invoices are numbered with a generated artificial key, so that's not
by any means a natural key.

I agree that Autonumber is a bad candidate for an invoice number,
but that's because the values for invoice numbers *do* matter, so
Autonumber (random or not) is inappropriate -- you need to generate
the numbers yourself, to insure no gaps.

You also mention "sequential alpha-numberic characters," and I'd
suggest that any invoice number that includes both letters and
numbers is de-normalized, unless it's a hex number. If the letters
have meaning (e.g., indicate the branch generating the invoice),
then it ought to be in a separate column, with a compound PK. Human
beings don't need to know that it's stored that way, but it's the
correct way to do it for the schema. Then it's up to the programmer
whether or not to make the numeric part globablly unique or unique
within the branch. I'd vote for globally unique, but auditors may
feel otherwise.

But once you've arrived at a compound key, you're running into
significant additional issues. If I were designing an app where the
auditors required unique alpha-numeric numbers, with non-global
numeric part, I'm pretty sure I'd use a non-visible surrogate key to
maintain the actual relationships in the schema. That surrogate key
could be an autonumer, or it could be a generated unique number (in
the event that the auditors felt they needed to look at it), but
either way, it's going to make design of the schema and the
application on top of it substantially easier.

So, there's several issues here that you seem to have mixed up with
the question of natural keys.

And coming to a satisfactory conclusion on how to solve the problem
presented requires separating out the separate issues and
considering each of them on their own merits.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Bob Quintal <rq******@sPAmpatico.ca> wrote in
news:1120313696.2ca249ca17e3023f1bb57ae11baab9ce@t eranews:

mb******@pacbell.net.invalid (Mike Preston) wrote in
news:42**************@news.INDIVIDUAL.NET:

<and we're off to the races....>

On Fri, 01 Jul 2005 22:51:30 GMT, Bob Quintal
<rq******@sPAmpatico.ca> wrote:

This applies to all pk's, imo.

It should not be an autonumber but
may easily be a set of sequential alpha-numeric characters.

Why not an autonumber? If it doesn't change, and users don't
see it, then why does it matter what it looks like?

There is nothing that stops a business rule from applying to
another field. You want to use the database engine to enforce
a relationship between record creation and reporting. There
simply is no need.

I'm maintaining a system where the clown who wrote it used
autonumbers as keys. He could have used three other
candidates. If you have two or more candidate keys in a table
you are not normalized.

Maybe, but irrelevant. I don't think we are strictly talking
about normalization, here. We are also talking about changes
in information. A normalized piece of data can change, such
as the maiden name of an individual, recorded only once and
used by all processes that require use of said maiden name. A
pk should not.

It is a normalization issue as well. You have introduced
duplicate information,(a unique record identifier where a unique
identifier already exists) since you have added a duplicate
primary key.

Well, if your natural key is 5 columns wide, you're duplicating far
more data than would be required with a surrogate key.

That's my opinion, and its not humble after 25 years designing
databases.

I yield to your experience. But the conclusion makes no sense
to me, except in the context of ensuring an application
conforms to an outside standard (such as an auditor's
requirement), whether or not that standard makes sense from a
design perspective.

Well it does make sense from a design perspective too. It makes
the database easier to undewrstaand for whoever is going to
maintain it after you are gone. It also means that you don't
have to go to extraordinary measures to protect the uniqueness
of the field that should have been the primary key.

Extraordinary measures? You just put a unique index on it. There's
nothing extra involved there.
There are times to use a surrogate key. I have a table where the
real primary key is spread across 5 fields. (two text, two
numeric and a datetime. I copped out and used an autonumbe5r to
simplyfy the constraints. And that is the issue, in essence. If
it is simpler without the autonumber, don't use an autonumber.

The number of instances where an autonumber is appropriate in the
first place where the structure will be simpler without the
autonumber is, in my experience, very small.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc