Separate PK in Jxn Tbl?

[David W. Fenton]

m:

I do know that the debate over relationship representations has
been going on for years, neither side giving any ground. Some
aspects of it are never addressed. For example, making the two
foreign keys in a junction table a composite PRIMARY key prohibits
NULL values from either key, but declaring a separate surrogate
primary key together with a UNIQUE constraint on the
two-foreign-key-composite-key does allow NULL values for either
(or both!). This can be useful for representing unrelated entity
tuples, either childless parents or orphans.

Er, why go to all that trouble? You can represent the same childless
parents by simply omitting a record in the junction table.

 

[Sylvain Lafontaine]

« But many of them seem to write as if contents as determined by address ».

The content is not determined by the address and in fact, for those who are
using surrogate keys, the exact value of an address inside the database has
zero importance. To me, a table is a collection of jars, each jar has a
serial number and the information that must be stored into the database -
the information for which the database has been create - is put inside these
jars. Each jar has its own serial number; however, the exact value of this
serial number has not importance at all in regard to what is put inside the
jar and if copy the information from this database to another database, I
don't care to see the information to be put back in a jar with the exact
same serial number.

However, proponents of the use of natural keys insist to have a strong
relationship between the serial number on the jar and what's in it. To me,
such a relationship is pointless and I feel no necessity to use it.

 

[Bob Badour]

m:




What *are* you talking about?

Any mistakes in schema design that you can make in Access, you can
make in any other RDBMS.

I would agree that there are many places that the wizards in Access
and the sample databases encourage sub-optimal practices. But most
professional developers aren't using either wizards or the sample
databases as their models for developing their own applications.

If your impression of Access comes from futzing with it for 10
minutes and from encountering kludged-together apps created by your
company receptionist, then you just haven't a clue what Access
offers, either as an application development platform or as a data
store (using its native Jet engine).

This is a schema question, and that is orthogonal to Access, because
Access is an application development platform. If you use Jet for
your data store, then Jet is relevant to the discussion. But you can
use any data store that offers an ISAM or ADO or ODBC drivers, and
that means all your schema issues are completely divorced from
Access itself.

That you can't seem to keep this distinction clear in your mentions
of Access demonstrates pretty clearly that you are completely
clueless about Access and really aren't in any position to be making
disparaging comments about it.

Nothing you have written contradicts JOG. In fact, you have agreed with
him albeit vehemently and derogatively.

 

[Sylvain Lafontaine]

This remind me of the discussion twenty five years ago between relational
databases (like SQL-Server) and non-relational databases (like ISAM
databases, dBase, etc.). It has always been right to say that for simple
queries, non-relational databases are faster than relational databases.
However, nowadays, non-relational databases have (practically) vanished
because of their inherent slowness when the queries become more and more
complicated.

This is exactly the same situation with the possibility of accelerating a
query by using a natural key: you are accelerating simple queries that are
already light and fast but on the opposite side, you are slowing down
complexe queries that are already big and slow. Not sure if going this way
is really advantageous.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Well, that's just dumb. Checks in code can reduce database round-trips,
and
therefore can improve performance, but are not and cannot be a substitute
for constraints on the tables. It is the constraints on the tables that
keeps garbage out of the database.

If the users only access the tables through forms, conforming to best
practices in Access, how are they going to get garbage into the
tables? Now if you're trying to keep Jamie and his Excel SQL out of
your database, that's another story .
<<<<<

There can be several forms that access the same table, so you would have
to duplicate the code behind each form that accesses a table, or you can
get garbage into the database.

An extra join may be needed if the natural key from the parent table is
used
in a restrict clause. If all you have is the artificial key from the
parent
table, then you have to join in order to access the natural key columns.
With natural keys, the natural key values from the parent table also
appear
in the child table, so there isn't any need to join. Bottom line: joins
of
artificial keys are typically faster than joins of natural keys due to
the
size of the comparands, but with natural keys, fewer joins may be
needed..

If you're planning on using a natural key column in the child table as
part of a join then doesn't it make sense to include that field in the
child table?

Still waiting...


A typical schema with artificial keys:

