Mistake in table design?

[Guest]

Hey all,

Access 2003 - I think I might have made a slight error in designing one of
my tables. I have two tables. One contains contact information and the
other contains time-stamped notes relating to the contacts. It has a
respective one-to-many relationship. They are linked by "ContactID".

Here's where I think I may have made a mistake... the second "notes" table
does not contain any unique values for each record. They are simply linked
by the ContactID which is not unique in the notes table.

Should I add a unique field identifying each note record?

Here's what I was trying to do when this hit me:

I have the notes for each contact listed in a subform. Each record on this
subform has an "Edit" button that opens a popup window that I want to contain
the current record's information (although I don't know how to do this).
This would be easy if it had a unique identifier. Since I don't have one, is
this still possible to do? Should I add in an autonumber to my notes records?

I hope I'm making sense. It's getting late in the day.

Thanks for any advice.

Aaron G
Philadelphia, PA

 

[Joseph Meehan]

Hey all,

Access 2003 - I think I might have made a slight error in designing
one of
my tables. I have two tables. One contains contact information and
the other contains time-stamped notes relating to the contacts. It
has a respective one-to-many relationship. They are linked by
"ContactID".

Here's where I think I may have made a mistake... the second "notes"
table does not contain any unique values for each record. They are
simply linked by the ContactID which is not unique in the notes table.

Should I add a unique field identifying each note record?

Here's what I was trying to do when this hit me:

I have the notes for each contact listed in a subform. Each record
on this subform has an "Edit" button that opens a popup window that I
want to contain the current record's information (although I don't
know how to do this).
This would be easy if it had a unique identifier. Since I don't have
one, is this still possible to do? Should I add in an autonumber to
my notes records?

I hope I'm making sense. It's getting late in the day.

Thanks for any advice.

Aaron G
Philadelphia, PA

Sure you can add it to the table. Just make it a primary key autonumber
no duplicates.

 

[Guest]

Joseph,

Thanks for the reply. I have a follow up question. Is it a bad design not
to have a unique identifier? I don't want to add extra fields to a table if
I can avoid it.

Thanks again.

Aaron G
Philadelphia, PA

 

[Guest]

Hi, Aaron.

Is it a bad design not
to have a unique identifier?

Most of the time, yes. There are occasional exceptions, like with a
temporary table that has no relationship with any other table and all of the
records in the table will be deleted within the current session.

A primary key is necessary to uniquely identify each record in the table.
Without it, relationships can't be established between other tables, because
the database engine can't identify exactly which records should be in the
relationship. Referential integrity cannot be enforced. The inability of
the database engine to uniquely identify each record will cause problems,
such as pulling the wrong records in a query. (Try explaining that to your
boss when the wrong data shows up in his reports.) . . . Or deleting the
wrong records. (Even worse.)

Without primary keys in tables, the functionality of forms and queries can
be limited, such as lack of subforms with matching records or displays from
combo boxes and list boxes with related data in the bound form. If you ever
intend to upgrade the Access database backend to a client/server database and
link to the new tables, you won't have updateable recordsets. That means no
record editing or deletions of records in these linked tables.

You may want to take a look at some database design documentation which
include the discussion of primary keys. Please see the following Web pages:

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://support.microsoft.com/?id=234208

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.