sing the Autonumber field as your primary key

[gheyman]

I have using several Freelancers to develop a bd. Some insist that the db should be using the ID fields in everything, other do not and are not, which is starting to take a toll on my db.

My db is all about tracking Proposals, so I started and many followed using the Proposal Number field as the primary field. I have code that actually builds the Proposal Number which also ensures that the number is never duplicated.

Why do some developers insist on using the ID/Autonumber fields from the tables? Anyone know?

 

[Joe4]

There are two different (and often very divided) schools of thoughts on this. I can't really say one is right and one isn't, it is usually just a matter of programmer preference. But there are those in the programming community who have strong feelings/opinions on both sides of the debate.

Personally, I like to use meaningful primary key fields instead of AutoNumber or some other random ID, as long as you have one in a table that will not duplicate or be blank, it shouldn't be an issue. I have developed many applications using this model over the years, and it has never caused me an issue (including a billing database that has been used for 15 years now). As long as you do it correctly, it should be fine.

 

[xenou]

In general it never hurts to use an ID field (Autonumber ID). This ensures every table has a primary key, which is better than tables not having a primary key. Sometimes it's just window dressing. For example, a state lookup table could probably have the State Abbreviation be used as a primary key, and having an autonumber ID field would be more or less useless, though still not harmful.

 

[Joe4]

In general it never hurts to use an ID field (Autonumber ID). This ensures every table has a primary key, which is better than tables not having a primary key. Sometimes it's just window dressing. For example, a state lookup table could probably have the State Abbreviation be used as a primary key, and having an autonumber ID field would be more or less useless, though still not harmful.

Good point. Usually, if I have a table that does not have a Primary Key field (or does not seem at first glance to need one), I will add one anyway. It never hurts.
And sometimes if you try to create some Action queries, Access won't like it if you include a Table that does not have a Primary Key field (even if that field is not the one you are trying to update).

 

[gheyman]

But know are we not talking about have a Primary key or not - and no longer talking about using the Autonumber in queries and forms? I just want to be clear because I have to make a decision to go one way or the other (on the Autonumber issue).

I do appreciate the recommendation on always having a Primary Key in every table and will follow that advice.

 

[Joe4]

Usually, the reason for using an Autonumber is to establish a Primary Key field. They go hand-in-hand.
You wouldn't really use an Autonumber and NOT make it the Primary Key.

Basically, it is good to have a Primary Key in every table. Whether or not it is a Autonumber, or some other derived ID field, the choice is yours.
Personally, I prefer to use more meaningful ID fields whenever possible, but will also use Autonumber fields in other tables, where the ID field isn't really important or used in Joins anywhere (like in Lookup tables).

So, I don't think there is any right or wrong answer here. It is really just personal preference.

 

[xenou]

The real important thing is to identify and properly use your primary keys, autonumber or otherwise. There is a school of thought that says always use a primary key as the "REAL" primary key, and use other fields as the "visible" id that users see. I don't really find the arguments compelling, but they are out there. Probably an autonumber field would actually be easier than some kind of roll your own method of getting a "Next highest Number" value, though, if you have a numeric key.