Relationship feature/bug/accident

[Peter Danes]

I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of course,
maybe I'm just being dumb.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected - books,
magazines and journals. There is one primary table for all the data which is
common to each type (archive number, physical location, date acquired...)
and a separate sub-table for each of the three types, containing data which
is unique to the type (books have an author, magazines do not...)

Originally I had a primary key in the main table using an autonumber field
and primary keys in the subtables using a compound key of consisting of the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in three
one-to-many relationships and everything looked great.

Recently I realized that the autonumber fields in the subtables don't really
do anything for me, since each subtable can have either one or no records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written to
date still worked.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put it
back as anything other than a one-to-one relationship. I don't want a strict
one-to-one, because for every main record, two of the three tables will have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a false
structure which is then removed again doesn't strike me as good practice.

Is this correct behavior for the relationship window or have I discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow? I
assumed that my form would simply create one record on the many side of the
join and not address the fact that the structure could allow more than one.
Or have I made a fundamental mistake in the design somewhere?

Pete

P.S. I'll be out of the office until the weekend, at least.

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[Steve Schapel]

Pete,

These are one-to-one relationships. Simple as that. What you have done
is entirely appropriate.

I don't quite understand your comment "I don't want a strict one-to-one,
because for every main record, two of the three tables will have no
entry." The fact that two of the three "subtables" will not contain a
record corresponding with any given main table record, is really immaterial.

 

[John Vinson]

I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of course,
maybe I'm just being dumb.

Well, true Subclassing - which you're using, and a classic case of it
- is somewhat tricky. You've got it almost right though.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected - books,
magazines and journals. There is one primary table for all the data which is
common to each type (archive number, physical location, date acquired...)
and a separate sub-table for each of the three types, containing data which
is unique to the type (books have an author, magazines do not...)

Subclassing in its classic form: an Entity with subclasses, where all
subclasses have some attributes in common but each subclass has other
attributes unique to that class. Strictly speaking, the term "one to
one" is shorthand for "One to (zero or one)", since the child table
Magazines will not contain any record where the main table refers to a
Book.

Originally I had a primary key in the main table using an autonumber field
and primary keys in the subtables using a compound key of consisting of the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in three
one-to-many relationships and everything looked great.

YOu don't need either the second autonumber nor the standard
doubleprecision number - just a Long Integer foreign key/primary key
linked to the main table.

Recently I realized that the autonumber fields in the subtables don't really
do anything for me, since each subtable can have either one or no records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written to
date still worked.

It should be one to one: you should be linking the main table
Autonumber to the single-field long integer Primary Key of the related
table.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put it
back as anything other than a one-to-one relationship. I don't want a strict
one-to-one, because for every main record, two of the three tables will have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a false
structure which is then removed again doesn't strike me as good practice.

Nothing fake about it; you're doing exactly the right thing.

Is this correct behavior for the relationship window or have I discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow? I
assumed that my form would simply create one record on the many side of the
join and not address the fact that the structure could allow more than one.
Or have I made a fundamental mistake in the design somewhere?

Well... no. There will not be any records automagically created. The
relationship doesn't *create* any records; it merely prevents you from
creating invalid records.

You presumably will have three subforms, one for Books, one for
Magazines, and one for Journals.

To be squeaky clean, you may need some VBA code to ensure that you can
only create *one* child record in one of the tables (i.e. you should
not have any main table records which have both a Book and a Journal
related record). I don't know any good way to prevent this just using
the relationships window (in SQL/Server you could use a constraint,
and you might be able to do so in the latest versions of Access).

John W. Vinson[MVP]

 

[Peter Danes]

Hello Steve,

thank you for your comments.

Perhaps I don't correctly understand the one-to-one relationship. (I've
never used it for anything.) I thought that a one-to-one meant that there
had to be exactly one record in each table on each side of the relationship.
Although, now that I think about it, that would sort of mean that you could
never add anything, since during each add, the 'other' table wouldn't yet
have the corresponding record, so the relationship would never permit any
adds.

Do you mean, then, that a one-to-one relationship can have one record on one
side and nothing on the other? Is there a no 'master' and 'slave'
arrangement like in the one-to-many? Can either side have one and the other
none?

Sorry to be so obtuse, but as I said, I've never used it for anything and
most books say it's rarely appropriate, that such things generally belong in
one table. Maybe this is one of the exceptions.