Customer {CustomerKey, CustomerNo, ...}
Key {CustomerKey}, Key {CustomerNo}

Item {ItemKey, ItemNo, ...}
Key {ItemKey}, Key {ItemNo}

CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo}
Key {CustomerItemKey}, Key {CustomerKey, ItemKey}
CI[ItemKey] IN Item[ItemKey]
CI[CustomerKey] IN Customer[CustomerKey]

SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price}
Key {SOLineKey}, Key {SOKey, SOLineNo}
SOLine[CustomerItemKey] IN CI[CustomerItemKey]


A typical schema with natural keys

Customer {CustomerNo, ...}
Key {CustomerNo}

Item {ItemNo, ...}
Key {ItemNo}

CI {CustomerNo, ItemNo, CustomerItemNo}
KEY {CustomerNo, ItemNo}
CI[CustomerNo] IN Customer[CustomerNo]
CI[ItemNo] IN Item[ItemNo]

SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price}
SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo]


Now write a query that returns how many of item '12345' were sold to
customer '4321'

It should be obvious that with the natural keys, no joins are
necessary--it's just a simple select from SOLine since all of the
information is actually /in/ SOLine; whereas, with the artifical keys,
several joins are required because in order to query by item number and
customer number, SOLine must be joined to CI which must then be joined to
Customer and Item.

 

[Bob Badour]

(e-mail address removed) wrote in
m:


What if there's more than one application built on top of the
database?

I, too, agree that one should put as much of the data logic in the
back end as possible.

However, that doesn't mean I use natural keys very often. I'm
definitely opposed to compound keys for any table whose PK will be a
foreign key in another table. It causes myriad problems of all sorts
(been there, done that), and despite its being theoretically
correct, just doesn't work well in practice.

Reference is one issue that increases the tradeoff importance of
simplicity relative to the other design criteria.

Just consider one scenario:

You need to build criteria for a query-by-form interface. That means
that to query on the PK of a table with a compound PK, you end up
needing to have multiple fields in your WHERE clause. And if you're
querying multiple records in the table with the compound PK, you'll
need a complex nested OR in your WHERE clause.

I know perfectly well that theoretically speaking you're not
supposed to let your application drive the design of your schema,
but this is a case where common sense tells me that following theory
leads to enormously difficult application logic problems.

I have yet to see any evidence from you to suggest you know the first
thing about theory. Perhaps you should strive to learn a little more
about it before blathering on about it.

Natural keys are great for tables with a single-column natural PK.

A natural key is neither more nor less than a familiar surrogate.

Otherwise, surrogate keys make building an application substantially
easier.

I disagree. Since all keys are fundamentally the same thing, surrogacy
is irrelevant. The design criteria for keys are: uniqueness,
irreducibility, simplicity, stability and familiarity (in no particular
order.)

And, BTW, I would, of course, advocate that any natural key that is
not used as the PK should naturally have a unique index on it.

You confuse physical and logical issues. One should declare all logical
constraints regardless of the indexes used.

And any natural key that can't have a unique index (because some
fields need to be Null) was never a candidate for PK in the first
place, and would have to have had uniqueness enforced in the
application in some fashion anyway.

One would have to be an idiot to design anything allowing NULL in the
first place.

 

[James A. Fortune]

The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs written in
COBOL, Java, or anything in between accessing a single Oracle database on
the one hand. On the other hand, if you are a developer creating a self
contained MS Access database cum application (tables, queries, forms,
reports, modules, etc.) all in one file, the same issues arise, but they
are resolved quite differently.

I'm not saying either one is "right" or "wrong". I'm just suggesting why an
objection that makes perfect sense to you and me might be lost on the MS
Access community.

I am only speaking for myself. I may be the only Access programmer on
the planet who validates input the way I do in code. I am not unaware
of the desirability of enforcing constraints at the table level. My
reluctance to depend on error trapping is shared by very few, if any,
Access developers. Those who judge my unorthodox style choices as
ignorant or incorrect presume much.

For example, in "Joe Celko's SQL PROGRAMMING STYLE:

Q: Couldn't a natural compound key become very long?

