Normalizing my table- too many dimensions!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm in the midst of designing a comprehensive database for archaeological
materials including tables for reports, notes, and photographs, but also
integrating an artifact cataloging system. I'm having trouble with the
Artifact table(s)- the table I have is tending towards having several
dimensions of each characteristic, and I don't know how I should handle this.
Here's what I've got so far:

tblArtifact
------------
ArtID (automatically assigned ID) PK
CatalogID (linked from another table)
ArtifactNumber (this has got to be different from ArtID- there can be an
Artifact #3
for each different CatalogID)
PieceCount
GeneralMaterial
GeneralMaterial2
SpecificMaterial
SpecificMaterial2
SpecificMaterial3
ObjectName
Type
Type2
Type3
Part
Part2
Techno
Techno2
Techno3
DecorativeTech
DecorativeTech2
DecorativeDesign
DecorativeDesign2
DecorativeElement
DecorativeElement2
DecorativeElement3
WareType
WareType2
VesselCategory
PostManuModification
PostManuModification2
TPQ
Mends
CrossmendLocation
Notes
 
tblArtifact
Click to expand...
Is this a foreign key: each Catalog has many Artefacts, but each Artefact
belongs to at most one Catalog?
ArtifactNumber (this has got to be different from ArtID- there can be
an Artifact #3 for each different CatalogID)
Click to expand...

Don't like this: do you mean an Artefact can belong to more than one
Catalog at the same time? Or that the same Artefact appears more than
once in this table?
PieceCount
GeneralMaterial
GeneralMaterial2
Click to expand...

Big warning flag: I guess GeneralMaterials and MaterialsInArtefacts
should be in separate tables...
SpecificMaterial
SpecificMaterial2
SpecificMaterial3
Ditto



Ditto


Ditto


Ditto

DecorativeTech
DecorativeTech2
DecorativeDesign
DecorativeDesign2
DecorativeElement
DecorativeElement2
DecorativeElement3
WareType
WareType2
Click to expand...

Ditto ditto ditto... Yuu need to read about normalisation and how to
construct a relational design.
VesselCategory
PostManuModification
PostManuModification2
TPQ
Mends
CrossmendLocation
Notes
Click to expand...

That's okay: we all start somewhere. There are probably half-a-dozen
different entities here (Materials, Techniques, WareTypes, DecElements,
Modifications, etc) which look as though they should be in their own
tables. How they relate between each other is up to you of course. Do
certain DecorativeElements only apply to particular DecTechniques?

Yes: this does still look a bit more like a spreadsheet than a database,
but it's early days in the design yet, so keep going. :-)

All the best


Tim F
 
you're "committing spreadsheet" (a common mistake), and in doing so,
breaking a basic table normalization rule: never put data in field names.
wherever you have "name, name2, name3", that's data. what you have is
subordinate data that has a many-to-one relationship with the superior
data - in other words: one artifact can have many general materials, one
artifact can have many specific materials, one artifact can have many types,
etc, etc.

in a relational database model, every data element that occurs only once per
subject (one artifact has one artifact number, one piece count, one object
name, one vessel category, etc) stays in the parent table. you express the
one-to-many relationships with linked parent-child tables, as

tblArtifacts
ArtID (automatically assigned ID) PK
CatalogID (linked from another table)
ArtifactNumber (this has got to be different from ArtID- there can be an
Artifact #3
for each different CatalogID)
PieceCount
ObjectName
VesselCategory
TPQ
Mends
CrossmendLocation
Notes

tblArtifactGenMaterials
GenMaterialID (primary key)
ArtID (foreign key from tblArtifacts)
GenMaterialName (never use the word "Name" by itself as a field name)
(any other fields that describe a specific instance of a general material)

tblArtifactSpecMaterials
SpecMaterialID (primary key)
ArtID (foreign key from tblArtifacts)
SpecMaterialName
(any other fields that describe a specific instance of a specific material)

tblArtifactTypes
TypeID (primary key)
ArtID (foreign key from tblArtifacts)
TypeName
(any other fields that describe a specific instance of a type)

tblArtifactParts
PartID (primary key)
ArtID (foreign key from tblArtifacts)
PartName
(any other fields...)

tblArtifactTechnos
TechnoID (primary key)
ArtID (foreign key from tblArtifacts)
TechnoName

tblArtifactDecoTechs
DecoTechID (primary key)
ArtID (foreign key from tblArtifacts)
DecoTechName

tblArtifactDecoDesigns
DecoDesignID (primary key)
ArtID (foreign key from tblArtifacts)
DecoDesignName

tblArtifactDecoElements
DecoElementID (primary key)
ArtID (foreign key from tblArtifacts)
DecoElementName

tblArtifactWareTypes
WareTypeID (primary key)
ArtID (foreign key from tblArtifacts)
WareTypeName

tblArtifactPostManuModifications
PostManuModID (primary key)
ArtID (foreign key from tblArtifacts)
PostManuModName

the standard interface for data entry into parent-child tables is a
mainform/subform setup. but you need to forget about forms for the moment;
your first priority is getting your tables set up correctly.

i strongly recommend that you read up on data normalization and table
relationships. if you invest the time now to get that right, you'll save
tons of time and frustration in building the rest of your database (queries,
forms, reports).
you'll find plenty of help at the following link
http://www.ltcomputerdesigns.com/JCReferences.html
focusing first on Starting Out, and Database Design 101.

hth
 
--
Jolene Updike
Virginia Commonwealth University Archaeology


Tim Ferguson said:
Is this a foreign key: each Catalog has many Artefacts, but each Artefact
belongs to at most one Catalog?
Click to expand...

Yes; CatalogID is the foreign key to tblCatalogInfo. (I should have called
that field CatalogInfoID) I'll try to explain the situation a bit better:
tblCatalogInfo contains information about artifacts (like each artifact's
site number, the cataloger of the artifacts, and the date cataloged) that
would be duplicated for items in tblArtifact that come from the same source.
Don't like this: do you mean an Artefact can belong to more than one
Catalog at the same time? Or that the same Artefact appears more than
once in this table?
Click to expand...
No- Multiple artifacts can share the same CatalogInfoID (CatalogID)- foreign
key
Big warning flag: I guess GeneralMaterials and MaterialsInArtefacts
should be in separate tables...
Click to expand...

I don't understand- what are you calling MaterialsInArtefacts? Would the
answer involve removing all the descriptive characteristic fields and
substituting it with something like "Attributes"? If so, how would I
organize that?

My brain still isn't trained to think in database, so it would really help
me if you gave me an example of how you might structure something like this.

In my reading so far, I just haven't found an analagous example to work from


That's okay: we all start somewhere. There are probably half-a-dozen
different entities here (Materials, Techniques, WareTypes, DecElements,
Modifications, etc) which look as though they should be in their own
tables. How they relate between each other is up to you of course. Do
certain DecorativeElements only apply to particular DecTechniques?
Click to expand...
Yes! Could you explain to me how to make that work?
Yes: this does still look a bit more like a spreadsheet than a database,
but it's early days in the design yet, so keep going. :-)
Click to expand...
I do have tables for these entities- for example,

tblSpecificMaterial
-----------------------
SpecificMaterialID (PK)
SpecificMaterial Name (every conceivable material listed)

These tables turn into look-up lists in the final plan.
All the best


Tim F
Click to expand...

Does this make more sense? Thanks for all the help!

Jolene
 
Thanks so much- that was really clear and helpful (as well as painfully
obvious)!!


--
Jolene Updike
Virginia Commonwealth University Archaeology


tina said:
you're "committing spreadsheet" (a common mistake), and in doing so,
breaking a basic table normalization rule: never put data in field names.
wherever you have "name, name2, name3", that's data. what you have is
subordinate data that has a many-to-one relationship with the superior
data - in other words: one artifact can have many general materials, one
artifact can have many specific materials, one artifact can have many types,
etc, etc.

in a relational database model, every data element that occurs only once per
subject (one artifact has one artifact number, one piece count, one object
name, one vessel category, etc) stays in the parent table. you express the
one-to-many relationships with linked parent-child tables, as

tblArtifacts
ArtID (automatically assigned ID) PK
CatalogID (linked from another table)
ArtifactNumber (this has got to be different from ArtID- there can be an
Artifact #3
for each different CatalogID)
PieceCount
ObjectName
VesselCategory
TPQ
Mends
CrossmendLocation
Notes

tblArtifactGenMaterials
GenMaterialID (primary key)
ArtID (foreign key from tblArtifacts)
GenMaterialName (never use the word "Name" by itself as a field name)
(any other fields that describe a specific instance of a general material)

tblArtifactSpecMaterials
SpecMaterialID (primary key)
ArtID (foreign key from tblArtifacts)
SpecMaterialName
(any other fields that describe a specific instance of a specific material)

tblArtifactTypes
TypeID (primary key)
ArtID (foreign key from tblArtifacts)
TypeName
(any other fields that describe a specific instance of a type)

tblArtifactParts
PartID (primary key)
ArtID (foreign key from tblArtifacts)
PartName
(any other fields...)

tblArtifactTechnos
TechnoID (primary key)
ArtID (foreign key from tblArtifacts)
TechnoName

tblArtifactDecoTechs
DecoTechID (primary key)
ArtID (foreign key from tblArtifacts)
DecoTechName

tblArtifactDecoDesigns
DecoDesignID (primary key)
ArtID (foreign key from tblArtifacts)
DecoDesignName

tblArtifactDecoElements
DecoElementID (primary key)
ArtID (foreign key from tblArtifacts)
DecoElementName

tblArtifactWareTypes
WareTypeID (primary key)
ArtID (foreign key from tblArtifacts)
WareTypeName

tblArtifactPostManuModifications
PostManuModID (primary key)
ArtID (foreign key from tblArtifacts)
PostManuModName

the standard interface for data entry into parent-child tables is a
mainform/subform setup. but you need to forget about forms for the moment;
your first priority is getting your tables set up correctly.

i strongly recommend that you read up on data normalization and table
relationships. if you invest the time now to get that right, you'll save
tons of time and frustration in building the rest of your database (queries,
forms, reports).
you'll find plenty of help at the following link
http://www.ltcomputerdesigns.com/JCReferences.html
focusing first on Starting Out, and Database Design 101.

hth
Click to expand...
 
I'm in the midst of designing a comprehensive database for archaeological
materials including tables for reports, notes, and photographs, but also
integrating an artifact cataloging system. I'm having trouble with the
Artifact table(s)- the table I have is tending towards having several
dimensions of each characteristic, and I don't know how I should handle this.
Here's what I've got so far:
Click to expand...
I think my problem is that I'm thinking about this table as if it were a
form- I'm relatively new at this, and I can't think of an alternate solution.
Click to expand...

Any time you have one to many relationships - such as one Artefact to
many DecorativeElements - you need ANOTHER TABLE. "Fields are
expensive, records are cheap". Whenever you find yourself with fields
with names like Widget1, Widget2 and Widget3, consider creating a
Widgets table related one-to-many to your main table, and taking the
widget information out of the main table entirely!

If you have a many to many relationship (such as a defined set of
Materials; one artefact can consist of several materials, and any
given material can be in many objects) then you need THREE tables not
two. See below.

In your case, I'd see the following tables:

tblArtifact
------------
ArtID (automatically assigned ID) PK
CatalogID (linked from another table)
ArtifactNumber (this has got to be different from ArtID- there can be
an Artifact #3 for each different CatalogID)
PieceCount
ObjectName
TPQ
Mends
CrossmendLocation
Notes

GeneralMaterials
MaterialID
MaterialName <e.g. "feathers">

SpecificMaterials
SpecificMaterialID
MaterialName <e.g. "hummingbird feathers">

MaterialsUsed
ArtefactID <link to Artefacts>
GeneralMaterialID <link to GeneralMaterials>
SpecificMaterialID <link to SpecificMaterials>

Types
Type <I don't know what your 'types' are so I don't know if you want
a numeric TypeID or just a text type>

ArtefactTypes
ArtefactID
Type <or TypeID>

This table would have one or more records for each artefact, defining
which type or types the artefact can be considered; e.g. you might
have a given artefact which is "Pottery" and "Storage Container" and
"Cooking Pot" all at the same time.

You can take it from there for the other "dimensions".

Your table structure will be much more complex - but only because the
data that you are modeling is more complex than a single table should
be forced to bear!

John W. Vinson[MVP]
 
you're welcome, Jolene! but i really only gave you part of the "equation". i
skipped the part about creating a table to list all the general materials, a
table to list all the specific materials, etc (which you say you've already
done - good). John's post explains the many-to-many relationship with
linking table, which will tell you how to put it all together, so be sure to
study it, too.

one note: make sure you only use those "look-up" tables to populate
comboboxes or listboxes in forms. do *not* create any lookup fields in any
of your tables! (see http://www.mvps.org/access/lookupfields.htm)

hth


Jolene Updike said:
Thanks so much- that was really clear and helpful (as well as painfully
obvious)!!
Click to expand...
 
--
Click to expand...

A general point: two hypens and a space is normal language for "end of
message, everything that follows is signature" -- and most newsreaders
will ignore everything that follows. I nearly assumed that there wasn't
any reply in this!
I don't understand- what are you calling MaterialsInArtefacts?
Click to expand...

It's a table that indicates which Materials are used in which Artefacts:
there is a many-to-many relationship between materials and artefacts and
therefore you need a third table to implement it.
Would
the answer involve removing all the descriptive characteristic fields
and substituting it with something like "Attributes"? If so, how
would I organize that?
Click to expand...

I wondered about this at first but hesitated to mention it because I
don't know what details belong to Materials, Technologies, and so on. If
you are using them purely adjectivally, though, you could end up with a
very strong "tall, thin" design like:

ArtefactID KeyWord
========== =======
1023 HardClay
1023 African Enamel
1023 3rd Cent BC
1023 Feathers
1023 HandCarved
9724 HardClay
9724 HandCarved
etc etc


You would control the KeyWord column by relating it to a table like this:

KeyWordShort AttributeType Notes
============ ------------- -------------
HardClay Primary Material etc
African Enamel Specific Material etc
3rd Cent BC Epoch etc
Feathers Decorat Element etc
HandCarved Decorat Technology etc


and so on.

These tables turn into look-up lists in the final plan.
Click to expand...

Look-up lists - good; look-up fields - bad :-)

Hope that helps


Tim F
 
Back
Top