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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Documentation/terminology Look Ups Vs. Foreign Key, Any Version    
 
   
Psycoperl
post Sep 12 2018, 05:02 PM
Post#1



Posts: 183
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Hello All..

I am working on documenting my project so that if for some reason my structure gets messed up or goes missing that I or someone else would be able to rebuild my project without reinventing the wheel.

One of the issues that I am having (especially since it has been over 15 years since I was in a DB class, where we had to do documenting) is trying to figure out when I should label something as a Foriegn Key (FK) or just a lookup relationship.

For example I have tblStudents (My List of Students) listed below, the ones with the (??) after the column name are the ones where I am not sure if they would be FK or just Lookups. Any guidance you could provide is appreciated.

stuSRSID (PK) Student Record System ID
stuTerm (??) Term Admitted - value from tblTerms
stuSourceBatch (??) Source and Batch Code - value must be in tblSourceBatch
stuType (??) Student Type - value must be in zlookup_tblStudents_StudentTypes
stuLastName
stuFirstName
stuMiddleName
stuAddress1
stuAddress2
stuAddress3
stuAddress4
stuCity
stuState (??) State or Provence Code - lookup in zlookup_StateProvence
stuPostal
stuCountry (??) Country Code - lookup in zlookup_Country
stuPhone
stuEmail
stuDateOfBirth
stuDateAdded
stuDateModified
stuStatus (??) Student Processing Status - lookup in zlookup_tblStudents_StatusCode
This post has been edited by Psycoperl: Sep 12 2018, 05:02 PM
Go to the top of the page
 
theDBguy
post Sep 12 2018, 05:13 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,236
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If you're referring to the Lookup tab on the lower half of the table design screen, then take a look at this article first.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
orange999
post Sep 12 2018, 05:13 PM
Post#3



Posts: 1,854
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I would think that an up to date jpg/png of your relationships window would show much of that info.

Update:
Just saw theDBguy's post. I interpreted your z.. tables ( zlookup_tblStudents_StudentTypes) to be real Lookup Tables not Lookup Fields.

This post has been edited by orange999: Sep 12 2018, 05:16 PM

--------------------
Good luck with your project!
Go to the top of the page
 
Psycoperl
post Sep 12 2018, 05:24 PM
Post#4



Posts: 183
Joined: 11-March 15
From: Somewhere lost in the NY Subways


@DBGuy Thanks for the advice article. At this point, what I am concerned about is what to call them for documenting the build process. I am just not sure when the right time to call them a Foriegn Key or when to just indicate that it should be referenced to a lookup table.

Second -- noting the issues listed in your post from the lookup tab -- how in Access could I set it up to not use the lookup wizard but still ensure that only values from the lookup tables are used?

Go to the top of the page
 
theDBguy
post Sep 12 2018, 05:27 PM
Post#5


Access Wiki and Forums Moderator
Posts: 73,236
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Any field in any table that references a primary key from another table is considered a foreign key. A lookup field is just anther way of referring to the same field. The lookup tab is just an Access way of trying to make it simpler for the designer, but only causes issues for the developer.

So, if you're asking me, I would document "all" of them as a foreign key. Then, it's up to you to remove all the lookup info from the Lookup tab or not. If you do remove them, then to make sure only valid values are entered on your foreign key field, you'll need to first enforce referential integrity in your table relationship and also use a combobox on your data entry form.

Hope it helps...
This post has been edited by theDBguy: Sep 12 2018, 06:14 PM
Reason for edit: fixed typo

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Psycoperl
post Sep 12 2018, 05:46 PM
Post#6



Posts: 183
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Ok that makes sense. Thanks.
Go to the top of the page
 
tina t
post Sep 12 2018, 05:48 PM
Post#7



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


QUOTE
Any field in any table that references a primary key from another table is considered a primary key

DBguy, did you mean to say foreign key in your statement above?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Sep 12 2018, 06:13 PM
Post#8


Access Wiki and Forums Moderator
Posts: 73,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Tina,

Sorry, you’re right. It was a typo. Thanks!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Psycoperl
post Sep 12 2018, 07:08 PM
Post#9



Posts: 183
Joined: 11-March 15
From: Somewhere lost in the NY Subways


I figured from his context. But thank you for your verification
Go to the top of the page
 
