UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> I Really Screwed Up, Did Not Follow Fundamental Advice To Plan First. Primary Key Problem., Access 2013    
 
   
jmkeuning
post Feb 21 2018, 01:30 PM
Post#1



Posts: 110
Joined: 20-October 15



Our data - we have clients, and our clients have matters. Clients are assigned numbers and each matter is assigned a number. So "matters" effectively are represented by the client/matter number. Something like 05894.00023 - Client number 05984 and matter number 00023. The 00023 is unique (EDIT: within) the client, but other clients also have a matter 00023. Of course, clients and matters have names also, like "Fish Shack" is a client and a matter might be "Fryer Acquisition." Well, me in my brilliance decided that my client/matter table will use the Client Name and Matter name as a primary key. So when we start a new matter we put in the information - Client name, Matter name, Client number, Matter number. But we also create an alias (called CaseName) - FishShackFryerAcq - or something like that. This is our primary key. Then I start creating related tables, and queries, and VBA scripts, forms with subforms, and reports, all with this made-up alias as primary and foreign key. A year later I am regretting it.

I want to know if it matters. And if it does matter (or even if it doesn't) should I fix it. And if I should, how.

The way I was thinking I could fix it is to start with tblCases, where CaseName is the primary key, and assign a number to every CaseName. Then run an update query to replace casename with the number. Then go to every table where CaseName is a foreign key and run the update query. If I do that, everything should still work. But then I need to change CaseName in tblCases to an autonumber. Can I do that? Then change every CaseName field in every table to a number.

Or does it not really matter? Every new matter will still get a unique Alias which represents the client and matter, so does it matter if the primary key is a string rather than an autonumber?
This post has been edited by jmkeuning: Feb 21 2018, 02:26 PM
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 02:23 PM
Post#2


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


In one sense, no. As long as you have a unique value for the PK, and can reliably use it in all related tables, you have a working relational database. On the other hand, as you've no doubt learned, it's generally more difficult to manage.

Retrofitting may or may not be worthwhile, depending on how much pain you are in now.
Go to the top of the page
 
ranman256
post Feb 21 2018, 02:25 PM
Post#3



Posts: 883
Joined: 25-April 14



it doesnt matter if it works.
Using autonums is easy and the user doesnt have to do anything....they are auto assigned.

But you can also use strings and assign them yourself. Once related, they can both look up and keep track of the child records.
Go to the top of the page
 
nvogel
post Feb 21 2018, 03:48 PM
Post#4



Posts: 891
Joined: 26-January 14
From: London, UK


You have done the right thing by implementing business keys (AKA "natural" keys) in your tables. Where information in a table has to be used by people it makes sense to have user-friendly keys that people will be familiar with. Familiarity, Simplicity and Stability are the properties that make good keys.

Numeric keys aren't needed for everything. If it works for you then I suggest you don't need to create additional keys unless and until you find a reason to. If you believe you may need to update key values then you can use the cascade update option so that foreign key values get updated automatically.
Go to the top of the page
 
zaxbat
post Feb 21 2018, 04:00 PM
Post#5



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Access automatically wants to assign the primary keys and they work extremely well. I always use them....period. It would be beneficial for you to switch to them if your database is not yet very large. You can still create nicknames tags identifiers etc. for user friendliness, but using the autonum primary keys that Access creates makes queries run faster than string lookups.
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2018, 04:24 PM
Post#6


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


This boils down to what you believe in most fervently, IMO.

You can't go wrong either way, as long as you are scrupulous in handling the PK values and all relationships that depend in them.

Most Access developers, myself included, are adherents to the "Surrogate Key" approach. Many others are adherents to the "Natural Key" approach.

If someone wants to turn it into a religious war, so be it.
This post has been edited by GroverParkGeorge: Feb 21 2018, 06:10 PM
Go to the top of the page
 
haresfur
post Feb 21 2018, 08:23 PM
Post#7



Posts: 294
Joined: 4-April 12
From: Bendigo, Australia


You could add an autonumber field to the table and then decide whether to use that or the natural key on a case by case basis. If one or the other has an advantage, then you use it. You can then change individual bits to use the autonumber without breaking others. The disadvantage of that approach is that you code can become harder to understand because the processes won't be consistent.
Go to the top of the page
 
BruceM
post Feb 22 2018, 07:43 AM
Post#8


UtterAccess VIP
Posts: 7,752
Joined: 24-May 10
From: Downeast Maine


There is a distinction between a primary key and a unique index. You can have an autonumber primary key, AND create a unique index on the combination of client/matter. I prefer to join on a single field, so I use the surrogate key approach. For some tables such as an Employee table you may need, say, five fields to assure uniqueness, which means the training records table needs five fields just to handle the join.

I realize you are talking about a single text field rather than a combination of fields. That is OK, just not a choice I would make. I'm not going to say one approach or the other is the "right" one, but I will say that IMHO the important thing is to assure the uniqueness of the record based on real world values. If the only difference between two records is the autonumber field (or a unique text field, for that matter), there is no real distinction between the two.

One potential problem with text fields such as FishShackFryerAcq is what to do if the Fish Shack gets another fryer. It could be FishShackFryerAcq2. You could have a notice, when attempting to enter FishShackFryerAcq, that the name already exists, so you could add the 2, and find out that name already exists too, so you could keep trying until you find a numeric suffix that hasn't been used. Or maybe Fish Shack opens another location, so you have FishShack2FryerAcq. If the value is assigned manually it could turn into a hassle to design unique names, especially if the only indication is a message that the name already exists.

I expect the situation you provided was as an example, not a literal scenario, but the above points remain.
Go to the top of the page
 
GroverParkGeorge
post Feb 22 2018, 08:27 AM
Post#9


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


Ah, that is another good point, Bruce.

There is a difference between the issues raised by choosing surrogate or natural keys, and the issue of "how" a natural key is implemented.
Go to the top of the page
 
Dave21495
post Feb 22 2018, 03:08 PM
Post#10



Posts: 406
Joined: 9-April 03
From: Los Angeles, CA


Just my two cents but I would advise always without exception using auto number as your primary key. The primary key serves soley to uniquely identify a row and should not convey any information other than that. 90% of the time a database that relies on a natural key for a PK in their tables will run into problems somewhere down the line. Ten years ago I was scolded on this site for not using autonumber as a PK and that has saved me countless times since. I have also made a fairly good living coming in to repair databases built using natural primary keys. Not only can there be instances where keeping the uniqueness of a field requires imagination and changing business rules, but also data can and will degrade over time. Using a text field as a primary key opens you to serious corruption problems down the road.

You can put a unique index on a field if you need to but you will save yourself tons of headaches if you use autonumber as your primary key for every table without exception.
Go to the top of the page
 
BruceM
post Feb 23 2018, 07:37 AM
Post#11


UtterAccess VIP
Posts: 7,752
Joined: 24-May 10
From: Downeast Maine


I can't quite agree that using a text field as a PK will lead to corruption, although I will admit I don't have direct experience with text PKs, at least not recently. It is true that natural keys are subject to change when the underlying business rules change, making cascade updates necessary. That is something I prefer to avoid, and I am firmly on the side of using a surrogate key (autonumber in Access, identity in SQL Server, etc.), but as a design consideration, not out of concern for corruption. If there is documentation to refute this I would be glad to read it.
Go to the top of the page
 
zaxbat
post Feb 23 2018, 09:04 AM
Post#12



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I will chime in a hearty "Huzzah!" about using the system default autonum as primary key PK. In all applications it is always best to try to follow what that app expects as default. And, obviously, text fields can and do change (the company merged and underwent name change...etc...) but PK's should never be changed.

The user need never see the PK or indeed never even needs to know that it exists (just confuse them anyway), but they work faster for queries, lookups, sorts, etc. and are automatic. If you have a text field as primary key then you must check each time before adding a new record to make sure that no conflict exist...additionally, you need to add extra code to ask the user to pick a different text string before saving. All extra work for no reason.....just use autonum. KISS Keep It Simple, Stupid! well, sorry, don't take that personally.....just an expression
Go to the top of the page
 
nvogel
post Feb 23 2018, 10:27 AM
Post#13



Posts: 891
Joined: 26-January 14
From: London, UK


zaxbat, I can only assume that your reply was intentionally humorous irony. Much like saying "Just use ON ERROR RESUME NEXT and you can automatically avoid all errors!" I guess your joke may seem cruelly familiar to unfortunate users of some (hopefully few) disastrously bad databases that lack usable, validated, meaningful keys.

To be serious though. It's not reasonable to claim, as Dave21495 does, that keys based on text fields are a source of "corruption" - whatever that means. If that really were the case then the DBMS software would surely be at fault and would not be fit for use. Millions of people rely on working database systems that do not suffer such issues.

There seem to be some other misunderstandings in this thread. A primary key is not a special type of key - it is just one key, potentially among several keys in a table. It is entirely arbitrary to say that some keys should be implemented as unique indexes (actually uniqueness constraints) and others as "primary" keys: all keys are implemented in fundamentally the same way and have the same features and function. The matter of which key you deem to be primary is mostly a question of style and convention which in most cases makes little or no difference. The keys you implement and how you intend to use them are what really matter.

An auto-number is simply a technical feature used to generate a number; it is not an alternative to a natural key. Auto-number generated values often get used as identifiers in the business domain (i.e. natural keys). They are not used exclusively for surrogate keys by any means.



Go to the top of the page
 
nvogel
post Feb 23 2018, 11:31 AM
Post#14



Posts: 891
Joined: 26-January 14
From: London, UK


Every key is unique by definition, not just the primary key. Foreign keys are not keys - the name is an unfortunate legacy but we are stuck with it. The term foreign key just means (informally) "this set of attributes is a key somewhere else".

Keys are sets of attributes that are irreducibly unique and non-nullable within a table, enforced as such by uniqueness constraints. Also referred to as Minimal Superkeys or Candidate Keys. Keys work the same in Access as in other database systems: CREATE TABLE tbl ( ... UNIQUE (a,b), UNIQUE ( c), PRIMARY KEY (z)); in this example either (a,b) or ( c) or (z) may be referenced by a foreign key but it could also be that none of them are referenced by a foreign key.








This post has been edited by nvogel: Feb 23 2018, 11:32 AM
Go to the top of the page
 
nvogel
post Feb 23 2018, 02:31 PM
Post#15



Posts: 891
Joined: 26-January 14
From: London, UK


It's difficult to make progress on a topic like this unless there's common ground on the standard concepts. I'm surprised you are having trouble appreciating the difference between a key and a foreign key.

The foreign key syntax in Access and other SQL-based systems is:

ALTER TABLE t1 ADD ... FOREIGN KEY (x) REFERENCES t2 (z);

x is a *foreign key* in table t1. z is a *key* in table t2. x references z.

In database design theory z is also called a candidate key or a minimal superkey but the terms primary/secondary/alternate key or just plain KEY are much more common.

Don't take my word for it of course, please study a good book on relational database fundamentals.


This post has been edited by nvogel: Feb 23 2018, 02:35 PM
Go to the top of the page
 
haresfur
post Feb 24 2018, 07:17 PM
Post#16



Posts: 294
Joined: 4-April 12
From: Bendigo, Australia


QUOTE
There seem to be some other misunderstandings in this thread. A primary key is not a special type of key - it is just one key, potentially among several keys in a table. It is entirely arbitrary to say that some keys should be implemented as unique indexes (actually uniqueness constraints) and others as "primary" keys: all keys are implemented in fundamentally the same way and have the same features and function. The matter of which key you deem to be primary is mostly a question of style and convention which in most cases makes little or no difference. The keys you implement and how you intend to use them are what really matter.


The bold is correct for Access but in SqlServer there is a fundamental difference in clustered vs. non-clustered indexes, if I understand correctly. And it is nearly always a good idea to make the primary key a clustered index.

In my databases the BoreID field in the Bore table is an externally-generated identifier that *must* be unique and text. I suppose there might be some performance advantages to using an auto-number unique index as a primary key, but I agree with you (I think we agree) that there is not really any reason to generate auto-numbers that simply replicate the function BoreID as an existing natural key. It makes sense to me to apply the most efficient index to this field since it is unique, is almost always a field returned in queries and is commonly used in the WHERE clause and as a sort field.

It might be different if you are doing a huge number of insert and delete operations on a natural key field so don't want the overhead on the indexing but that's never the case for my applications.
Go to the top of the page
 
haresfur
post Feb 24 2018, 07:31 PM
Post#17



Posts: 294
Joined: 4-April 12
From: Bendigo, Australia


QUOTE
The user need never see the PK or indeed never even needs to know that it exists (just confuse them anyway), but they work faster for queries, lookups, sorts, etc. and are automatic. If you have a text field as primary key then you must check each time before adding a new record to make sure that no conflict exist...additionally, you need to add extra code to ask the user to pick a different text string before saving. All extra work for no reason.....just use autonum. KISS Keep It Simple, Stupid! well, sorry, don't take that personally.....just an expression


Perhaps this is a tangent to the original question but I don't totally agree with this. My perspective is that of an end-user who also does a lot of the design work to meet my needs.

Sure, not all users want or need to know anything beyond how to run the application. But there is an advantage to everyone if, at least some of them, understand something about database design and operation. In my business there is huge advantage in giving the users the understanding and resources to write ad-hoc queries. I've sat in plenty of meetings where the end users and the designers couldn't communicate the needs that drive the design and the design constraints on how you meet those needs.

There is no extra effort in checking a text string for conflict if there is a requirement that that string be unique. And if you don't force the uniqueness through the table design, then you run a risk of really messing up the database if someone messes up and doesn't check first.
Go to the top of the page
 
nvogel
post Feb 25 2018, 12:41 AM
Post#18



Posts: 891
Joined: 26-January 14
From: London, UK


haresfur,

The text you highlighted in bold is correct for SQL Server as well as Access. In SQL Server a clustered index can be on any set of columns, it doesn't have to be a key. Typically the clustered index is on non-nullable columns and is unique (i.e. a superkey) but it doesn't have to be the same as the primary key. If there is a better set of columns to cluster on then you should make the primary key non-clustered. I agree with everything else you said.

My previous two replies were in response to posts by BruceM. Those posts have since disappeared for some reason, which makes my comments unnecessary and maybe off-topic.
Go to the top of the page
 
BruceM
post Feb 26 2018, 08:27 AM
Post#19


UtterAccess VIP
Posts: 7,752
Joined: 24-May 10
From: Downeast Maine


I deleted those posts. They were not directly related to the original topic, and there seemed to be a fundamental disagreement about the meaning of some of the terminology. I regard a primary key as a unique identifier for a record. Just to be clear, because I believe you misunderstand me on this point, a surrogate key as a unique identifier will generally be in the context of a unique constraint. I say "generally" rather than "must" for a reason I will describe shortly. The main point I tried to make is that two records that are distinguished from each other only by a surrogate key are not really separate records. A natural key will enforce the need to distinguish the records from each other, but so will effective design. I am not alone in preferring a surrogate key to a multi-field primary key (and therefore a multi-field join). That surrogate key advocates and natural key advocates disagree does not make either one wrong, because the overall design is what really matters.

As for "generally" rather than "must" above, sometimes a main record is little more than a "container" for related records. For instance, the same person may generate two purchase orders to the same supplier on the same day. The only thing to distinguish the main records is the PO number. If you choose to use the PO number as the primary key, and I choose to set a unique constraint on the field but use autonumber as the PK, it reflects two valid approaches, that's all.

I understand foreign keys to be the field or fields in a related table that link the record to the parent record. Within that context they can be null, if for no other reason than that it is not always the business practice to complete all fields when entering a new record.

Also, a foreign key does not have to be unique, in my view. If that was the case, the PO could have only one line item, since the PO ID field is the linking (foreign key) field.

Please, if I am using terminology differently than you are, or if you disagree with my points, explain the difference if you wish to respond. Since you seem to have more SQL Server experience than I, and initiated the portion of the discussion to which I responded, it would be interesting to hear some of your thoughts. However, if you are surprised at my different understanding of certain concepts or my use of terminology, I have no wish to learn about that, and most certainly not in the absence of explanation. BTW, while I am gaining familiarity with ALTER TABLE syntax and so forth, an elliptical SQL fragment is not especially helpful.
Go to the top of the page
 
zaxbat
post Feb 26 2018, 10:57 AM
Post#20



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


One thing I know is: There's a lot that I don't know. Nonetheless, I try to help were I can. An attitude of gratitude goes a long way in here....everybody, keep on coding !!!!!! (that's not hospital humor...ha ha ha ha---if you missed the reference, that's cool....we all put our pants on one leg at a time anyway)

Uh......Hey, Zax.....?
....yeah?
Man, you're talking to yourself again.
.....oh wow.....sorry...........uh, hm.....well, later....
This post has been edited by zaxbat: Feb 26 2018, 11:01 AM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2018 - 02:57 PM