Primary key in every table?

[Guest]

I think each table needs a primary key but I'm not sure. Please tell me
which set up is better.

My first draft had two tables, Contact Information (AutonumberPK) and
Products (Claim ID Autonumber PK).

I was thinking perhaps it was better to break up Contact Information (which
was huge!) into a few smaller tables. Is that a better idea and, if so, does
every table need a primary key?

What I have so far is....

tblContact
Reference Number (PK)
Date
Contact Name
Organisation
Address
Postcode
Account Number
Account
Telephone
Email
Opera Reference
Product(s)
Description
Carrier Claim? (yes/No field)
Progress Date

tblCarrierClaim
Reference Number (linked to Contact Information)
Consignment Note Number
Delivery Date
Product(s) (linked to Contact Information)
Description (linked to Contact Information)
Loss or Damage
Admin Name
Position
Phone
Claim Reference Number (PK)

tblCollection
Reference Number (linked to Contact Information)
Claim ID (linked to tblProducts)
Collection ID (PK)
Postcode (linked to Contact Information)
Product(s)
Comments
Expected Collection Date
Actual Collection Date

tblReturns
Reference Number (linked to Contact Information)
Claim ID (PK - linked to tblProducts)
Final Analysis
Report
Approved for Credit? (Yes/No Field)
Progress Date

tblProducts
Reference Number (linked to Contact Information)
Claim ID (PK)
Qty
Code
Item
Problem
Long Code
Sell Price
Cost Price
Initial Analysis
Action
Handling Fee? (Y/N)
Handling Fee Amount
Discount? (Y/N)
Discount Amount
SubTotal
Finance Completion Date

Is this the best way to do it? Previously I had everything in the Contact
Details Table except for the products table. This is a database to manage
returned items (ie, damaged, manufacturing faults, picking errors, etc) for
an office products company. I know this is a lot to read through but if you
have any comments or ideas I would appreciate anything. I have done the
draft database and wish to start on the real deal next week.

Thanks for your help.

 

[Guest]

Hello -

I think each table needs a primary key but I'm not sure.

Yes, pretty much all tables should have a primary key.

I agree that your first design had too many fields in the Contact
Information table. I encourage you to check your choice of field names
against the following references:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Some of your proposed field names include parenthesis ( ), question marks,
and most include spaces. My recommendation is to eliminate the spaces by
using "camel case". For example: "Loss or Damage" could be renamed
"LossOrDamage". It's still quite readable, but you won't have to deal with
issues later on related to spaces in your field names.

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335 and

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

Field names such as Date, Description, Position and Report are considered
reserved words and should be avoided. Table names should be plural where
possible, but field names should be singular. You don't want to encourage
people to enter a list of values into a single table cell. For example, the
field name: Product(s) kind of implies that a person could enter more than
one product into the same field in the same record.

You should not store any values that can be derived from other data. The
SubTotal field in the Products table looks like it could be calculated from
other data.

My preference is to prefix all primary key field names with lowercase "pk"
and all foreign key field names with lowercase "fk". I just think it makes it
easier in the long run. For example:

tblContacts
pkReferenceNum


tblProducts
pkProductID
fkReferenceNum (linked to Contact Information)


If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I think each table needs a primary key but I'm not sure. Please tell me
which set up is better.

My first draft had two tables, Contact Information (AutonumberPK) and
Products (Claim ID Autonumber PK).

I was thinking perhaps it was better to break up Contact Information (which
was huge!) into a few smaller tables. Is that a better idea and, if so, does
every table need a primary key?

What I have so far is....

tblContact
Reference Number (PK)
Date
Contact Name
Organisation
Address
Postcode
Account Number
Account
Telephone
Email
Opera Reference
Product(s)
Description
Carrier Claim? (yes/No field)
Progress Date

tblCarrierClaim
Reference Number (linked to Contact Information)
Consignment Note Number
Delivery Date
Product(s) (linked to Contact Information)
Description (linked to Contact Information)
Loss or Damage
Admin Name
Position
Phone
Claim Reference Number (PK)

tblCollection
Reference Number (linked to Contact Information)
Claim ID (linked to tblProducts)
Collection ID (PK)
Postcode (linked to Contact Information)
Product(s)
Comments
Expected Collection Date
Actual Collection Date

tblReturns
Reference Number (linked to Contact Information)
Claim ID (PK - linked to tblProducts)
Final Analysis
Report
Approved for Credit? (Yes/No Field)
Progress Date

tblProducts
Reference Number (linked to Contact Information)
Claim ID (PK)
Qty
Code
Item
Problem
Long Code
Sell Price
Cost Price
Initial Analysis
Action
Handling Fee? (Y/N)
Handling Fee Amount
Discount? (Y/N)
Discount Amount
SubTotal
Finance Completion Date

Is this the best way to do it? Previously I had everything in the Contact
Details Table except for the products table. This is a database to manage
returned items (ie, damaged, manufacturing faults, picking errors, etc) for
an office products company. I know this is a lot to read through but if you
have any comments or ideas I would appreciate anything. I have done the
draft database and wish to start on the real deal next week.

Thanks for your help.