Pete

 

[Peter Danes]

Hello John,

thank you for the explanations. It apppears that I simply had my head stuck
in the fertilizer generator again.

Well, true Subclassing - which you're using, and a classic case of it
- is somewhat tricky. You've got it almost right though.

I've seen the term subclassing here and there, but never knew what it meant
and it was always in a context that made it sound pretty involved.

Strictly speaking, the term "one to
one" is shorthand for "One to (zero or one)", since the child table
Magazines will not contain any record where the main table refers to a
Book.

Yes, and I should have figured that out myself, since a simultaneous add to
two tables is impossible. (Or is it? A join query...Still, even SQL at some
level has to put a record into one table and then into the other.)

YOu don't need either the second autonumber nor the standard
doubleprecision number - just a Long Integer foreign key/primary key
linked to the main table.

Sorry about the confusion, I did get that one right, actually. I'm using a
Czech OS and I mistranslated the data type in my original post.

Well... no. There will not be any records automagically created. The
relationship doesn't *create* any records; it merely prevents you from
creating invalid records.

I understand that - my wording there was a little awkward. Much of my
confusion came from Access leaving the lines in the relationship window as
one-to-many even after I had altered the subtables. Is that an Access
mistake? Does changing the tables and not changing the relationships leave
the DB in some indeterminate 'between' state, or does Access change the
relationship to function correctly and simply neglect to update the
relationship window? Or does it leave them displayed that way deliberately
for some reason?

You presumably will have three subforms, one for Books, one for
Magazines, and one for Journals.

Exactly. One main form with three subforms and some other doodads which are
not pertinent to this discussion. The subforms display nothing at all when
there is no record, which I find a little aggressive. My preference would be
to have the fields displayed but disabled; it seems more in line with
standard Windows functionality, but it's not a big enough issue for me to
waste time circumventing it. And at least the user will have no possible
confusion with which subform to fill out.

To be squeaky clean, you may need some VBA code to ensure that you can
only create *one* child record in one of the tables (i.e. you should
not have any main table records which have both a Book and a Journal
related record).

Yes, I have that currently under construction and no problems. That sort of
stuff is well within my capabilities.

I don't know any good way to prevent this just using
the relationships window (in SQL/Server you could use a constraint,
and you might be able to do so in the latest versions of Access).

This is a stand-alone single-user app built with A2K, but I can handle it
with VBA just fine.

I think I'll build a small test DB to experiment with to make sure I've got
it all clear. Thank you for explaining it so well.

Pete

 

[Douglas J. Steele]

You've got one table with a Primary Key on it, and the other table with a
Foreign Key pointing to that first record. That means that you still need to
have a record in the first table before you can have one in the second
table: you can't have only a record in the second table.

 

[Steve Schapel]

Pete,

Yes, there is a master/slave arrangenment with defined one-to-one
relationships where Referential Integrity is enforced. If you do this
isn the Relationships window, by dragging from a field in one table to
the field in the other, the "line of command" is determined by the
direction of the drag, if you see what I mean. In your case, if you
drag from the Autonumber primary key field of the main table to the
Number (Long) field in a subtable, and then Create the relationship with
Referential Integrity enforced, well then you will be able to enter
whatever records you want in the main table, without a corresponding
record in the subtable. But you won't be allowed to enter a record in
the subtable without a related record in the main table.

If you did not enforce RI, then you would be able to enter records in
either table without a corresponding record in the other... but I can't
think of a case where this would be applicable... certainly not in your
database.

As regards "most books say it's rarely appropriate", this is true. And
the scenario you have described, sometimes called a sub-classing design,
is one of those situations where it is appropriate.

 

[Peter Danes]

Hello Steve,

You're right, I just tried it. If I drag from the main table to the sub
table, it works fine. If I drag from the sub table to the main table, it
refuses to enforce RI. What determines the direction? Is it that one has an
autonumber PK field and the other simply a long integer PK?

I just tried linking some other key fields to see what would happen and got
1-N, 1-1 and Undetermined for various combinations of fields, some key and
some not. I also tried it on two autonumber PK fields and it refused to
create the relationship at all. I only did about a dozen, not enough to get
a real sense of what is determining the result. Do you know of any websites
that go into detail on this? I have Getz, Litwin and Gilbert's 2-volume
developer's handbook, but I don't recall seeing this in there anywhere.

