For MS Access I would lean towards natural keys for two reasons:
If your relationships and keys and foreign keys are properly defined you can specify “Cascade Update Related Fields” which will make an update to a key field’s value trivial.
If you have an artificial key, you can definitely enforce uniqueness on another field by applying a unique index, but I don’t think you can do that for a combination of two fields e.g. Manufacturer_ID + Manufacturer_Part_No. That has to be enforced in data entry and editing which is not fool-proof. The better way to do this is to make the two fields a compound key.
Relational database systems don’t have a magic way of dealing with changes. It’s hard, but that’s life. You just have to make a choice, and deal with it. You could, for example, enter the new product number as a new product: you loose the association with the history. You could just change the product number: you loose the old product number. You could tie the two product numbers together – it makes everything bigger, slower, clunkier, and, with MS Access, some of the most powerful features don’t work (valuable MS Access features don’t work with many-many queries)
Traditinally, Database Administrators prefered natural keys. Application Programmers prefered Symbolic Constants. Relational database don’t have a magic way of dealing with this dilemma either. BUT, since you have a small database, and depend on outsiders for your data, I’d lean towards optimising the Application, not the Database. Use artificial keys except where you turn purple with embaressment.
half the fun of application design - relational database or not - is anticipating the unusual cases and dealing with them.
What if the manufacturer changes?
What if the manufacturer name changes?
What if more than one manufacturer makes the product?
What if the item number changes?
What if the manufacturer later uses a same number for something different?
What if the price changes? Do inventory items have the same price, do you blend the “cost of product”, do you track them be serial number vs. price?
What if the quantity changes, say from quarts to liters, from 6-packs to 8-pacjks, etc.?
What if an item can be disassembled and parts sold separately?
What if an item is discontinued?
Are you tracking orders and fulfillment? How do you handle partial shipment? Partial shipment then discontinued?
What if payment is made in a foreign currency, but then the exchange rate changes between accepting payment and depositing the amount?
How do you handle history of activity when almost anything - manufacturer, number, description, price -can change at any time?
You have to anticipate the weirdest cases and decide - are they worth programming around, or just ignore them, etc.
But for example, you don’t want to do a report “how much did we sell in 2010?” By taking the 2010 inventory and multiplying by today’s prices.
A primary key should be unique not just when it’s created, but over time. That is, once you’ve used a PK to identify a row, you shouldn’t use it to identify a different row in the future, even if the original row has been deleted.
A PK should also be unchanging, as far as possible. One reason is that, if you do change the PK you will have to update all foreign keys that refer to that row. This can involve a lot of work. Also, there could be references outside the database (such as hand-written records) that contain the old PK.
One solution I’ve seen to ‘item number may change’ is to have the primary key artificial, and then have a list of item numbers with an ‘effective date’ range. The most current number, in the database I’m thinking of, was copied into the primary record.
This keeps the history of the item together. And searches can search on the ‘current’ item number or ‘historical’ item numbers.
But if you use an artificial primary key, you simply don’t need to synchronise updating the same value in multiple tables, because you’ll only be storing the value in one place.
That’s incorrect. Microsoft Access supports unique compound indexes.
Silently updating an unknown number of other records in multiple related tables. I wouldn’t recommend using this setting as a general rule–it makes it really easy to do things you wish you could undo but can’t. Cascading updates is not always as easy as Access likes to imagine it is.
One thing with enforcing stuff like this in the DB is that it’s a good idea to couple it with custom error messaging (method explained here: MS Access 2003: Replace system error message with a custom message ) - that way, you can explain to the user what they did wrong in terms specific to the application you are creating, rather than in generic DB terms.
Access was just about cutting edge in V2. Oracle peeps used to complain it was non-standard because it had features that Oracle didn’t get until 5 years later. And then … nothing.
Data analyst here - I’m not a specialist in database design but I work neck-deep in client databases 50 hours week.
I think others have pretty well chimed in on what I popped in to add, e.g. an artificial key versus a logical key (which would be one field or a combination of them). It’s been nearly 20 years since I did much with MS Access so I’ll go with what others have said about it enforcing unique constraints.
There might well be times where you need, say, item ID to be non-unique in and of itself - e.g. if you can buy a 6-pack of Coca Cola from each of two grocery stores, then '6PACKCOKE12OZCAN" would be the same for both… and when Coke “improves” the product to make it 11.75 ounces instead you might want to change the ID to “6PACKCOKE11.75OZCAN”. There the logical key might be “SAFEWAY” + “6PACKCOKE12OZCAN” or “PUBLIX” + “6PACKCOKE12OZCAN”
Or you might want to allow duplicates e.g. if you’re using SSN as the ID and one was mis-entered. Or if you want to be able to catch that two different people are using the same SSN (I gather one trick that illegal immigrants sometimes use to get hired is to use someone else’s SSN… so I’d bet the IRS has red flags go up when this happens).
The project I’m currently working on has all its key fields generated - year/month/day/hour/minute/second + a sequence number so the net result is something like 20ish digits. It has more natural keys on the tables as well but those are enforced either by the application or by database constraints.
Do NOT implement cascade deletes / updates (if Access even supports such). It’s a rare thing that this can be done with 100% certainty that you won’t farkle something by accident. I’d much rather have it built into the database as a constraint that throws an error when I try to delete a parent, than have it delete all the child relationships.