Let me know what you think.

[jstew]

I just got done reading an article on the net about how to setup relationships. Basically it was saying that you should never make a P-key an autonumber and that you never link a parent/child table with the P-key. Visit this site and let me know if this is right. It seems like I've been wrong all along because I thought you were always supposed to link with P-keys. Anyways scroll down to the bottom of this site and it shows a Relationship diagram if you don't like to read. Thanks for the help!
jstew

http://www.blueclaw-db.com/tips_tricks.htm#Table_Design

 

[johnnymac43]

This question was addressed by several people (including myself) a few weeks ago in thread 181-462472. Take a look and see if it helps.

Cheers

John

 

[Darrylles]

Hi jstew,

Yet again, the hornets nest is disturbed. (Expect at least 50 posts for this!).

Some people will swear that you should never use autonumbers for PK (because they are not 'data', i.e. they are artificially introduced). The users never get to see these numbers - so what is the point when you can use 'pure' data to do the same job.

I was taught to create unique keys from 'existing' data (just like most of the people who post the following posts), but would never go back to using that method unless forced to. I'll stick to autonumber for PK and transport it across to other tables as FK's any day.

I'll promise you this: you'll pick up relational db design quicker, once understood - you'll design and develop quicker and you'll have less problems in the future.

Some people will say that it's an optimum way to save data using 'pure' data as PK's. However, this doesn't mean the same as 'it uses less data storage', nor does it mean that it is more efficient or quicker access time.

As with anything else, don't let ANYONE tell you there is only ONE correct way.

Remember that a lot of the arguments against autonumber are purely down to 'elitism', (which will of course be denied).

Best way for you to decide is to design a small RDB, using 2 different methods. Then create a couple of forms, queries and reports for each. You decide which you want to use from that.

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Darrylles]

Hi Johnny,

(Sorry - you got in whilst I was typing lol).

If you leave out the space between 'thread' and the number, then it displays as a link as:

thread181-462472

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[KornGeek]

I'm going to restate my opinion here (it's also stated a little differently in the other thread). Autonumber can be useful or problematic, depending on what you expect from it.
In a single-user database where you only need a unique number, Autonumber is wonderful. In a multiuser database or in a situation where the value matters to the user, Autonumber can be problematic.

It really depends on your usage. It's like a chainsaw. If you're cutting wood, it might be the best tool for you. If you're juggling, I would advise you to find something else. Then again, there are those who succesfully juggle chainsaws. It just goes to show you, there is an exception to every rule.

 

[Darrylles]

Hi,

Korn, single or multi-user - neither set should ever see an autonumber if you are responsible for that database. It is a means to uniquely identifying records - nothing else.
It is a 'behind-the-scenes' object in a forms-based application.

If you are supplying one user with a quick table to enter data, then ok, they will see the autonumber.
But how can you expect that 'user' to maintain data-integrity with a four part key - if autonumber is not used?

If you insist on letting 'users' view tables (and hence view the autonumber) then you must have a policy of teaching users relational database design - tell the IT director that.

If you need a user to see a 'key', then you create an artifical user key (using the autonumber concatenated perhaps), the autonumber DOES NOT exist from a user perspective (that is what needs to be understood for the autonumber to be effective).

Regards,

Darrylle






"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[johnnymac43]

Never say never! Sometimes there is no real alternative to showing a user the autonumber (and no logical artificial user key).

Consider the following scenario:

> Data is generated from a variety of sources and is written to a table with an autonumber ID. Thousands of new entries are added each day. There are hundreds for each possible identifying attribute.

> For various technical and/or business reasons it is not timestamped.

> There is no combination of data items to establish a data UID.

> Automatic verification and validation processes, based on expected value ranges for data items, are run against all new entries in the table.

> Reports of possible errors and/or indications of abnormal occurrences in the processes that generate the data are produced for manual investigation. Hundreds of new entries are reported each day.

> After investigation, a user locates some of the table entries and either amends them or marks them as 'abnormal'. Up to 10% of reported items are amended.

Question:

> What value can be used to uniquely identify a reported entry when it is to be updated??

I rest my case.

Cheers

John

 

[nathanstevenson]

Hi all,

Here is a specific case which I have discovered DRAMATICALLY benefits from autonumber usage. It is probably a case faced by many of us in the business - that of converting "flat" data stored in Excel files into relational data stored in an Access database. This is also true of timely additions to a database. e.g. monthly or weekly imports.

First, consider how you would weave a unique id INTO an Excel file - NOT easy by any means. In fact it took me 4 weeks to create a piece of code that does this properly. So now you have all your "keys" woven into your flat Excel data.

This means you can import to a staging table in Access, and then into the specific tables the data belongs in. Now if you want to ADD data to the Access DB, you have to somehow check to see if it exists in table a, then reassign an ID based on that match to your record in the staging table, and repeat this process for variable sets (tables) a -> x, in the staging DB. Also you have to find the end of each table and append if that data doesn't already exist in that table. For 9 different tables, this is a non-trivial undertaking, and it leaves a fair amount of room for error unless you are some Access/VB god who can do this stuff with their eyes closed.

Now if you use autonumbers in your DB instead of doing the "pure" and "elite" method, then:
A. You don't have to "weave" the data with IDs (bloody difficult).
B. You can import without having to reorder and rearrange the Excel import file. i.e. straight from the source with no transformations.
C. Once in the staging table you can run a check unmatched records query on each table, then append the results of this query to the relevant table and repeat.
D. You don't have to manually increment the numbers, or get the "end no" of each table.
E. The database takes the overhead of creating and sustaining the relationships, which is what it is meant to be used for.

So my vote is definitely FOR autonumbers, then again, I agree with KornGeek in that different DBs need different keys.

E.G. a parts DB or any DB that tracks physical products would be useful. if it tracks virtual info like document numbers, dates, or non-physical info, you're better off with autonumbers, as all they do is maintain relationships.

I also liked the chainsaw analogy.

Nathan

 

[Darrylles]

Hi Johnny,

Hang on, what do you mean EXACTLY by: "There are hundreds for each possible identifying attribute".

YOU identify a unique attribute per 'entry' or 'record' BEFORE you get to this stage surely?

YOU normalize the table structure BEFORE you get to this stage.

If you can't do this with autonumber, (with a fk to autonumber) then you can't do it anyway!

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[johnnymac43]

Other than a timestamp, the attributes I was referring to are items like Date, MachineID, and ActivityID. On a given day a machine will record a set of measurements for each of hundreds of occurrences of an activity, and some will do this for multiple activities. Unfortunately, in the real world not all such recorders have the ability to timestamp each set of measurements.

Just to add to the excitement, recordings are not evenly spaced during a day, but are made when a significant variation from the previous recorded measurements is detected. The autonumber relates the records in their natural sequence within a day, and human intellect, assisted by independent (non-digital) evidence, determines the significance of the measurements and assesses the likelihood of an abnormal reading being correct.

When we are used to working with timestamped data, this environment is really nineteenth century, but in much of the world (not just third world countries) it is a fact of life. My intention was to give an example of "never say never", rather than to debate design issues in a particular set of circumstances.

Cheers

John