And the 1-1 line in the relationship window doesn't seem to give any
indication of the controlling direction once the relationship is
established. Is there some way to tell by looking, or do you have to dig
into the tabledefs and know what attributes of the fields to look for?

Pete

 

[Peter Danes]

Hello Doug,

thank you for your comments. I obviously didn't understand the 1-1
relationship very well, but I think I'm starting to get a grip on it. It
seems simple on the surface, but there is more to it than I first thought.

Pete

 

[John Vinson]

Hello John,

thank you for the explanations.

Additional comments inline...

I understand that - my wording there was a little awkward. Much of my
confusion came from Access leaving the lines in the relationship window as
one-to-many even after I had altered the subtables. Is that an Access
mistake? Does changing the tables and not changing the relationships leave
the DB in some indeterminate 'between' state, or does Access change the
relationship to function correctly and simply neglect to update the
relationship window? Or does it leave them displayed that way deliberately
for some reason?

I'd say it's just a bug, or an overlooked feature. The relationship
window doesn't get reconstructed when the table structure changes, it
seems. Whether the relationship actually changes or not is sort of
irrelevant - if there is a unique Index on the foreign key, you can't
add a second record anyhow; so regardless of what the relationship
window shows, it's effectively one to one.

Exactly. One main form with three subforms and some other doodads which are
not pertinent to this discussion. The subforms display nothing at all when
there is no record, which I find a little aggressive. My preference would be
to have the fields displayed but disabled; it seems more in line with
standard Windows functionality, but it's not a big enough issue for me to
waste time circumventing it. And at least the user will have no possible
confusion with which subform to fill out.

A blank subform sounds suspiciously like one for which the
Recordsource is not updateable. If you've changed the table structure
and the relationships, you probably need to redefine each Subform's
Recordsource to point to the (newly redesigned) tables; also check the
Master/Child Link Fields. My guess is that the form was not updated to
reflect the changed table structure.

Yes, I have that currently under construction and no problems. That sort of
stuff is well within my capabilities.

Excellent.
John W. Vinson[MVP]

 

[Albert D.Kallal]

Note that ms-access "knows" if it is a one to one, or a one to "many" based
on the fact that the foreign key in the child table has a unique index
setting (if you only allow ONE value of the same type...then it has to be a
one to one. If you remove the duplicates allowed, then obviously you can
have "many").

By the way, you are correct in that the child tables do NOT need a
autonumber primary key. However, you REALLY REALLY REALLY REALLY shold put
that autonumber field in. The reason for this is if later on you do want to
relate a table to that child table, then you can without having to add that
autonumber collum. Furhter, to get the last reocrd of a child table is VERY
common question (last inoive, last order, last whatever!!). If you do NOT
add a autonumber field, then grabbing the last invoice for a custom canbe
difficlet. Adding a autonumber makes that reocrd UNIQUE, and even if you
don't relatae data to that table, you should always as a rule have a UNIQUE
way of working with a particlar reocrd. (this will bite you when you got
code, or just about anything that needs to manipulate those child reocrds.
So, a primary key is not only for reoanlships, but just for you to keep
yourself sane, and allow yo to write code etc. that can work with a SINGLE
reocrd in a table. So, don't bother to remove those autonubmer collums, as
they will come to good use in the future.

For example: If you have a autonumber, then to get the custoemrs last
inoivce you can use:

tblCustomer tblInvoice

SELECT ContactID, CompanyName , tblInvoice.InvoiceDate, tblInvoice.Pamount
FROM tblCustomer LEFT JOIN tblInvoice ON ContactID = tblInvoice.contact_id

Contact ID CompanyName InvoiceDate InvoiceAmount
1 AppleBee Jan 1/2005 $33.44
1 AppleBee Jan 1/2005 $45.00
2 Staples
3 Office Depot Dec 12/ 2005


If you look at the above, applebee has two invoices on the same day, but we
want the LAST invoice. So, we go:

SELECT ContactID, CompanyName, tblInvoice.InvoiceDate,
tblInvoice.InvoiceAmount
FROM tblCustomer LEFT JOIN tblInvoice ON tblCustomer.ContactID =
tblInvoice.contact_id
WHERE ((tblInvoice.ID)=(select top 1 id from tblInvoice where contact_id =
tblCustomer.contactID order by InvoiceDate desc, id desc)));

