unique identifier across multiple "sister" tables

mikulas

New member
Local time
Today, 06:26
Joined
Jan 18, 2011
Messages
5
I'm working on a database to store information about archaelogical artifacts. I have a number of different types of artifacts, such as beads, utensils, and ceramics. Each type has fields that pertain only to that type of artifact (e.g., circumference applies to beads, not utensils). But at the end of the day they are all still artifacts and share some fields in common, such as location of dig, estimated period of origin, etc. So I am envisioning tables like the following for each artifact type:

Table: Beads
ID (PK)
Completeness
Material
Manufacturing Technique
Bead Structure
Bead Form
Bead Shape
etc.

Table: Utensils
ID (PK)
Utensil Form
Plating
Handle Decoration
Object Length in millimeters
etc.

Table: Ceramics
ID
Manufacturing Technique
Ware
Vessel Category
Form
Mended Form
etc.

I saw somewhere how these tables could be connected to another table whose purpose is to give each record a unique identifier vis-a-vis all the records in other tables -- obviously each record has its own within-table identifier (table.ID), but I want each record to be numbered uniquely in the db as a whole. So I saw something like

Table: Generate Artifact ID
ContextSampleID (PK; to be used in other tables)
ArtifactID (foreign key, related to the ID fields in each of the previous 3 tables).

I'd post a picture or link of the relationship diagram I'm modeling after, but as a new user I cannot. My problem is that I do not understand how the "Generate Artifact ID" table works (or if it works?). There's probably something very basic I'm missing; this project is outside the scope of my usual, more modest Access projects. Can anyone enlighten me?

Many thanks in advance.
 
What about this kind of structure? -

tblArtifacts
pkArtifactAutonumberID
ArtifactIdentifier 'This would be however you identify it in the real world (Text or Number)

tblProperties
pkPropertyAutonumberID
Property 'This would be your properties, cirumference, Plating, etc.....

tblArtifactProperties
pkArtifactPropertyAutonumberID
fkArtifactAutonumberID 'Number
fkPropertyAutonumberID 'Number
PropertyValue 'Text, so that you can enter anything you need to.
 
Thanks, Krystal. I certainly see how the structure you propose makes sense, but it seems like I would then have to enter info for each record by hand -- I already have fully populated tables in Excel, and it would take some fancy cutting-and-pasting to achieve tables directly importable into this new structure, no?
 
The structure Krystal describes is the way to do it. If you don't do it this way you will have far more work to query the data.

This structure also allows an unlimited number of properties to be stored for each artifact. Moreover, new property types can be added at any time without adding fields to the tables or even entering design mode.

I also recommend you have a field for UnitID in the Properties table with a relationship to a Units table where the name of the unit is stored. This keeps the unit consistent and separated from the data value which would otherwise have to be parsed to extract the numeric section for numbers.

The Excel data can be converted in queries or using a loop through recordsets.
 
OK. But if the data type of the last field in the third table

tblArtifactProperties
pkArtifactPropertyAutonumberID
fkArtifactAutonumberID 'Number
fkPropertyAutonumberID 'Number
PropertyValue 'Text, so that you can enter anything you need to.

is set to "text", then what if I want to later sort by date of discovery/collection? If the field's data type is "text", then the sort function won't work right. Is there a way around this?

Thanks again, all.
 
Last edited:
Mik, you can generate a master record in a General Artifact table, and then include that ID in the Special Artifact tables you've demonstrated here. This join is demonstrated in red ...
Code:
[B]Special Artifact Table: [COLOR="Blue"]Beads[/COLOR][/B]
[COLOR="Blue"]ID (PK)[/COLOR]
[COLOR="DarkRed"]ArtifactID (FK)[/COLOR]
Completeness
Material
Manufacturing Technique
Bead Structure
Bead Form
Bead Shape
etc.
... where you have a general table like ...
Code:
[B]General Artifact Table:  Artifact[/B]
[COLOR="DarkRed"]ArtifactID (PK)[/COLOR]
DigLocation
Circa
etc.
And you can also create a join on the ID and TableName, demonstrated in blue, like this ...
Code:
[B]General Artifact Table:  Artifact[/B]
ArtifactID (PK)
[COLOR="Blue"]SpecialTableID (FK)[/COLOR]   [COLOR="Green"]'use this field and ...[/COLOR]
[COLOR="Blue"]SpecialTableName (FK)[/COLOR] [COLOR="Green"]'this field to join to a single special table record[/COLOR]
DigLocation
Circa
etc.
There is not a right or a wrong way to solve this problem. Each approach has costs and payoffs. Defining each value as a property as proposed above is tremendously flexible, but that comes at a cost. Datatyping problems is one, but also your user will need to explicitly select a property name AND assign a value to it. Ensuring that all the required properties of an artifact are present will be difficult. Creating a form for data entry will become quite complicated.
I recommend that before you commit to any structure you run some tests on various options and see what fits best for you.
 
