Relationships between records in the same table

[Ilkams]

Hi, I'm having trouble working out how to relate/associate records within a table to each other and would appreciate any suggestions. Let me try to explain...

I have a table with information about individual museum objects, an Objects table. Each object has a unique collection number, e.g. TH4000 (which is the primary key). However, some objects within this table have a relationship with each other and are then given part numbers, e.g. a teapot may consist of 3 pieces, a lid, a strainer, and a body (all of which need their own object record), and would be numbered TH2000.01, TH2000.02, TH2000.03. I need to be able to relate the part records to each other so that TH2000.01 shows that it is related to TH2000.02 and TH2000.03 and that TH2000.02 shows that it is related to TH2000.01 and TH2000.03 etc. I understand that I will at some point have to manually indicate that TH2000.01 is related to .02 and .03 but I would like to do it only once and in a way that .02 and .03 then know they are related not just to .01 but to each other.

I'm sorry if I'm missing something very obvious, I've returned to Access after a 7-year absence and I'm a bit rusty!
Thanks for your advice.

 

[David Eagar]

1 to Many: Objects table (1) to Parts (or whatever you want to call it) Many table

 

[jdraw]

Further to David's response, there should not be a relationship between records in the same table. When you see this, it is time to separate the "data" into a second table. As David noted, you have an Objects table, and you need to create a Parts table and establish the relationship between the tables.

In this case, you have 1 Object can be made up of Many Parts.

Example:
A teapot is made up of a Lid, a strainer and a body.

 

[Rabbie]

Further to David's response, there should not be a relationship between records in the same table.

Not strictly true. This link will give you an example where a self link is the best solution.

 

[Ilkams]

1 to Many: Objects table (1) to Parts (or whatever you want to call it) Many table

Thanks David, but I'm trying to avoid using tblObjectsRelated since it will have all the same fields in it as tblObjects - seems a bit redundant..

 

[Ilkams]

Not strictly true. This link will give you an example where a self link is the best solution.

Thanks Rabbie, I will give this self/reflexive join method a go.

Somebody has also suggested I could put a ParentID into the tblObjects. Actually, I'd need a ParentID and a GrandparentID in the tblObjects, since there are sometimes three levels of objects e.g. a tea set (grandparent), a teapot (parent), and a teapot lid (child).

 

[Galaxiom]

Somebody has also suggested I could put a ParentID into the tblObjects.

This is the essence of the self join when there is a single parent object.

However do not be tempted to use multiple fields for the parent objects. Firstly you could have a problem with ensuring the same parent object is not entered in multiple fields. It can be done in code but it is clumsy.

Where there are multiple parents, a join table is a better solution and allows an unlimited number of parents.

The join table has fields ObjectID and ParentID with a record for each parent. It may also contain a ParentTypeID. Using the person example they may have birth-parents, step-parents. adopted parents etc.

Do not be tempted to indicate Father and Mother as this breaches normalization. Only a woman can be the mother and only a man the father (at least for now) so the sex of the parent record determines part of the nature of the relationship and must not be indicated in the record.

The situation mentioned above of preventing duplicate parent entries is easily controlled in the join table by a multifield index (no duplicates) on ObjectID and ParentID.

Do not include grandparents into this situation.

Actually, I'd need a ParentID and a GrandparentID in the tblObjects, since there are sometimes three levels of objects e.g. a tea set (grandparent), a teapot (parent), and a teapot lid (child).

This would be a breach of normalization.

A child record with both parent and grandparent values could conflict with the records belonging to that child's parents.

The self join (optionaly via a join table) is the correct solution and can be extened to as many aliases of the main and join tables as necessary.

Such constructs are not uncommon and certainly not a reason to separate the data into more tables. Indeed such a move could catastrophically breach normalization.