You will note the "top 1" to grab the LAST inoivce, but if you don't have a
autonumber, then that statement will return TWO records for the top 1.


As for which direction to draw, and how to setup relationships? It is very
important
how you do this.

I going to re-post a message of mine on this...

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

Thus, if you allow a main reocrd to be added, and NOT have to have a child
reocrd (one, or many), then you should set your relsonships to be a left
join. About 80-95% of my relonaships are left joins......

 

[Steve Schapel]

Peter,

You're right, I just tried it. If I drag from the main table to the sub
table, it works fine. If I drag from the sub table to the main table, it
refuses to enforce RI.

I would assume that this is simply because existing data does not comply
with the RI requirement, i.e. you already have a record in the main
table for which there is no corresponding record in the subtable.

What determines the direction? Is it that one has an
autonumber PK field and the other simply a long integer PK?

No, this shouldn't matter.

I just tried linking some other key fields to see what would happen and got
1-N, 1-1 and Undetermined for various combinations of fields, some key and
some not.

If you have a unique index on a field in one table, and relating it to
another table via a field that is not unique, the relationship will be
assumed to be one-to-many. If you are relating two tables via fields
that are unique in both tables, as you are in your original examples
(they are both primary key fields, and therefore automatically unique),
the relationship will be assumed to be one-to-one.

I also tried it on two autonumber PK fields and it refused to
create the relationship at all.

No, it doesn't make sense to have a relationship based on 2 Autonumber
fields, since the value entered into the related table can not be
determined by the value entered into the primary table.

And the 1-1 line in the relationship window doesn't seem to give any
indication of the controlling direction once the relationship is
established. Is there some way to tell by looking, or do you have to dig
into the tabledefs and know what attributes of the fields to look for?

If you double-click on the join line to open the relationships
properties, you will see the related fields listed in the top panel, and
this will indicate which is regarded as the main table and which the
related table.

 

[Peter Danes]

Hello Albert,

sorry about the delayed response, I just got back home.

Thank you for your thoughts on my problem. I'm certain that you're correct
about the need for a primary key, but if you look again at my original post,
I believe that I already have a perfectly functional one.

Every record in the main table has exactly one corresponding record in
exactly one of the subtables, and that one subrecord has as a primary key
the autonumber generated as a primary key in the main table, copied from the
main table when the subrecord is created. So each subtable contains either
nothing or exactly -one- subrecord, which has the same ID for its primary
key as the autonumber generated in the main table for the master record.
Since this ID is unique to that one subrecord, I believe that it is a valid
primary key, even though not generated as an autonumber in *that* table.

The issue of multiple subrecords and needing the last one does not arise
here. I know that requirements can change over time, but this is so specific
to the design that if something arose where I needed multiple subrecords, it
would pretty much mean a complete redesign anyway.

If you still think I am wrong and need another autonumber field, I'd be
pleased to hear more.

Pete

 

[Peter Danes]

Hello John,

sorry about the delayed response, I just got back home.

I'd say it's just a bug, or an overlooked feature. The relationship
window doesn't get reconstructed when the table structure changes, it
seems. Whether the relationship actually changes or not is sort of
irrelevant - if there is a unique Index on the foreign key, you can't
add a second record anyhow; so regardless of what the relationship
window shows, it's effectively one to one.

I see. It would be nice if the window kept up with the table design, or at
least had a "Renew" button. And if you add or delete fields in a table, the
window does reflect those changes. But I guess it's a fairly minor point.

A blank subform sounds suspiciously like one for which the
Recordsource is not updateable. If you've changed the table structure
and the relationships, you probably need to redefine each Subform's
Recordsource to point to the (newly redesigned) tables; also check the
Master/Child Link Fields. My guess is that the form was not updated to
reflect the changed table structure.

No, I don't think so. I read these groups a LOT, in fact, they are my
primary source of information for troubleshooting, so you can probably guess
how often I see your name and your posts. You're one of the last people I'd
want to argue with, but this seems to be legitimate behavior. If you google
the Access archives for 'blank subform', you'll find quite a number of
references to this. And it worked that way even when I had the tables truly
one-to-many. The gist seems to be this: when a subform's recordset contains
NO records, AND the properties sheet has adding records disallowed, the
entire subform is not displayed. The subform's container on the main form
stays, but all the subform controls are invisible. It makes a certain amount
of cockeyed sense, I suppose. If there are no records to show and you can't
add any new ones, it's unlikely that you have much reason to work with the
subform. But it also blocks access to any command buttons and such that you
may want even if you have no records and no adds allowed.