GroverParkGeorge
post Sep 13 2018, 09:01 AM
Post#10


UA Admin
Posts: 33,494
Joined: 20-June 02
From: Newcastle, WA


PMFJI:
At the risk of whipping a dead horse, I can't resist the urge to take a stick to the idea of using lookup fields in a table.

There is only one place where Lookup Fields in tables are useful, and in fact, required. I guess, two places. Linked SharePoint lists used as tables in Access require them. The now deprecated Access Web App tables used them in the Access interface. In both cases, however, they are nothing more than interface design tools for designating fields which are, in fact, Foreign Keys.

Properly handled, they do no harm. In a properly constructed Lookup Field, you bind the field to the Foreign Key (not to the value field) from the related table. One of the common problems I see in "rescue" databases is that the Lookup field was used to store that value field instead of the Foreign Key (i.e. the Primary Key from the related table). For example, in a table with a field to record the Foreign Key for an employee who completed a task, the original creator used a Lookup field with one column--that column being the "Name" field from the employee table, NOT the Primary Key for the employee. The result is that the Lookup field does NOT support Referential Integrity because the value stored is NOT the related Primary Key. It "works", as many things do in Access, despite being a less than optimal approach. Moreover, that mistake means the Employee table has a single "EmployeeName" field because that's how this Lookup field works. In short, in addition to the problems in that link theDBGuy provided, Lookup fields make it really easy to implement sloppy logic.

So, in this case, we need to avoid conflating the implementation mechanism -- Lookup Fields -- with the database design feature -- the Primary/Foreign Key relationship. Use them if you must. Properly designed they do little real harm. It's just that there is a better way.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
Psycoperl
post Sep 13 2018, 09:05 AM
Post#11



Posts: 183
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Understood.... I am taking great pains to ensure that I am always choosing the "Key" not the value in the relationship.
Go to the top of the page
 
mike60smart
post Sep 13 2018, 10:37 AM
Post#12


UtterAccess VIP
Posts: 12,735
Joined: 6-June 05
From: Dunbar,Scotland


Hi

I don't know about anyone else but I think the use of the extra characters preceding the actual Fieldname is a bit of a waste of time.

My personal preference is to name the fields as follows:-

StudentID (PK) Student Record System ID
TermID (??) Term Admitted - value from tblTerms
SourceBatchID (??) Source and Batch Code - value must be in tblSourceBatch
TypeID (??) Student Type - value must be in zlookup_tblStudents_StudentTypes
LastName
FirstName
MiddleName
Address1
Address2
Address3
Address4
City
StateID (??) State or Provence Code - lookup in zlookup_StateProvence
Postal
CountryID (??) Country Code - lookup in zlookup_Country
Phone
Email
DateOfBirth
DateAdded
DateModified
StatusID (??) Student Processing Status - lookup in zlookup_tblStudents_StatusCode

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Sep 13 2018, 03:59 PM
Post#13



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


i'll have to differ with you on using prefixes in naming table fields, Mike. i choose a one- to four- character prefix for every table i create, and use that prefix in the name of every field in the table. even in a normalized database, you can have a practical name used in more than one table. making sure that every field in my database is uniquely named does two things for me: 1) it makes it easy for me to see what table a field is in, when using the fieldname anywhere in the database, and 2) when i have two tables with an identical practical fieldname in the same query, i don't have to prefix the fieldnames with the appropriate tablenames - because in fact the fieldnames are not identical, due to each having its' own prefix.

different strokes...

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Psycoperl
post Sep 13 2018, 05:14 PM
Post#14



Posts: 183
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Tina

That is exactly why I have it named that way since my Users table has some of the same fields as my students table. Where the users table is the actual staff that is working with the system

Go to the top of the page
 
mike60smart
post Sep 14 2018, 02:04 PM
Post#15


UtterAccess VIP
Posts: 12,735
Joined: 6-June 05
From: Dunbar,Scotland


Hi Tina

Good point I will adopt this in future

Thanks again

pompom.gif kisses.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Sep 14 2018, 02:53 PM
Post#16



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


hi Mike, sometimes my 2 cents is worth 2 cents to somebody else - and then sometimes it isn't! ;) it's all good, either way. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st September 2018 - 10:44 AM