Multiple PK's? Autonumber? HELP!

J

Jolene Updike

I am just learning Access 2000 in order to develop a relational database in
order to manage and catalog archaeological artifacts and paperwork, and I've
run into a brick wall. The nature of my data is a bit different than many of
the examples I've seen, so I'm hoping there's someone out there who can help
me wrap my mind around this problem. My main table, tblMainSite, is very
straightforward. It consists of 2 fields- SiteNum* and SiteName. Things get
tricky as I branch out. Here are a list of the rest of my tables (The fields
marked by asterisks either serve as the primary keys, or comprise information
that makes records unique in that table):

tblReports-
*SiteNum
Type
Title
AuthorLast
AuthorFirst
Notes
Location
(this table gives me no problems)

tblNotes-
*SiteNum
*Provenience
Type
Notes
Location

tblPhotos-
*SiteNum
*Provenience
Format
Subject
Quantity
Location
Notes

(here's where it gets trickier)

tblMainArt-
*SiteNum
*Prov
Excavator
DateExcavated
Cataloger
DateCat

tblArtifact-
*SiteNum
*Prov
*ArtifactNum
PieceCount
Material

etc.


What do I do? Autonumbering the tables with multiple asterisks seems to
aggrivate the situation.

Please help a desperate novice!
 
J

Jack MacDonald

A convention that I use is to always have a single, Autonumber field
in each table to serve as the primary key. The table may contain other
fields that can be indexed in combination to ensure uniqueness, but
the primary key is a single field. I find this technique makes it
easier to follow the relationships between tables.

You don't seem to have a "people" table, yet I see several places
where (presumably) people are involved. I suggest that you add such a
table with all the usual fields (PeopleID, FName, LName, etc). See
notes interspersed below.

On Wed, 22 Dec 2004 07:27:01 -0800, "Jolene Updike" <Jolene
I am just learning Access 2000 in order to develop a relational database in
order to manage and catalog archaeological artifacts and paperwork, and I've
run into a brick wall. The nature of my data is a bit different than many of
the examples I've seen, so I'm hoping there's someone out there who can help
me wrap my mind around this problem. My main table, tblMainSite, is very
straightforward. It consists of 2 fields- SiteNum* and SiteName. Things get
tricky as I branch out. Here are a list of the rest of my tables (The fields
marked by asterisks either serve as the primary keys, or comprise information
that makes records unique in that table):

tblReports-
*SiteNum
Type
Title
AuthorLast
AuthorFirst
Notes
Location
(this table gives me no problems)
Click to expand...

This structure presumes that each report has exactly one author. If
that is the case, replace the two author fields with a foreign key to
the new people table. If a report can have multiple authors, you will
need an additional linking table to create a many-to-many relation
between reports and authors.
tblNotes-
*SiteNum
*Provenience
Type
Notes
Location

tblPhotos-
*SiteNum
*Provenience
Format
Subject
Quantity
Location
Notes
Click to expand...

Consider removing the "quantity" field and adding a "FullFileName"
field. That will allow you to reference digital photo files from your
database.
(here's where it gets trickier)

tblMainArt-
*SiteNum
*Prov
Excavator
DateExcavated
Cataloger
DateCat
Click to expand...

Is "Prov" the same as "Provenience" in the previous two tables? What
is the difference between "Art" and "Artifact" in the next table. You
should probably replace the Excavator and Cataloger with foreign keys
to the new People table.
tblArtifact-
*SiteNum
*Prov
*ArtifactNum
PieceCount
Material

etc.
Click to expand...

I *think* this structure might serve you better:


tbleSite
*SiteNum
etc
- describes exactly ONE site


tblProvience
* ProvienceID
SiteNum - foreign key to the Site table
(each site can have several Proviences -- a guess on my part!! If it
is important that a site can be subdivided into several subsets, then
this table is required. If you do not require this subdivision, then
you can omit this table. )

tblReports
*ReportID
ProvienceID (foreign key to the Provenience table)
(each Report pertains to exactly ONE Provenience

tblNotes
*NoteID
ProvienceID (foreign key to the Provenience table)
(each note pertains to exactly ONE Provenience

Question: what is the difference between a Report and a Note? Do they
really need to be kept separate?

tblPhoto
*PhotoID
ProvenienceID (foreign key to the Provenience table)
FileName
PhotographerID (foreign key to the people table)
etc
(Each photo was taken at exactly ONE provenience)


tblArtifacts
*ArtifactID
ProvenienceID
DateExcavated
ExcavatorID (foreign key to the people table)
DateCatalogged
CataloggerID (foreign key to the people table)


Notice that I have one table about artifacts, while you (apparently)
have two. I am unclear what you were attempting to accomplish by using
two tables -- perhaps I have overlooked some vital aspect but it looks
to me like one table is sufficient.
What do I do? Autonumbering the tables with multiple asterisks seems to
aggrivate the situation.

Please help a desperate novice!
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
J

Jolene Updike

Thanks- I'm on the right track. BUT I still have a few questions:
(And, yes, Art = artifact and Prov= provenience; sorry for the inconsistency
in my example.)
tbleSite
*SiteNum
etc
- describes exactly ONE site
Click to expand...

Yep- I already have that one. It's the primary table
tblMainSite-
*SiteNum
SiteName
tblProvience
* ProvienceID
SiteNum - foreign key to the Site table
(each site can have several Proviences -- a guess on my part!! If it
is important that a site can be subdivided into several subsets, then
this table is required. If you do not require this subdivision, then
you can omit this table. )
Click to expand...

That's correct- I do need to subdivide each site, and that is the problem.
Provenience will be something like "SURFACE," "A," "B," etc. (layers of
soil), i.e. provenience names will repeat themselves throughout records. A
record will only be unique when site num and prov are viewed together.

Site numbers, however, are unique (for example, 44OR854).

The first Artifact table is designed to give information for all of the
artifacts recovered from a particular site num/prov.

The second artifact table,(there are a lot more fields, I just didn't want to overkill with my example)

is designed to catalog and view the characteristics of individual objects.
Maybe I can consolidate the two tables, but I'm not sure how to do it without
having to enter duplicate data with every field.



Hope that helps; I really appreciate your time!

Jolene Updike
Virginia Commonwealth University Archaeology
 
J

Jack MacDonald

Jolene
see comments in-line

Thanks- I'm on the right track. BUT I still have a few questions:
(And, yes, Art = artifact and Prov= provenience; sorry for the inconsistency
in my example.)


Yep- I already have that one. It's the primary table
tblMainSite-
*SiteNum
SiteName


That's correct- I do need to subdivide each site, and that is the problem.
Provenience will be something like "SURFACE," "A," "B," etc. (layers of
soil), i.e. provenience names will repeat themselves throughout records. A
record will only be unique when site num and prov are viewed together.

Site numbers, however, are unique (for example, 44OR854).
Click to expand...

OK - I was thinking that you might have meant subdivision in the XY
plane, but now I see that you mean subdivision on the Z axis. I think
that may make a difference because of "consistency of nomenclature".
Let me explain.

Suppose you have 5 sites with exactly three proveniences each. I would
envision a provenience table with 15 records -- one for each of the
site/provenience combinations. In my first message, I had referred to
this table as tblProvenience. That may have caused some confusion if
you had envisioned a provenience table with just three records. Since
the provenience can assume just three values (easily expanded) in the
Z axis, instead of the infinite number in the XY plane, it is a good
candidate for placing into its own table.

Therefore, you would have this set of tables:

tblSite (as you have already identified)
SiteID
etc

tblProvenienceName
ProvenienceNameID PK - an autonumber
ProvenienceName - text
- in the example, the records will be "Surface", "A", "B", etc

tblSiteProvenience
SiteProvenienceID PK - an autonumber
SiteID - foreign key to the tblSite
ProvenienceNameID - foreign key to tblProvenienceName
etc

This latter table provides a unique record for every Site/Provenience
combination in your database. The Photos, Reports, Notes, and
Artifacts are related to this table alone. However, since each record
in tblSiteProvenience is subsequently related to a particular Site,
there is a direct, traceable route back from each Artifact to the Site
without explicitly including the Site information in the Artifact
table.

If you had meant subdivision in the XY plane, then the likelyhood of a
consistent naming convention ("surface", "a", "b") would have
diminished significantly. If that had been the case, then I would have
omitted tblProvenienceName, and changed the structure of the
tblSiteProvenience, by removing the ProvenienceNameID field and
replacing it with ProvenienceName. However, the concept of using
tblSiteProvenience is the same in either case.

The first Artifact table is designed to give information for all of the
artifacts recovered from a particular site num/prov.
Click to expand...

Such information should be included in tblSiteProvenience. Anything
that pertains to the unique combination of SiteProvenience belongs in
that table, regardless of whether it is about artifacts, notes,
photos, etc.

The second artifact table,
(there are a lot more fields, I just didn't want to overkill with my example)

is designed to catalog and view the characteristics of individual objects.
Maybe I can consolidate the two tables, but I'm not sure how to do it without
having to enter duplicate data with every field.
Click to expand...

I hope you understand my point about removing the Site information
from the artifact table, and rely on the tblSiteProvenience instead.
It will simplify your job.

Not sure whether PieceCount belongs in this table. It's a judgement
call on your part. It *does* belong if (say) seven identical items of
a particular kind are to be catalogged as a single entitiy. If those
seven items are to be catelogged individually, then you can remove the
PieceCount field because each record always refers to a single item.
It's your call...
Hope that helps; I really appreciate your time!
Click to expand...

You're welcome.

Jolene Updike
Virginia Commonwealth University Archaeology
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
J

Jolene Updike

Jack,

Thanks! Your responses have been very helpful. I have set up the
tblProvName and tblSiteProv, and now I'm thinking about the structure of my
database very different way. See comments in-line.

Jack MacDonald said:
Jolene
see comments in-line



OK - I was thinking that you might have meant subdivision in the XY
plane, but now I see that you mean subdivision on the Z axis. I think
that may make a difference because of "consistency of nomenclature".
Let me explain.

Suppose you have 5 sites with exactly three proveniences each. I would
envision a provenience table with 15 records -- one for each of the
site/provenience combinations. In my first message, I had referred to
this table as tblProvenience. That may have caused some confusion if
you had envisioned a provenience table with just three records. Since
the provenience can assume just three values (easily expanded) in the
Z axis, instead of the infinite number in the XY plane, it is a good
candidate for placing into its own table.

Therefore, you would have this set of tables:

tblSite (as you have already identified)
SiteID
etc

tblProvenienceName
ProvenienceNameID PK - an autonumber
ProvenienceName - text
- in the example, the records will be "Surface", "A", "B", etc

tblSiteProvenience
SiteProvenienceID PK - an autonumber
SiteID - foreign key to the tblSite
ProvenienceNameID - foreign key to tblProvenienceName
etc

This latter table provides a unique record for every Site/Provenience
combination in your database. The Photos, Reports, Notes, and
Artifacts are related to this table alone. However, since each record
in tblSiteProvenience is subsequently related to a particular Site,
there is a direct, traceable route back from each Artifact to the Site
without explicitly including the Site information in the Artifact
table.

If you had meant subdivision in the XY plane, then the likelyhood of a
consistent naming convention ("surface", "a", "b") would have
diminished significantly. If that had been the case, then I would have
omitted tblProvenienceName, and changed the structure of the
tblSiteProvenience, by removing the ProvenienceNameID field and
replacing it with ProvenienceName. However, the concept of using
tblSiteProvenience is the same in either case.



Such information should be included in tblSiteProvenience. Anything
that pertains to the unique combination of SiteProvenience belongs in
that table, regardless of whether it is about artifacts, notes,
photos, etc.
Click to expand...
Does this mean that I should condense all the tables that now share the
foreign key of SiteProvenience into one? For example, here's a bit of what I
have now:

tblNotes (this refers to field notes and maps- very different from reports)
*NotesID
SiteProv
Type
Location
Comments

tblPhotographic
*PhotoID
SiteProv
Format
Subject
Location
FullFileName
Notes
(thanks for the FullFileName suggestion. It will be useful for present and
future records. Most of what I'm dealing with right now are various formats
of film that need to be archived)

[I can see clearly that my old tblMainArtifact and tblArtifact should be
combined into a new comprehensive tblArtifact]

tblArtifact
*ArtifactID
SiteProv
Excavator (thank's for the advice about the People table, but I'm not sure I
want to do that yet)
DateExcavated
Cataloger
DateCataloged
ArtifactNum
PieceCount (yes, there can be multiple pieces cataloged together)
Material
etc....


So--- do all of those tables get combined? Maybe that seems weird to me
because I'm trying to do with tables what I should be doing with queries.
 
J

Jack MacDonald

No - keep them in separate tables since they represent different
entities.



Does this mean that I should condense all the tables that now share the
foreign key of SiteProvenience into one? For example, here's a bit of what I
have now:

tblNotes (this refers to field notes and maps- very different from reports)
*NotesID
SiteProv
Type
Location
Comments

tblPhotographic
*PhotoID
SiteProv
Format
Subject
Location
FullFileName
Notes
(thanks for the FullFileName suggestion. It will be useful for present and
future records. Most of what I'm dealing with right now are various formats
of film that need to be archived)

[I can see clearly that my old tblMainArtifact and tblArtifact should be
combined into a new comprehensive tblArtifact]

tblArtifact
*ArtifactID
SiteProv
Excavator (thank's for the advice about the People table, but I'm not sure I
want to do that yet)
DateExcavated
Cataloger
DateCataloged
ArtifactNum
PieceCount (yes, there can be multiple pieces cataloged together)
Material
etc....


So--- do all of those tables get combined? Maybe that seems weird to me
because I'm trying to do with tables what I should be doing with queries.
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Top