Beginner Relationship Question (1 Viewer)

flecsynotpepsi

New member
Local time
Yesterday, 22:06
Joined
Feb 14, 2018
Messages
2
Hello All,

I have a probably basic design question, but I do need some help. I began designing this database and entering data into it. As one can see from the attached relationship table, I was unaware that it was advised to have a ID field with autonumber set as the primary key in each table. I was told his was because lookups can take longer with larger databases. If a database has something like 1000+ items, should I change the tables or continue as is?

I am afraid of changes I make would alter data I have already entered and corrupt forms I have already setup.

Thanks.
 

Attachments

  • Relationships for Inventory 2.zip
    127.7 KB · Views: 134

Minty

AWF VIP
Local time
Today, 02:06
Joined
Jul 26, 2013
Messages
10,355
One off the main reasons for adding an ID to the type of single field tables (Sometimes referred to as lookup tables) is that you can correct a typo or change the text description and it will automatically be reflected in all your existing records without it affecting your underlying data.

E.g. You add an ID to your Owner table. The owner decides to change their name. As you have now stored the ID rather than the actual text you simply update the text description and it will be reflected in all the appropriate places you display the linked text.

Some people don't like adding an ID just for the sake of it. I find it makes life much easier in the long run - particularity if you add additional fields to the lookup table in later development for added functionality.

Be warned though - don't confuse this with the use of Access Lookup Fields in your table. These are implemented in a weird fashion and will cause you issues - see here for an explanation http://access.mvps.org/access/lookupfields.htm
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:06
Joined
Feb 28, 2001
Messages
27,001
There is a wealth of information available in this forum that you can reach using the very good Search function. In the thin blue ribbon that is near the top of the page, just under the thing that tells you what part of the DB you are examining (left side) and who the forum thinks you are (right side), you will find "Search" as the 3rd from right item.

Here, sometimes the problem with searching is finding the correct topic for searches. Let me help. You want to read up on "autonumber" and "synthetic keys" and "natural keys." I recall a very lengthy discussion from a couple of years ago on "synthetic vs. natural keys" that will list the pros and cons (and probably beat them to death - we do that here) of the two types of keys.

Minty's number one reason for having a separate "internal ID" field is because sometimes names get changed. Access will CERTAINLY allow you to have a name as part of a prime key (PK) field. However, names can be edited and if we are talking about people's names, duplicated easily, thus requiring a multi-field key to maintain the key's candidacy. In fact, the ability to potentially change the contents of the field being used as a PK is one of the reasons to remove it from candidacy as a PK.

You asked another question regarding having 1000+ records and whether performance would be affected based on key type. If your PK is a more or less "standard" autonumber field, which is a LONG data type, you would find this performance to be relatively very fast. If you have a text field as your PK, you really do have to face the idea that longer keys can take longer to manipulate. When the record count is in the 800 to lower 1000s range, you would probably be unable to detect the speed difference if the database is local to your workstation. If you have a network-attached back-end file (i.e. part of the database is shared on a network) then MAYBE you would barely be able to see a speed difference. MAYBE. Maybe not. When you get above 10K records, key size might become visible. It's all a continuum of response speeds anyway.

However, here is the REAL reason you want a numeric key vs. a text key. Key size is an issue if you have parent/child tables, because WHATEVER you use for the PK of your parent table, that key has to be duplicated as the foreign key (FK) in all child tables to which it is related. If the child table requires a PK, it is often necessary to build a compound key out of the FK and something unique to the child table. That means the index that uses the keys for searching will have to work harder. And there, a numeric PK is superior in most cases.
 

flecsynotpepsi

New member
Local time
Yesterday, 22:06
Joined
Feb 14, 2018
Messages
2
Thank you so much both Minty and The_Doc_Man. That was extremely informative and helpful.
 

Mark_

Longboard on the internet
Local time
Yesterday, 19:06
Joined
Sep 12, 2017
Messages
2,111
As you used the work Lookups, there are two decidedly different types that you will used in applications; By Reference and By Value.

By Reference means you store a reference to what you wish to look up in your record. If the reference is to a person, you look up the values currently in that person's record, such as name (which can be changed), position (changeable), or spouse (also changeable). If you want the information to always be current, you normally use a By Reference look up. In your record you save a pointer to the data, normally the records ID.

By Value lookups are just what they sound like. You copy the same value that is currently in the looked up record. This is normally used when you are putting in the name as it appears at a time, spouse at the time you record, or other data that should not change if the source you looked it up from changes.

Knowing which of the two you will be using is very important at design time. As soon as you hear "Look up" work out which of the two it will be. If you mix them up you can run into issues later.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:06
Joined
Feb 19, 2002
Messages
42,981
When you have no data to preserve, changing from IDs as FKs rather than text fields, is just a matter of changing the data type and the join line. Once you have data to preserve, the process is more complex and will require running a bunch of update queries before deleting the old text fields.

Start by MAKING A BACKUP. Then add the autonumber to each table. Name it after the table so you would have CategoryID, OwnerID, etc. Make the new autonumber the PK and save each table. Then in the Assets table, add 5 new columns, one for each of the new ID's. Use the same name in the Assets table as you used in the lookup table.

Create 5 update queries, one for each ID field. Join the original Category to the Category field on the Category table and update CategoryID in Assets with Category.CategoryID, etc.

Once you are certain that all updates have been done successfully, delete the 5 old relationships by deleting the join line in the Relationship window and then in the Assets table, delete the text fields. Go back to the relationships window and recreate the relationships. This time check the enforce RI option.

Additional changes:

The relationship with Division and Location is muddy. Are Divisions always at a single location? If so then:
Remove the join line from Assets To division and make the join from Assets to the DivisionID field in Location. In the diagram, move Division to the right of Location for clarification. Then remove LocationID from the Assets table.

If the relationship with division and Locations is controlled by the pairs in the Location table, then Leave LocationID in Assets and have both join lines go to Location table. Again, you would remove the join from Assets to Division but you would not remove the DivisionID from Assets.

And Finally - NEVER use special characters or embedded spaces in your column or table names sho change all the spaces, #, and /, to words or an Underscore if you want the separation. So Model # becomes ModelNum or if you must, then Model_Num. Rack/Shelf becomes RackOrShelf.

Even if you don't need to convert data, be sure to change the Foreign key names to match the names of the new FKs.
 
Last edited:

Users who are viewing this thread

Top Bottom