One-to-ones or one big one? And other questions (1 Viewer)

ArielSha

New member
Local time
Today, 13:21
Joined
Feb 16, 2016
Messages
8
I've started working on a database some weeks ago, and some of it works great, yet some doesn't...
The database is meant to gather data on archaeological objects from excavations - specifically objects made of bone, antler, horn etc. It already has a lot of tables... maybe too many? :confused: And with the many tables come many questions. I'll start with one or two and we'll see how it goes.
At the end of the post (in the code box) I added a link to an image of the relationships view. Just add the http thing as prefix

So the "central" table in the database is:
OBJECT_BASIC_DATA
ID - (Autonumber, primary key)
ObjectID - (number, another primary key) = this is a number given to an item by me for the research. I'm not sure I need it, since I have the ID (autonumber) field, but with the mistakes and deletes I did over the past weeks the autonumber is already at 100+, while the table has maybe 8 rows. I also use this field in 1-to-1 relationships to other tables (see below).
other fields in this table are connected (as child fields) to other parent tables (Sites table; References table; Archeo_context table; Archit_context table) and they seem to work perfectly.

Now, OBJECT_BASIC_DATA (OBD for short...) had many many fields and it was getting too big, with many of the fields empty, so I decided to split it into many smaller tables (OBJECT_MEASURMENTS; OBJECT_TYPOLOGY; OBJECT_RAW_MATERIAL; OBJECT_TECHNOLOGY; OBJECT_CHRONOLOGY; OBJECT_TAPHONOMY; OBJECT_PICTURES; OBJECT_PARALLELS). I tried to make 1 to 1 relationships between these tables and OBJECT_BASIC_DATA, since every record in OBD could only have 1 record in these tables and vise-versa. I used the ObjectID field, which all of them have, as the relationship field, although it is a primary key only in OBD (where it primary keying with ID [autonumber]). I'm not sure I made the right decision splitting the big table into many small ones (about 5-10 fields in each). I'm also not sure about using both ID and ObjectID as primary keys in OBJECT_BASIC_DATA, but ObjectID is the only sure connection I have between OBD and the other OBJECT_TABLES above.

Next issue... I've created a form to enter new records. It looks really nice and organized and colorful. But it doesn't work. How do I make Access understand that when I type a number in the OBD.ObjectID control,it needs to put that value in all the ObjectID fields of the other tables related to it? I tried screaming at it, but it didn't work.

I'm not sure I can post pictures, so I add a link to an image of the tables relationships. Its a mess, but its as clear as I could make it. Just add http at the prefix.

Code:
prntscr.com/a48pcl
Hope to hear some opinions and suggestions
Sorry for the extremely long post
Ariel
 

sneuberg

AWF VIP
Local time
Today, 04:21
Joined
Oct 17, 2014
Messages
3,506
It looks really nice and organized and colorful. But it doesn't work. How do I make Access understand that when I type a number in the OBD.ObjectID control,it needs to put that value in all the ObjectID fields of the other tables related to it? I tried screaming at it, but it didn't work.
You could avoid some of this screaming by not creating one-to-one relationships. There is almost never a good reason to put data in different tables in this fashion. If you are getting near the Access limit of 255 fields per table your data probably isn't in normal form.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:21
Joined
Apr 27, 2015
Messages
6,286
It sounds like instead of having so many tables, what is needed is a field that describes the type of object: MEASURMENTS; TYPOLOGY; RAW_MATERIAL; TECHNOLOGY; CHRONOLOGY; TAPHONOMY; PICTURES; PARALLELS.

A look-up table or a value list for the field would ensure integrity. Without seeing the tables, advice is somewhat limited.
 

ArielSha

New member
Local time
Today, 13:21
Joined
Feb 16, 2016
Messages
8
Thanx for the replies guys, I really appreciate your help!

It sounds like instead of having so many tables, what is needed is a field that describes the type of object: MEASURMENTS; TYPOLOGY; RAW_MATERIAL; TECHNOLOGY; CHRONOLOGY; TAPHONOMY; PICTURES; PARALLELS.
So, for example, if the OBJECT_MEASURMENTS table has the following fields:
ObjectID [relating to ObjectID in the OBJECT_BASIC_DATA table] Width,
Thickness, Length, Diameter, CompWidth, CompThickness, CompLength, DrillDepth, DrillDiameter.....
How do I fit all this data into one MEASURMENTS field? Or should I just have all these fields in the OBJECT_BASIC_DATA rather than in a separate table?