A1: So what? This is the 21st century, and we have much better
computers than we did in the 1950s when key size was a real physical
issue. What is funny to me is the number of idiots who replace a
natural two- or three- integer compound key with a huge GUID, which no
human being or other system can possibly understand, because they think
it will be faster and easy to program.

....

The auto-numbering features are a holdover from the early SQLs, which
were based on contiguous storage file systems. The data was kept in
physically contiguous disk pages, in physically contiguous rows, made up
of physically contiguous columns. In short, just like a deck of
punchcards or a magnetic tape. Most programmers still carry that mental
model, too.

The most pathetic part is that many here even parrot his (incorrect)
reasoning about my reasoning. I submit that they, not I, are succumbing
to subtle psychological effects.

James A. Fortune
(e-mail address removed)

 

[David Cressey]

This remind me of the discussion twenty five years ago between relational
databases (like SQL-Server) and non-relational databases (like ISAM
databases, dBase, etc.). It has always been right to say that for simple
queries, non-relational databases are faster than relational databases.
However, nowadays, non-relational databases have (practically) vanished
because of their inherent slowness when the queries become more and more
complicated.

I don't believe the above is correct. It's still the case that a graph
based DBMS
like IMS can otperform an SQL based DBMS for queries that were anticipated
at the time the graph was designed. This is not a function of the
complexity of the query. It's a function of the relationship between the
path the query must take through the data and the paths that are made
blindingly fast by the graph set up at stata store time.

Essentially, an index gives and SQL engine exactly what it needs to locate
table rows: pointers to table rows.

Where the SQL DBMS (and the databases built using it) are superior to a
graph based DBMS (and the databases built using it) is flexibility. When
an unanticipated query occurs in a graph based system, the DBMS is reduced
to doing a brute force search (if it can even do that much). Either that or
the database designer must redesign the database, unload and reload all the
data, and revise any application programs that were dependent on the old
structure. That's usually prohibitively expensive.

By contrast the SQL DBMS can generally build an in memory index when needed,
or a new index can be built by the database designer without unloading any
data or requiring any revisions to programs.

This is exactly the same situation with the possibility of accelerating a
query by using a natural key: you are accelerating simple queries that are
already light and fast but on the opposite side, you are slowing down
complexe queries that are already big and slow. Not sure if going this way
is really advantageous.

I disagree.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"James A. Fortune" <[email protected]> wrote in messagenews:%

Access programmers use forms to interact with the data. If I follow
Jamie's advice and constrain the data at both the table level and in
code,
then your points make more sense. Right now, they're just arguments for
me not to constrain the data at the table level because the reasons you
gave might make natural keys preferable in that situation .

Well, that's just dumb. Checks in code can reduce database round-trips,
and
therefore can improve performance, but are not and cannot be a substitute
for constraints on the tables. It is the constraints on the tables that
keeps garbage out of the database.

If the users only access the tables through forms, conforming to best
practices in Access, how are they going to get garbage into the
tables? Now if you're trying to keep Jamie and his Excel SQL out of
your database, that's another story .
<<<<<

There can be several forms that access the same table, so you would have
to duplicate the code behind each form that accesses a table, or you can
get garbage into the database.

* Referencing an artificial key in a child table can complicates
queries - and not just with a longer restrict clause, but with a whole
extra join that may well have been unrequired if a natural key had
been used.

I don't agree with that point. The child table can contain the
AutoNumber
primary key from the main table as a foreign key if desired. I don't
see
how using the natural key fields requires less joins than that. Maybe
an
example would help me understand what you mean.

An extra join may be needed if the natural key from the parent table is
used
in a restrict clause. If all you have is the artificial key from the
parent
table, then you have to join in order to access the natural key columns.
With natural keys, the natural key values from the parent table also
appear
in the child table, so there isn't any need to join. Bottom line: joins
of
artificial keys are typically faster than joins of natural keys due to
the
size of the comparands, but with natural keys, fewer joins may be
needed..

If you're planning on using a natural key column in the child table as
part of a join then doesn't it make sense to include that field in the
child table?

Still waiting...


A typical schema with artificial keys:

