January 29th, 2010, 07:04 PM
posted to microsoft.public.access.tablesdbdesign
|
|
|
Creating Relationships between tables
Thanks for all the replies. It's a little clearer now.
"BruceM via AccessMonster.com" wrote:
I too will risk igniting conflict by saying natural keys make perfect sense
in some situations. An autonumber is a good choice much of the time, but I
wouldn't get locked into thinking it always needs to be used.
An autonumber is a type of Long Integer field. Linking fields must be of the
same data type (LI to LI, text to text, Double to Double, etc.), which is why
Long Integer must be used to link to autonumber. Number field is a sort of
umbrella term, but the specifics (Integer, Long Integer, Currency, Single,
Double, etc.) must be the same. You can't link an integer to currency, as
the latter may contain decimals and the former cannot.
Choose a naming convention that works for you. I too tend to use the table
name plus ID for the PK field. For instance, in tblEmployee, the PK is
EmployeeID. In the linked table I usually use the first letter or two of the
table name, then the linking field name. For instance, tblAward will have
AwardID as the PK, and A_EmployeeID as the linking field, also known as a
foreign key by some, but others hotly contest calling it a foreign key. The
point of using a variant of the field name is that I find it easier to tell
them apart in SQL and other code. This is my choice, but you may decide to
make a different choice. It helps to be consistent, especially if you are
working with others on the same project, but also for your own benefit when
you revisit a project after a year or two.
Lee Ann wrote:
I've followed the direction from Steve and Karl. Just a few follow-up
questions - I've set up tables according to Steve. When trying to link
similar fields between the tables (EstablishmentID), it doesn't allow me to
do it unless I set up the foreign key (if that's the correct word) as
Number/Long Integer, as suggested by Karl. Is this always the way it's
supposed to be done? If I don't use the number/LI, but yet have the same
data type in both tables, it tells me it can't match because the data types
are not the same.
Thanks for the assistance - it seems the more I learn, the more questions
there are.
Date of Offense - Time of Offense - Day of Week
Use single DateTime field and derive the parts as needed.
[quoted text clipped - 63 lines]
I apologize for the lengthy post and I appreciate any assistance I can get.
Thanks in advance.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1
.
|