Help with possible many-to-many relationship

[gcw]

Hi all - I'm a newbie here so please forgive any clumsiness/lack of knowledge...



I have a database which essentially concerns a number of objects. Each object has various pieces of unique information, and two lookups, namely find-spot and place of production. My find-spots lookup is fine, but I am having some difficulties with place of production, because while most objects have only one place of production, some have two.

My initial solution was to have two 'place of production' fields, each linked to the same lookup table. But when I run queries based on place of production, it won't pull out all the records I need (ie if I ask for place of production A, I get all records that only have place of production A, not those that have A & B)

It seems to me that I need a many-to-many relationship, but I have played around with this and can't seem to make it work. One of the problems is that, because I am the only user of the database, I tend to enter data straight into the tables (ie the objects table), and with a many-to-many relationship I feel like I have nowhere in the objects table to mark the place of production.

Any help greatly appreciated. Thanks!

[jzwp11]

If an object can be produced in many places and a production place can have many objects associated with it, then you do have a many-to-many relationship which requires 3 tables

tblObjects
-pkObjectID primary key, autonumber
other fields that pertain ONLY to the object

tblPlaces
-pkPlaceID primary key, autonumber
-txtNameOfPlace
other fields that pertain ONLY to the place


tblObjectPlaces
-pkObjPlaceID primary key, autonumber
-fkObjectID foreign key to tblObjects
-fkPlaceID foreign key to tblPlaces

[gcw]

Thanks jzwp11. That was a set up I had looked at, but it seemed to me that every time I enter an object, I'm then going to have to go to the ObjectPlaces table to associate it with a place of production. This doesn't seem very efficient.

Another suggestion that has been made to me was to have multiple versions of those objects that have more than one place of production (it's fewer than 10% in all). So the objects table would look something like this:

ObjectVersion	Place (lookup)	Count	OtherInfo
001	A	0	xxx
002:1	A	1	xxx
002:2	B	2	(null)

So any object with one place of production has "count"=0, and any with multiple place of production has two records, one with "count"=1 and one with "count"=2. Then to run a query, I can ask for either 0s & 1s, or 0s & 2s, to avoid selecting multiple versions of the same object. I am extremely wary of not having a system whereby 1 object = 1 record, and am also not sure how I can get "OtherInfo" associated with version 1 to also associate with version 2 without manually entering it a second time.

I don't like this second idea much, but wanted to mention it to see other people's thoughts, and because the many-to-many relationship as I see it currently doesn't quite function as I need.

[orange]

Here is a free video on resolving many to many
https://www.youtube.com/watch?v=7XstSSyG8fw

[jzwp11]

The properly normalize way is as I showed in my post. If things change over time (>10%) you can be setting yourself up for problems.

[orange]

I agree with jzwp11, and I would add that I would make a unique compound index of these 2 fields
-fkObjectID foreign key to tblObjects
-fkPlaceID foreign key to tblPlaces

in tblObjectPlaces.

This will prevent duplicates in this table.
Good luck