"=?Utf-8?B?Sm9sZW5lIFVwZGlrZQ==?="
<JoleneUpdike@discussions.microsoft.com> wrote in
news:674A5B4F-ECA0-4521-9924-CAB94110757C@microsoft.com:

Is this a foreign key: each Catalog has many Artefacts, but each Artefact
belongs to at most one Catalog?

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?

Big warning flag: I guess GeneralMaterials and MaterialsInArtefacts
should be in separate tables...

Ditto

Ditto

Ditto

Ditto

Ditto ditto ditto... Yuu need to read about normalisation and how to
construct a relational design.

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

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]