Customer {CustomerKey, CustomerNo, ...}
Key {CustomerKey}, Key {CustomerNo}

Item {ItemKey, ItemNo, ...}
Key {ItemKey}, Key {ItemNo}

CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo}
Key {CustomerItemKey}, Key {CustomerKey, ItemKey}
CI[ItemKey] IN Item[ItemKey]
CI[CustomerKey] IN Customer[CustomerKey]

SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price}
Key {SOLineKey}, Key {SOKey, SOLineNo}
SOLine[CustomerItemKey] IN CI[CustomerItemKey]


A typical schema with natural keys

Customer {CustomerNo, ...}
Key {CustomerNo}

Item {ItemNo, ...}
Key {ItemNo}

CI {CustomerNo, ItemNo, CustomerItemNo}
KEY {CustomerNo, ItemNo}
CI[CustomerNo] IN Customer[CustomerNo]
CI[ItemNo] IN Item[ItemNo]

SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price}
SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo]


Now write a query that returns how many of item '12345' were sold to
customer '4321'

It should be obvious that with the natural keys, no joins are
necessary--it's just a simple select from SOLine since all of the
information is actually /in/ SOLine; whereas, with the artifical keys,
several joins are required because in order to query by item number and
customer number, SOLine must be joined to CI which must then be joined to
Customer and Item.

 

[David Cressey]

Whenever I've created junction tables [sic] in the past, I always made

the PK of the junction table the combined pks from the two other tables..
<<

I wish I knew where people got the term "junction table" when they
mean a table that models a relationship . I guess if you are still
thinking in terms PK-FK being pointer chains and structure and not
relational, then you would borrow old terminology from Network
Databases. And bring that mindset with you, too.

It probably came from the term "junction record" in CODASYL databases, where
a record can be a member of two sets, in order to enable a many-to-many
connection between
the owners of the record in the two sets.

Much of the early (Bowdlerized) description of relational databases was
written with reference back to the (at the time) more familiar CODASYL
databases.

 

[Bob Badour]

I don't believe the above is correct. It's still the case that a graph
based DBMS
like IMS can otperform an SQL based DBMS for queries that were anticipated
at the time the graph was designed. This is not a function of the
complexity of the query.

I, of course, disagree. A graph based dbms has no performance advantage
over a relational dbms because a relational dbms may use exactly the
same physical structures.

[snip]

 

[Brian Selzer]

This remind me of the discussion twenty five years ago between relational
databases (like SQL-Server) and non-relational databases (like ISAM
databases, dBase, etc.). It has always been right to say that for simple
queries, non-relational databases are faster than relational databases.
However, nowadays, non-relational databases have (practically) vanished
because of their inherent slowness when the queries become more and more
complicated.

This is exactly the same situation with the possibility of accelerating a
query by using a natural key: you are accelerating simple queries that are
already light and fast but on the opposite side, you are slowing down
complexe queries that are already big and slow. Not sure if going this
way is really advantageous.