I have adds disallowed because I don't want someone to make a mess, as you
pointed out could happen, by adding, for instance, both a book and journal
subrecord for one master record. The easiest way I have thought of to do
this is to not allow adds on the subforms. I create the proper subrecord in
the proper subtable based on what letter the user enters for record type
("B" book, "M" magazine, "J" journal) with all fields blank except the
primary key, requery to make the proper subform visible now that it has a
record and allow the user to finish entering data in the proper subform. To
Access, that is then updating an existing record, but to the user, it
appears to be a seamless continuation of the data entry process.

Pete

 

[Tim Ferguson]

I hate to disagree with a MVP, Albert, but may I chip in a little bit here?

By the way, you are correct in that the child tables do NOT need a
autonumber primary key.

As I understand it, the set up is something like

Publications (*PubsID, ArchiveNum, PhysLocation, etc)

Books(*PubsID, Author, House, etc)
FK (PubsID) references Publications

Magazines(*PubsID, IssuesPerYear, etc)
FK (PubsID) references Publications

However, you REALLY REALLY REALLY REALLY shold
put that autonumber field in.

In other words,
Books(*BookNum, PubsID, Author, House, etc)
Unique (PubsID)
FK (PubsID) references Publications

Magazines(*MagazineNumber, PubsID, IssuesPerYear, etc)
Unique (PubsID)
FK (PubsID) references Publications

The reason for this is if later on you
do want to relate a table to that child table, then you can without
having to add that autonumber collum.

Surely, this is just the best reason for _not_ adding another ID column.
Say there was a need for another table of Translations for local
translations of magazines: you would presumably suggest a design like

Translations(*MagazineNumber, *LanguageCode, etc)
FK MagazineNumber references Magazines

(leaving aside for a minute the question of having a PK *TranslationID...),
while I would suggest something like this:

Translations(*PubsID, *LanguageCode, etc)
FK PubsID references Magazines

(note that the relationship is constrained to the Magazines table, not the
publications table)

Now, say there is a need for a query of PhysicalLocations for each
translation: the first variation would need a three-table join on
Translations -> Magazines -> Publications, while the second would need only
Translations and Publications. It's not an uncommon scenario, and in my
view, relegates the Magazines table to an (unneccesary) mapping function.

If you look at the above, applebee has two invoices on the
same day, but we want the LAST invoice. So, we go:

You don't really use autonumbers to provide a MostRecent function do you? I
refer to the (I think) seventh commandment:

Thou shalt not use Autonumber if the field is meant to have
meaning for thy users.

If you want to know the most recent invoice in one day, then you need a
time field to sort by. No?

Best wishes


Tim F

 

[Albert D.Kallal]

You don't really use autonumbers to provide a MostRecent function do you?
I
refer to the (I think) seventh commandment:

Thou shalt not use Autonumber if the field is meant to have
meaning for thy users.

If you want to know the most recent invoice in one day, then you need a
time field to sort by. No?

An excellent answer. And, yes, a timestamp is a good solution.