is set to "text", then what if I want to later sort by date of discovery/collection? If the field's data type is "text", then the sort function won't work right. Is there a way around this?

Some aspects of the record such as the discovery date, location and any other fields common to every record are best kept in a dedicated field. Various other specialised aspects of the find can work well with the related table approach.

However the sorting problem can be dealt with by sorting on a derived field using the type conversion functions. The IsNumeric and IsDate functions can be used to determine if a value is actually a number or a date.

CInt for Integer (up to ~ 32K but generally just use Long)
CLng for LongInteger CSng for Single Precision floating point
CDbl for Double Precision
CDate for Date
 
Defining each value as a property as proposed above is tremendously flexible, but that comes at a cost.

This flexibility outweighs the other problems because they can be effieicently worked around. The big drawback of the dedicated tables is the need to add new tables and fields for the artifact types and properties the users didn't think of during the design and testing. (Don't doubt that they will, users always do.)

New tables can be added, fields can be added to tables programatically but such measures are not to be recommended. Moreover the modification of reports and forms in this way is impractical.

Datatyping problems is one, but also your user will need to explicitly select a property name AND assign a value to it.

Ensuring that all the required properties of an artifact are present will be difficult. Creating a form for data entry will become quite complicated.

One alternative to the all-text technique to deal with the datatyping is to include fields for each required datatype in the GeneralProperties table. The PropertyTypes table includes a value to indicate the datatype for each property. Usually just Text, Number (Long integer with a unit size that avoids a decimal component) and sometimes Date are enough.

The PropertyTypes applicable to each record in ArtifactTypes are held in a join table. When a new artifact record is created use VBA to populate the GeneralProperties table with the appropriate properties.

The general properties are displayed in a subform in Continuous Forms mode. There are a couple of tricks to creating an illusion that the appropriate control for the datatype appears separately for each record.

Even if VBA is not your strong suit it is probably worth the effort of learning how this is achieved. It will pay off in time saved by avoiding the need for different forms for each artifact type. In the end though you have to decide if you have the resources to take on the challenge.

I recommend that before you commit to any structure you run some tests on various options and see what fits best for you.

Definitely. Planning of the datastructure and understanding the pros and cons of these decisions is the most critical part of the design. Hasty decisions made here can cause huge complexity later. Changing structure later means major rework of the form and report design.
 
@lagbolt: Now that you've explained it, the relationships seem rather obvious. Sorry I didn't see that before...

@Galaxiom: Unfortunately VBA is not my suit at all. But thank you for the outline you gave in your last post -- I agree, avoiding a plethora of forms for different artifact types would be a huge plus. Guess it's time to find a good VBA text from the library. As usual, this project is turning out larger & more complex than expected!
 
Guess it's time to find a good VBA text from the library.

There is no motivation to learning near as strong as a specific goal especially when it comes to VBA. It is a vast field and you really only need a few small pieces to achieve what you are aiming for in your project.

It is good for a beginner to get a bit of a background to at least understand the nature of events and procedures but recognising what you need in your project by ploughing through a book is unlikely.

I would recommend reading a bit online. There are many sites with all levels of information about VBA so if you really have no experience than start by finding a primer.

Finding your specific answers is more about being able to describe the problem. First port of call is Google for the simple questions but being able to ask at sites like this is invaluable for the more obscure requirements. I got involved here because Google often found the answers here and that really opened doors.

Programming is an art to some extent and it just isn't possible to put all that in a book. The really clever bits are not found in books but in the minds of creative people at sites like this.

To a large extent pretty much anything is possible in VBA. (I will qualify that by saying that it sometimes involves very complex stuff.) Indeed many a database has been kludged with VBA to overcome problems caused by inadequate data modelling.

Your focus needs to be on getting the table structure and relationships in place. Don't even bother with more than a few sample records at the first but make sure you have a representative variety.

That is the beauty of the generalised system. There are very few tables involved so it is easy to get something up and running. Then post your proposed structure for comment either as a database (preferably mdb format) or an image of the Relationship diagram.

BTW. Best avoid using the words Property or Properties as a name for anything in your database as these are reserved words. I use Aspect as an alternative.

As usual, this project is turning out larger & more complex than expected.

You have made a good start by recognising that dealing with a large variety of different fields requires careful planning and made the right decision by looking for advice. Dealing with this complexity in the foundations of the database is the key to streamlining the development.

No doubt we will hear more from you soon.
 
Thanks, Galaxiom, for the wise words. Since I have no VBA experience, then it looks like I will begin with a primer before asking specific questions here.

No doubt we will hear more from you soon.

How did you know? j/k
 

Users who are viewing this thread

Back
Top Bottom