Without seeing the tables, advice is somewhat limited
Is there a way to show the tables?

Thanx again,
Ariel
 

Grumm

Registered User.
Local time
Today, 12:21
Joined
Oct 9, 2015
Messages
395
You posted an image of the tables... so he can see them. Maybe didn't took the time to do it.
Anyway, I think you missed the point of id's and primary keys.
"How do I make Access understand that when I type a number in the OBD.ObjectID control,it needs to put that value in all the ObjectID fields of the other tables related to it? I tried screaming at it, but it didn't work."
From what i see, some tables are correct, some are a little vague for me.
But you need to see this in different steps. You first need to create a new basic data record, THEN create the correct records in the other tables using the ID you created first.
It is strongly not recommended to change primary ID's.
 

plog

Banishment Pending
Local time
Today, 06:21
Joined
May 11, 2011
Messages
11,613
Agree that 1-1 relationships are wrong. However, I think your big issue is storing values in names. The best example of this is numerated field names (Image_1, Image_2, Image_3...). Tables should grow vertically (with more rows) and not horizontally (with more columns). So, while the 1-1 relationship is wrong for OBJECT_PICTURES, you still should have OBJECT_PICTURES, but it should have this structure:

OBJECT_PICTURES
ImageID, autonumber, primary key of table
ObjectID, number, foreign key to OBJECT_BASIC_DATA
Image, will actual image
Description, text, will hold description of what image is
*ImageNum, number will hold number of image

*ImageNum probably isn't necessary, I put it there in case the suffix (_1, _2) of all those field names is actually relevant. Most likely it isn't, its just you used those numbers to give each field a unique name.

That's how OBJECT_PICTURES should be structured, with just 4/5 fields, not the 10 in there now. Then when you want to add 4 images to an OBJECT_BASIC_DATA record, you make 4 records in that table not 1.

You've made that mistake in a lot of tables--and not just numbered fields. OBJECT_CHR has the same issue, but you didn't number your fields, you used a prefix that should be a value in the table, not a field name. Excavator, Ref_Based, Suggested all should be values in a field (for example CHR_Type) and not in the names of the fields. Same issue with OBJECTS_MEA & OBJECT_TAPH--the field prefixes should be values held by a field and not in the names of a fields.

Lastly you did the same thing with a few tables. Use_wear, weathering and Burning should not be table names, they should be values in a table. All 3 of those tables should have their data combined and a new field should be added to say if each record in it is for Use_wear, weathering or Burning.
 

Mile-O

Back once again...
Local time
Today, 11:21
Joined
Dec 10, 2002
Messages
11,316
Here's a quick table structure for measurements and the main objects.

tblObjects
ObjectID (Autonumber, primary key)
ObjectName (Text)
...
(stuff related to the object)

tblMeasurements
MeasurementID (Autonumber, primary key)
MeasurementTypeID (Number, foreign key)
Measurement (Number)

tblMeasurementTypes
MeasuremenTypeID (Autonumber, primary key)
MeasurementType (Text)
MeasurementScaleID (Number, foreign key)

tblMeasurementScale
MeasuremenScaleID (Autonumber, primary key)
MeasurementScale (Text)

tblMeasurementsToObjects
ObjectID (Number, primary key, foreign key)
MeasurementID (Number, primary key,foreign key)


I've added tables for Measurements, Measurement Types, and Measurement Scales as well as a junction table since once many Objects can have many measurements.
 
Last edited:

Mile-O

Back once again...
Local time
Today, 11:21
Joined
Dec 10, 2002
Messages
11,316
ObjectID - (number, another primary key) = this is a number given to an item by me for the research. I'm not sure I need it, since I have the ID (autonumber) field, but with the mistakes and deletes I did over the past weeks the autonumber is already at 100+, while the table has maybe 8 rows.

Unless you need a very specific unique code structure for your objects then the autonumber should suffice. That you are losing numbers through deletes shoudl not matter as the number is ultimately meaningless and should remains that way, Or, in other words, don't ascribe any logic or meaning to your autonumber; it's there purely to ensure uniquely coded records, rather than replicate any other system you may have in mind.
 

ArielSha

New member
Local time
Today, 13:21
Joined
Feb 16, 2016
Messages
8
OH WOW!!
Thank you guys, this gives me a lot to work on!
Its amazing how experienced designers can point these flaws so clearly, while I can stare at the screen for hours and never figure it out.
A lot to learn....
I'll get to work, and let you know how its going. Will probably take me a few days.

Ariel
 

Users who are viewing this thread

Top Bottom