However, one should point out that simply using a autonumber for order STILL
does not give the actual numbers any meaning to the user. For example, we
can state that we are going to use autonumbers for a relation, but that does
not mean we spilled the beans, and told the user what we are using the
autonumber for!!! In other words, breaking silence in that we are using a
autonumber for something such as relationships does NOT break that above
rule. So, we can tell a user that we are going to use autonumbers for
relations, and that most certainly has a meaning to the user!!. So, the
above concept being explain is that the user never sees, or assigns an
actual meaning to a ACTUAL autonumber. So, using the autonumber for setting
the order no more breaks the above rule then using them for relationships.
As long as the user never writes down, or sees those autonubmers, .you are
free to use them as you see fit (to build relationships, or to set
order ---- and telling the user we are going to do this would not all of a
sudden mean we can't use the auotnumber for a relationship!). To split
hairs, the concept of "order" could be argued to have more meaning then the
concept of a relationship. (the reason being that a autonumber can be a
random number for relationships, but using the autonuber for order implies a
increasing value all the time, so your debate is somewhat correct in that
order does have meaning, but so does the concept of a relationship, but not
as much meaning!!).

As mentioned, a timestamp is a very good solution to retrieve the last
record However, it still needs pointing out that another good rule in
database designs is to ensure that ALL records have a unique identifier, or
a primary key. This concept of having a PK is NOT ONLY for building
relationships, but also to uniquely identify a record in a table. If we
start discussion about databases, and codds rules, you will find that these
scholars will quickly point out that a record in a table needs a Primary key
to allow one to accomplish a normalized database. (but, lets not even go
there..and again, a shrewd person could point out that a compound key
consisting of the foreign key + the timestamp could produce a primary key).

However, my point is that just because a child table don't seem to need a
primary key, adding one gives you the ability to restive the last record. It
gives you the ability to uniquely identify a record. And, for future
designs, and modifications, a new child table can be added, and you will
never have to worry if the parent table is missing a PK.

So, adding a timestamp might solve some of these problems, but consideration
needs to be given for other issues

So, I think it is just a bonus to always have a PK. And, of couse, this is
not a "rule", but simply a good suggeston on my part....

 

[Tim Ferguson]

An excellent answer. And, yes, a timestamp is a good solution.

However, one should point out that simply using a autonumber for order
STILL does not give the actual numbers any meaning to the user.

Fair point indeed: I'm still a bit unhappy about requiring autonumbers to
be always in order... although they are called incrementing, there are
too many situations when an AN will be created out of sequence... The
point at which the number is allocated will be (a) significant and (b)
probably provider-specific. For example:

Joe is having a lot of trouble making up his mind about his new laser
printer: the salesman has opened the invoice and is waiting to enter the
HP LaserCannon 34009 with 5000 page sheet feed. Meanwhile, Joe's
secretary nudges him and points to the empty cupboard, so he asks for
some copier paper to be put on a new order (and a new invoice) for same
day messenger deliver. Finally, he makes up his mind and goes for the
BudgetBrick MagicWriter with the built in WAN and cofee-maker.

Which invoice is the most recent? Local business rules may legitimately
choose either one, but it certainly should not be an accidental side
effect of SQL Server versus Jet architecture!

However, my point is that just because a child table don't seem to
need a primary key, adding one gives you the ability to restive the
last record. It gives you the ability to uniquely identify a record.

I would sooner stick needles in my eyes than recommend a table without a
primary key: my point about the subclassing/ one-to-one relationship
described was that the FK _is_ the PK (that should be the other way
round) and there is no need to specify a new one. I am now way too old to
get into arguments about substitute PKs in heirarchies of one-to-many
relations; but this is a somewhat different thing.

All the best


Tim F

 

[Dirk Goldgar]

I would sooner stick needles in my eyes than recommend a table
without a primary key: my point about the subclassing/ one-to-one
relationship described was that the FK _is_ the PK (that should be
the other way round) and there is no need to specify a new one. I am
now way too old to get into arguments about substitute PKs in
heirarchies of one-to-many relations; but this is a somewhat
different thing.

I'm with Tim on this one.

 

[Albert D.Kallal]

Ah, ok...if we are talking about a one to one relationship, then of course I
agree!!
(I kind of missed that point).

To be fair, my response was NOT in the context of a one to one relationship,
but a one to many.

For a one to one, without question we already have a PK in the child table
(that comes from the parent table), and thus one would NOT want a autonumber
pk for the child table......

(this issue was so obvious, that I did not even think it was being debated
!!!);.

If I wrongly suggested that one should put a autonumber in a child table
that is NOT the primary key, then my apologies, as I would not suggest that
for one second. (and,in re-reading my post, I mentioned autonumber, but NOT
pk - that is was not my intention!).

Since the original question is in this context, then I am guilty of
confusing here. However, I can assure you that my point is that you want a
PK in the table...and if you already got one...then adding a autonumber to
that table is just not a good idea at all!

In reading this, we all actually seem to be on the same channel, but I was
not 100% clear here. So, no, I would NOT suggest adding a autonmber field
UNLESS it is the PK (and, in the original post....the child table already
has a PK)......

I now see why you and Tim pointed this out, as it looked like I was
suggesting to add a autonumber field that was NOT the PK.....

So, great heads up here folks...and thanks!!!

 

[Tim Ferguson]

In reading this, we all actually seem to be on the same channel, but I
was not 100% clear here. So, no, I would NOT suggest adding a
autonmber field UNLESS it is the PK (and, in the original post....the
child table already has a PK)......

That makes us all in violent agreement then!

All the best

Tim F