I don't think it's exactly the same situation. As long as the data resides
in the same table, it is possible to create several indexes over several
columns in order to improve query performance--including join performance.
It's a lot harder to do that when the data is spread out over several
tables, as can be seen from the artificial key example. (You could use
indexed views, perhaps, if you know ahead of time which queries will be
run.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

"James A. Fortune" <[email protected]> wrote in messagenews:%

Access programmers use forms to interact with the data. If I follow
Jamie's advice and constrain the data at both the table level and in
code,
then your points make more sense. Right now, they're just arguments
for
me not to constrain the data at the table level because the reasons
you
gave might make natural keys preferable in that situation .

Well, that's just dumb. Checks in code can reduce database round-trips,
and
therefore can improve performance, but are not and cannot be a
substitute
for constraints on the tables. It is the constraints on the tables that
keeps garbage out of the database.

If the users only access the tables through forms, conforming to best
practices in Access, how are they going to get garbage into the
tables? Now if you're trying to keep Jamie and his Excel SQL out of
your database, that's another story .
<<<<<

There can be several forms that access the same table, so you would have
to duplicate the code behind each form that accesses a table, or you can
get garbage into the database.

* Referencing an artificial key in a child table can complicates
queries - and not just with a longer restrict clause, but with a
whole
extra join that may well have been unrequired if a natural key had
been used.

I don't agree with that point. The child table can contain the
AutoNumber
primary key from the main table as a foreign key if desired. I don't
see
how using the natural key fields requires less joins than that. Maybe
an
example would help me understand what you mean.

An extra join may be needed if the natural key from the parent table is
used
in a restrict clause. If all you have is the artificial key from the
parent
table, then you have to join in order to access the natural key columns.
With natural keys, the natural key values from the parent table also
appear
in the child table, so there isn't any need to join. Bottom line: joins
of
artificial keys are typically faster than joins of natural keys due to
the
size of the comparands, but with natural keys, fewer joins may be
needed..

If you're planning on using a natural key column in the child table as
part of a join then doesn't it make sense to include that field in the
child table?

Still waiting...


A typical schema with artificial keys:

Customer {CustomerKey, CustomerNo, ...}
Key {CustomerKey}, Key {CustomerNo}

Item {ItemKey, ItemNo, ...}
Key {ItemKey}, Key {ItemNo}

CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo}
Key {CustomerItemKey}, Key {CustomerKey, ItemKey}
CI[ItemKey] IN Item[ItemKey]
CI[CustomerKey] IN Customer[CustomerKey]

SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price}
Key {SOLineKey}, Key {SOKey, SOLineNo}
SOLine[CustomerItemKey] IN CI[CustomerItemKey]


A typical schema with natural keys

Customer {CustomerNo, ...}
Key {CustomerNo}

Item {ItemNo, ...}
Key {ItemNo}

CI {CustomerNo, ItemNo, CustomerItemNo}
KEY {CustomerNo, ItemNo}
CI[CustomerNo] IN Customer[CustomerNo]
CI[ItemNo] IN Item[ItemNo]

SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price}
SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo]


Now write a query that returns how many of item '12345' were sold to
customer '4321'

It should be obvious that with the natural keys, no joins are
necessary--it's just a simple select from SOLine since all of the
information is actually /in/ SOLine; whereas, with the artifical keys,
several joins are required because in order to query by item number and
customer number, SOLine must be joined to CI which must then be joined to
Customer and Item.

 

[Tony Toews [MVP]]

"Clearly the person is an idiot and is better off employed at a 7-11."
http://groups.google.com/group/microsoft.public.access.conversion/msg/163c373772d787d3

"One idiot student once emailed me"
http://groups.google.com/group/microsoft.public.access.modulesdaovba/msg/4d5e152c11a9e0a3

"tell him he's an idiot"
http://groups.google.com/group/microsoft.public.access/msg/31c0abc7b367656d

"This person is an idiot."
http://groups.google.com/group/microsoft.public.access/msg/6fedf72a4680cf25

"But no, some idiot manager at Mickeysoft..."
http://groups.google.com/group/microsoft.public.access.forms/msg/222d3ed992e9a2ec

<chuckle> You almost got me there. However I was ridiculuing other people who
weren't part of the conversation. Not much of an excuse.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

So now they're good reasons? In your earlier post, you said they weren't
good reasons. Can't you make up your mind? You also haven't stated your
reasons. How can I like them or not like them? I don't know them!

They are good reasons for me and, in my opinion, for many or all other users of
Access. I flippantly stated "no particular good reason" as I was thinking about
some of the regulars in the comp.databases.theor newsgroup and what I assumed would
be there viewpoint.

I was not speaking of corruption due to disk failures; I was instead
referring to permitting garbage into the database due to the misuse of
auto-number primary keys.

Ok, not quite sure what you mean by this. Presumably because a unique index wasn't
declared on other fields such as, for a parts table, a part number. Which can be
duplicated by multiple manufacturers so even there that's not quite a good example.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I always use an autonumber PK and a unique index set on the two FK fields [sic]. Why? No particular good reason. One of my database rules is that all tables have an autonumber primary key [sic]. <<

You add redundancy to a schema and never thought about it?

I have and I'm quite comfortable with it.

