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    
post Feb 26 2018, 05:22 PM

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

I agree that, as a generalization, mulit-field primary keys are to be avoided. If for no other reason than it makes you queries more complicated to write.

One caution in using an autonumber primary key is that it allows someone to enter duplicate record, unless other constraints are imposed. By that I mean duplicating the natural key fields. Worse yet, if the other fields are different how do you know which record is correct?

The point about foreign keys allow you to enter records when the information for the foreign key table is interesting. Hadn't thought about it that way.
Go to the top of the page
tina t
post Feb 26 2018, 06:11 PM

Posts: 5,796
Joined: 11-November 10
From: SoCal, USA

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.

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?

to address your initial questions - and if you haven't abandoned your thread out of sheer exhaustion - i'd say first: why are you regretting the design you started with? there must be some reason it has become an issue for you. so "should I?" is a question best answered by starting with why you want to. second: "can i?" can easily be answered as "Yes, you can" - it's certainly do-able. the caveat is that it will probably take quite a bit of work, both at the table level and at the interface level - queries, forms, reports, code. that points you back to why you want to. will the benefit you may gain offset the extra work involved?

i personally use surrogate primary keys for two reasons: 1) because i don't like multi-field joins, and 2) because "natural" keys are made of real data, and i doubt that it's possible that real data will never change. now, CascadeUpdate allows a primary key value to be changed, and that change will be made in all related child records. but i've found it to be a PITA - to avoid data update collisions, it's best to make such changes when all users are out of the db, and that can be difficult in a multi-user, multi-shift environment.

but you can chalk up your current design to "live and learn" and just roll on. again, it goes back to why you're considering making a change.

Go to the top of the page
post Feb 27 2018, 07:51 AM

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

One caution in using an autonumber primary key is that it allows someone to enter duplicate record, unless other constraints are imposed.
I agree, but with the caveat that in some cases the constraint itself is somewhat artificial. Two purchase orders, entered the same day by the same person to the same supplier, could be identical except for the purchase order number. The functional difference is in the related records (the line items). The SupplierID, PurchaserID, and PurchaseDate fields may be the only other fields in the main record. Whether the Purchase Order number or a surrogate field is the PK, it is likely that a single field PK makes the most sense.

Since I almost always use an autonumber PK, I am not very familiar with cascade updates. Specifically, with cascade updates I don't know for sure the circumstances under which the linking field is updated, but I don't think it applies if there is a mass update via an update query. If the purchase order number format changes, most likely it will affect only new records, so there is no need for a cascade update. However, if an EmployeeID number format changes, and the EmployeeID is the PK, most likely it will be necessary to update all records, both the main employee record and any related records. If this is done one record at a time via a form, I believe related records within that database would change at the same time. I don't know what exactly would happen if the Employee table is updated all at once via an update query, but I think a separate update would be needed for the related records. I am just about certain, though, that if the EmployeeID is inserted into the aforementioned purchase order records in a separate database, the update would need to be performed manually. So that may be a consideration that guides the choice of primary key.

In one of my earlier databases I stored a combination of department abbreviation, two-digit year, and incrementing number in one field -- B-17-01, C-17-01, B-17-02, B-18-01, B-18-02, C-18-01, B-18-03, C-18-02, and so forth. The incrementing is done via code when each record is created. It works, and there is no need to change it, but it is not a choice I would make again. If the change you are contemplating involves other tables, particularly ones in other databases, it may be best to make the change. If it is merely awkward but is not causing problems, your best choice may be not to worry about it. There is no single rule, but only the best practice for a particular situation.
Go to the top of the page
post Feb 28 2018, 12:15 PM

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

My writing skills are not the best. I did not mean to say that using a text field as a PK is a source for corruption but it is vulnerable to data corruption. I was thinking of a project with a large pharma company where they were using the first 3 letters of a vendors name plus the first 3 letters of the city plus a code for the vendors type as the PK. They brought be in because they were having problems...go figger.
Go to the top of the page
post Feb 28 2018, 04:11 PM

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

Dave, the example you describe does sound like a poor choice of key but it's quite possible to make the wrong choice of keys whatever datatypes you use. The lesson here ought to be: choose your keys with care. Automatically using numbers for keys and assuming that's OK just because they are numbers could be an equally bad strategy in many cases.
Go to the top of the page
2 Pages V < 1 2

Custom Search

RSSSearch   Top   Lo-Fi    21st March 2019 - 04:46 AM