Dilemma designing complex tables and relations

[Television]

Hellos!

I have a problem in a following situation and would appreciate advice on it.

I am creating a database for finds from archaelogical excavation using Access 2010. Finds were recorded in paper form and now they need to be entered in to a database. Finds are associated to particular Locus (stratigrafical level, building part, etc...) and they contain different kind material (pottery, building material, etc...). Materials are further divided to several different subtypes ex. pottery can be amphora, cooking ware and so on. Each of these types have similar characteristics ex. all pottery-types have rim, base, body, etc... Amount of all the subtypes is quite large and cannot be entered in one single table so I have to create several tables, which, of course, is good database practice anyway.

Now, it would be easy to create separate tables for all the subtypes, but that would lead to a huge number of tables. Since all the subtypes contain similar kind of information, that made me to think that there could be a way to use that to create only one table for different materials. That table would contain raw data (which is mainly numerical) and I might be able to use junction tables to link it to main finds table.

My actual question is: Is that feasible way to do this? Or could there be another way to do it? If so what that could be?

It's been awhile since I have done any database related work and I have forgotten quite a lot, so this might be a silly question, but bear with me .

I appreciate any help and thanks in advance!

 

[catalin.petrut]

I always prefer to use multiple tables. And that becouse a few reasons. For more help, i suggest that you attach the form that you use now, the printed one. And some aditional info on every field in it.

 

[mdlueck]

You could do it that you have an "items" table, and look-up tables which provide pick list values to populate the "items" table. One of those look-up tables could be the type of item. That pick list may be added to over time as you encounter new types of items.

Do you foresee having more items than a Long Int data type is able to support? That data type will support 2,147,483,647 records in the table. If not, then I would suggest going that route, and make a Long Int the auto-number primary key to the table.

 

[Television]

Attached is a finds form of our project. I couldn't find final form, but this version is only missing header, which contains some identifying information about Locus, room etc., and some irrelevant formating. Pottery and Brick/Tile sections are most relevant to my problem. Everything else is pretty easy to implement.
Some explanations: In leftmost column is a name of particular pottery or other material type. Following fields of the same row contains numerical data of how many pieces (or sherds) of that particular material were found in that Locus. Top row indicates what kind of pieces are we talking about. Ex. 6 pieces of amphora rim, 3 pieces of amphora handle and so on. Most of the time Total Number and Weight could be calculated so there are no point to put them separately on table. Of course not every Locus have all the possible types, but most of the fields are empty instead.
As you can see putting every pottery and brick/tile type in separate table would create quite a many tables, maybe too many(?). And the information they would contain would be very similar to each other. Maybe there are more elegant solution to this?
Thank you!

 

[jdraw]

There is a free data model at
http://www.databaseanswers.org/data_models/archeological_site_conservation/index.htm

that may help you. It is a general model that is meant to show what someone has considered reasonable. It is not specific to your situation, and is not meant to be the only way to set up.

Good luck.

 

[catalin.petrut]

I have made a basic set of tables and a form (with subform) to insert data. Take a look!

 

[Television]

I have made a basic set of tables and a form (with subform) to insert data. Take a look!

Oohh! This looks just what I need. Needs tinkering of course, but basic idea looks sound. Thank you very much!

 

[catalin.petrut]

@Television, you welcome. I bet it needs tinkering, i'm a newbie!