If you had
gone thru the Normalization process, this would stick out as a
fundamental design error immediately. You have not been writing SQL;
you are faking a sequential file system in SQL and even say "field"
instead of "column" -- huge conceptual and implementation differences!
Ok.

Now if I was to have a child table [sic: that terms comes from Network DBs; did you used to program in IMS?] from the junction table [sic: did you mean a table that models a relationship among many entities? Or a multi-way pointer structure as in an Network DB?] then I would absolutely use a autonumber primary key [sic: it cannot be a key by definition] for ease of use when designing queries, forms and reports. <<

Never woked in IMS or Network DBs. These are common terminology in the Access world
so that's what I use.

I hope that you are not designing forms and reports in the database.

You've never used Access then?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Yes. Because Access developers have no need to separate data access
from user interface from business rules. Just bind a form to a
recordsource, sprinkle some VBA code in a few events and run with it.

I sure would like to have that middle tier with business rules and also have the
power of Access and RAD.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I am only speaking for myself. I may be the only Access programmer on
the planet who validates input the way I do in code.

Not sure exactly what mean mean by that statement but I do a lot of validating on
input as well. And I very much try to keep one form updating one table so as to
not have to duplicate such data.

I'd very much like to see a business rules layer implemented that worked well within
Access. However I rather much doubt we'll ever see it given that Microsoft is
focusing their attentions on the office information worker rather than the developer.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jon Heggland]

Quoth David W. Fenton:

Any database engine can have the schema defined in a way that will
allow duplicates.

Nonsense. Any /SQL-based/ database engine, perhaps.

 

[David Cressey]

This is the big problem with natural keys. When the theory was first
elaborated, the point was that a natural key never change its value. That
was the point that was making the choice of a natural key on par with the
use of a surrogate key as the primary key of a table. However, like anyone
have discovered with experience, a natural key can change its value under a
set of various circonstances. One could argue that if a key can change its
value, than it's not a natural key but as you know, this argument bring
nothing in regard to help you choosing a natural key.

This is a specific example of a syndrome that I described more generally:
the mismanagement of natural keys.

If a key is "natural", it is managed, if at all, beyond the scope of the
DBMS. It might be managed at the application layer, or it might be managed
by people, or it might indeed be unmanaged data, like sunspots.

When it is managed by other people, it is subject to mismanagement.
Changing values that ought to be immutable is one of many ways that natural
keys can be mismanaged.

As I said before, I prefer to use natural keys where ever possible. If
that's not possible due to mismanagement of the natural keys, I'll use
synthetic keys.

 

[David Cressey]

(quote)
I don't claim to know more about db theory than the cdt regulars, or
to have more experience than the many practioners who have contributed
to this thread.

I do know that the debate over relationship representations has been
going on for years, neither side giving any ground. Some aspects of it
are never addressed. For example, making the two foreign keys in a
junction table a composite PRIMARY key prohibits NULL values from
either key, but declaring a separate surrogate primary key together
with a UNIQUE constraint on the two-foreign-key-composite-key does
allow NULL values for either (or both!). This can be useful for
representing unrelated entity tuples, either childless parents or
orphans.

(unquote)

In the case of a junction table, this point is moot.

If both FK values are NULL, the entire row of the junction table can be
omitted with no loss of information.

Childless parents will exist in the table that contains parents, where the
id of the parent is not an FK, but a PK. Same pattern for orphans. The two
tables can even be the same table.

 

[David Cressey]

(e-mail address removed) wrote in
m:


What if there's more than one application built on top of the
database?

I believe this is a moot point when it comes to MS Access. The app and the
database are all stored together in Access. There is, by definition, only
one Access.

Access regulars, feel free to correct this if it's wrong.

 

[David Cressey]

to see if there is a different number Ah! <<

In the US, you get a UPC/EAN code and then a batch number with booze.
The closest thing to a serial number is your cash register receipt.

This is the "cat food problem" in a nutshell. (Or, in this discussion, the
"gin bottle" problem.)
The problem has been known as the "cat food" problem for years and years.

Items on a cash register tape represent entities with no identity at the
individual level, only at the batch or product level. You therefore can't
tell whether two bottles of gin were checked out, or whether one bottle was
scanned twice.