Do I really need a primary key

[Gary]

I have numerous fields in a database which could be all grouped into 3
seperate tables. eg. tbl_customer, tbl_quotation, tbl_technical

The unifying factor in each table would be a field called ProjectName.
Beyond that, they pretty much have their own different field names.
However if I was to nominate ProjecName field as the primary key, I would
not be able to add duplicate names.
eg. using tbl_customer:

ProjectName: Acme
Company: Purple Pty Ltd
Contact: John Smith

next record entry in same table:
ProjectName: Acme
Company: Brown Pty Ltd
Contact: Sally Dent

this second entry of Acme would not be allowed (if ProjectName was a primary
key). I would like to be able to enter "Acme" more then once in customer
table (as well as entering Brown Pty Ltd more then once in the "company"
field. But in order to link up "Acme" in the other tables, a primary key
would be required.
I was simply thinking of not using the primary key and just use one table to
include all the fields in it. Would that be the way to go ?

 

[Al Camp]

Gary,
You didn't indicate what information and purpose the Quote and Tech
tables serve. If they contain multiple entries of information against a
particular Project, then the ProjectName table will require a key field (the
One) to link it to the subsidiary tables (the Many).
Add an autonumber key field to your ProjectName table... (ex. ProjectID).
Add ProjectID (Long Integer) to both the subsidiary tables, and create a
One to Many relationship between them.

If Quotes and Tech have only one entry per ProjectName, then they should
be in your ProjectName table.

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Brendan Reynolds]

If a Customer can be involved in more than one Project, then ProjectName
does not belong in the Customer table at all. What you have appears to be a
many-to-many relationship between Customer and Project, and to model a
many-to-many relationship what you need is an intermediate 'linking' or
'junction' table - for the sake of example lets call it CustomerProject. The
primary key of this table is a multi-field primary key including ProjectName
and CustomerName and it is related one-to-many to Customer (on CustomerName)
and one-to-many to Project (on ProjectName). Perhaps a picture might help
here ...

http://brenreyn.brinkster.net/rels.jpg

 

[aaron.kempf]

yes, you always need a single-column int, smallint primary key

 

[Tony Toews]

yes, you always need a single-column int, smallint primary key

int and smallint are SQL Server terms and are generally unfamiliar to
Access folks

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[aaron.kempf]

well tony mr smarty pants..

any access developer worth his beans would be doing access data
projects; so i strongly disagree with you