1

I am a total novice to this whole database world and I have a question. I am building a database for my final project for my masters class. The database includes cities, counties, and demographic data for the state of Colorado. The database ultimately will be used as a spatial database. At this point I have all my tables built in Access, and have a ODBC connection to PostgreSQL to import the tables after they are created. Access does not allow for shapefiles to be added to the database, PostgreSQL does.

My question is about primary keys, each of my tables in Access share an FIPS code (this code allows me to join the demographic data to a shapefile and display the data in ArcMap with the proper coordinates). I have a many demographic data tables with this FIPS code. Is it acceptable to set the FIPS as the primary key for each table? Or does each table need its own individual primary key that is different from the others?

Thanks for the help!

June7
  • 19,874
  • 8
  • 24
  • 34
Dane
  • 11
  • 2
  • 2
    Is it possible? Sure, but probably not a good design choice. I can understand using FIPS as a key field, but but not the primary for all tables. Usually you design tables to contain like data, but describing the attributes of different entities. Your 'city', 'county' tables would not be FIPS; I believe they should be the names. It's ok to use an Autonumber field as a PK. Since your tables are probably related with a one-to-many relationship, your member table can't use FIPS because it would not be unique. Yes, there are exceptions for certain things, but my suggestion is NO! – Wayne G. Dunn Nov 25 '17 at 18:17
  • Awesome Thank you Wayne. Just so I am understanding correctly.... Your suggestion is to use the auto-generated "ID" field as the PK for most of the demographic data? and the "name" field as the PK for City and County? – Dane Nov 25 '17 at 18:20
  • Also, with the FIPS code as a PK say on my crime demographic table, would it then be a FK on all the other tables that contain a FIPS number? – Dane Nov 25 '17 at 18:28
  • Ahhh, the dangers of commenting on just a snippet of information :) You *could* use an Autonumber field as PK, but I *usually* prefer to use a unique field where possible. If you had four tables: plant, product, customer, and order, then plant could use either plant name or AN for PK; same for product. However probably can't use customer name as PK due to duplicates (there may be a 1,000 '1st Bank' branches), thus you need either a compound key, or AN -- I would just use AN as PK. Yes, it's ok to use FIPS as FK in addition. – Wayne G. Dunn Nov 25 '17 at 18:46
  • Yes I know it is difficult to give great advice on just a bit of information. I would be happy to share the database with you if you wanted to take a look at it for me? – Dane Nov 25 '17 at 19:05
  • I would be happy to look at your database, but first I think you should read the 'specifying primary keys' section of: https://support.office.com/en-us/article/Database-design-basics-eb2159cf-1e30-401a-8084-bd4f9c9ca1f5#bmspecifyprimarykeys then set all your key fields. This way it will help you learn the process, and then we can hash over the differences. I miss skiing in Colorado!! – Wayne G. Dunn Nov 25 '17 at 19:20
  • I will take a look at that link. And I am spoiled with the skiing here. The slopes just opened, I have yet to make it up yet. But I will soon! – Dane Nov 25 '17 at 19:25
  • I have to disagree with the other comments & answers here; since your FIPS code is common to all tables that makes it the *very definition of a perfect primary key*. Provided this code relates to a unique row on each table it's absolutely acceptable. – Absinthe Nov 25 '17 at 21:06
  • Absinthe, So what your saying is that if the FIPS code is common to all tables (or most tables) it will work as the PK as long as each table has a unique row on each table? For example my county table and my population table both have a "County_FIPS" row, this row would be the PK for both tables? – Dane Nov 25 '17 at 21:27

1 Answers1

-1

The default PK is “ID”, so there really no problem with using this default for all tables.

In fact it means for any table or code you write you can now always rest easy as to what the primary key is going to be.

And if you copy or re-name a table, then again you know the ID.

Some people do prefer having the table name as part of the PK, but that does violate normalizing of data since now your attaching an external attribute to that PK column.

However for a FK (foreign key), since the VERY definition of the column is an external dependency, then I tend to include the table name like this:

Customers_ID

And once again due to this naming convention, then you can always “guess” or “know” the name of a FK column (table name + ID).

At the end of the day, there is not really a convention on this issue. However I will recommend for all tables you create, you do allow access to create that default PK of “id”. This of course assumes your database design is not using natural keys. And the debate of natural keys vs surrogate key (an auto number pk “id”) has many pros and cons. You can google natural keys vs surrogate keys for endless discussions on this issue.

June7
  • 19,874
  • 8
  • 24
  • 34
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Thank you for the help. For what ever reason I am having a hard time understanding the PK and FK relationship? I have struggled with it all semester when doing my ERD's for the course. I just cant seem to make the connection on what the point of the keys are?? – Dane Nov 25 '17 at 21:30
  • The “ID” as PK should exist in every table. The FK is simply a plane Jane “long” number field. Your code (or an access sub form) then will place the value of the “PK” ID of the table that you want to relate this record back to. So a table of Customers, and then say a table of furniture that the customer owns, then for each record in furniture that you want to “point to” or “relate” back to a single customer record, you simply type in the PK value from the parent record "ID" into this FK column. The FK is not automatic filled out for you when you add the child record directly to the table. – Albert D. Kallal Nov 25 '17 at 21:49
  • That makes this much more understandable. So, basically the PK is the "pointer" and the FK is what the pointer is pointing to? – Dane Nov 25 '17 at 21:59
  • Sure. In fact it better to simply say the PK is the "thing" that is used to identify that row - does nothing more, and nothing less. And the PK don't really point to anything. And a FK is simply a column that will contain that PK value from the table + row you want the child record to point to. So the PK does not really point to anything, but is the "one thing" that identifies that one row. Any child table you want to relate to the parent means you will simply enter the PK value into that FK column. So it really only FK columns that point to a record, and PK columns don't point to anything. – Albert D. Kallal Nov 26 '17 at 02:05