Hi all, I need some help designing a database for archaeological features from an excavation. Even though I have been using and setting up rather simple access databases for years, I still consider myself quite a noob on the matter. Since I have to develop a database for my own research now, I'd like to do it 'right' way from the start.
I already made several tables and entered some data, but after some reading I realized I may have to change some things.
See the snapshot of my main table, tbl_FEATURES, attached.
Features have two identifiers: the square_ID (the units in which the site has been excavated, square trenches) and their own feature ID.
Properties feature_type and feature_subtype refer to separate tables.
The most important issue I have is how to store the chronological sequence. Each feature has one or more strata and one or more levels. The strata are square dependent, so each square has its own unique stratum order.
The levels are site-wide and are used to connect the strata from the different squares with each other.
Question group 1:
At the present, as you can see, I've put the levels in the features table. For each level a separate column with either a 0 or a 1 to indicate absence or presence of a feature in a certain level. This setup doesn't keep to the rules of database normalization, as I have just discovered. But what's a good alternative way to deal with this?
In the design it is also important that I can quickly tick the appropriate levels and strata in the data-entry form I'm using, as there are quite some levels and strata.
And should I put my levels and strata in separate tables as well?
Question group 2:
The project supplied me with a 'concordance' spreadsheet (excel) that relates all levels to the strata of each square (see 2nd attachment). As you can see, sometimes a stratum spans several levels, and on others several strata together are represented by just one level.
I need to put this info in a table as well. At the present I have a table called tbl_stratum_to_level which lists each unique case.
What I want in the end is the following:
I make a new entry of a feature, using a form. I tick all strata that the feature belongs to. Then I want a query that looks in the concordance table for the correct level. As stated, this may sometimes be several levels for one stratum, and at other other times several strata for one level.
In the end all of this needs to be connected to a time enabled GIS in order to visualize the changes that occur over time (features appearing an disappearing).
Ask me if I need to clarify. I tried to be a bit brief in order not to overwhelm you with info.
Looking forward to your suggestions!
I already made several tables and entered some data, but after some reading I realized I may have to change some things.
See the snapshot of my main table, tbl_FEATURES, attached.
Features have two identifiers: the square_ID (the units in which the site has been excavated, square trenches) and their own feature ID.
Properties feature_type and feature_subtype refer to separate tables.
The most important issue I have is how to store the chronological sequence. Each feature has one or more strata and one or more levels. The strata are square dependent, so each square has its own unique stratum order.
The levels are site-wide and are used to connect the strata from the different squares with each other.
Question group 1:
At the present, as you can see, I've put the levels in the features table. For each level a separate column with either a 0 or a 1 to indicate absence or presence of a feature in a certain level. This setup doesn't keep to the rules of database normalization, as I have just discovered. But what's a good alternative way to deal with this?
In the design it is also important that I can quickly tick the appropriate levels and strata in the data-entry form I'm using, as there are quite some levels and strata.
And should I put my levels and strata in separate tables as well?
Question group 2:
The project supplied me with a 'concordance' spreadsheet (excel) that relates all levels to the strata of each square (see 2nd attachment). As you can see, sometimes a stratum spans several levels, and on others several strata together are represented by just one level.
I need to put this info in a table as well. At the present I have a table called tbl_stratum_to_level which lists each unique case.
What I want in the end is the following:
I make a new entry of a feature, using a form. I tick all strata that the feature belongs to. Then I want a query that looks in the concordance table for the correct level. As stated, this may sometimes be several levels for one stratum, and at other other times several strata for one level.
In the end all of this needs to be connected to a time enabled GIS in order to visualize the changes that occur over time (features appearing an disappearing).
Ask me if I need to clarify. I tried to be a bit brief in order not to overwhelm you with info.
Looking forward to your suggestions!
