Data Table Relationships - one-to-one or one-to-many

[norseman]

I am having a problem with database design in Access 2021. I am building a database to track my wood inventory (935 unique species of wood).



Yes, I know that any given common name for a piece of wood can have multiple Botanical Names, and any given Botanical Name can have multiple Common Names. For my sanity’s sake I have reduced it to one common name has one and only one botanical name.

I have one table that I call MasterDataTable and one table I call MasterTreeReference. Their structure is shown below. I have double and triple checked to ensure there are no duplicates in both tables CommonName field and in the BotanicalName field of the MasterTreeReference

MasterDataTable
PK	WoodID	Number
	CommonName	Short Text
	Flat	Yes/No
	Turn	Yes/No
	Pen	Yes/No
	Other	Yes/No

MasterTreeReference
PK	CommonName	Short Text
	BotanicalName	Short Text
	Origin	Short Text
	SG	Number
	Janka	Number

The purpose of this join is to garner the BotanicalName, Origin, SG, and Janka data on an input form I am trying to create that lists all the wood I currently own.

On the form itself if I have tried invoking the query builder and joined the two tables on the CommonName field and I get all the correct data that I want from both tables

Also, note that the MasterDataTable is of the wood I own (939 unique species of wood), and the MasterTreeReference is a list of over 4,071 (and still growing) different trees from around the world.



When I go to the relationships screen to connect the CommonName in the MasterDataTabe to the CommonName in the MasterTreeReference table it tells me that there is a one-to-many relationship.

Why? Shouldn't the relationship be one-to-one?

I have attached a .accdb file that contains a reduced set of records from both tables

(I would put my expertise level at about a 4 on a 1 to 10 scale)

[June7]

What do you mean by unique piece? Only one of each botanical/common name? E.g., only one piece of Mulberry? If that is the case, I don't see need for 2 tables.

If you want one-to-one relationship then have to link on common unique ID fields. I.e., WoodID would be unique PK in both tables.

If this really is 1-to-1 relationship, then definitely don't need 2 tables for your data.

1-to-1 is usually only called for when a data structure requires more fields in a table than Access allows (limit of 255 fields). This is rare (although I have done it).

Review this discussion https://www.utteraccess.com/topics/2.../posts/2821467

[CJ_London]

if relationships are one to one then you don't need the two tables, just combine the fields into one table. But if you ever ended up with two samples of the same common name then you have a one to many relationship (for example you might have two examples of tulipwood, one flat and one turn)

Note that your tree reference table should have an autonumber PK and the FK should be in the other table - you should not use common name since that can be changed.

And if you really have a one to one relationship and you want to keep two tables then ensure you do not allow duplicates in the master data table

[Micron]

Late to the party, but since I had this in Notepad during my dallying I'm not going to just dump it.
I'd say it depends on how you're using CommonName. If you mean that Acer is the botanical name for maple, and a piece in your inventory is maple then it is the same word and CommonName is not something peculiar to each table (i.e. no two CommonName values are the same in either table).

In that case I think BotanicalName is the pk in reference table. CommonNameFK is the foreign key in reference that relates back to CommonNamePK in master. This assumes that you intend to relate Acer to your many pieces of Maple in your inventory. However, if you intend to put a number id on every piece you have then that isn't right. I guess you need to clarify what common name means in each table and if you're tagging each piece with an ID. To do that I think Acer and Maple should be in botanical and common name fields in the same table and the pk from reference is the fk in your table for wood stocks.

[norseman]

[QUOTE=June7;522187]What do you mean by unique piece? Only one of each botanical/common name? E.g., only one piece of Mulberry? If that is the case, I don't see need for 2 tables.

If you want one-to-one relationship then have to link on common unique ID fields. I.e., WoodID would be unique PK in both tables.

If this really is 1-to-1 relationship, then definitely don't need 2 tables for your data.

1-to-1 is usually only called for when a data structure requires more fields in a table than Access allows (limit of 255 fields). This is rare (although I have done it).


I am not sure you are understanding my question. My question is about why Access is trying to force a one-to-many relationship when one does not exist. It should be a one-to-one. This should be a very simple PK/FK situation, but I am not sure Access is seeing that way. Or maybe, I just do not understand enough of how Access works.


Thank you for your response

[norseman]

In reply to CJ_London

In nearly a thousand species of wood I have not had a situation where I had two samples of the same common name that were not the same wood

I have not tried reversing the roles of the tables where the FK is in the MasterDataTable. It is something to think about

Thank you for your reply

[norseman]

In reply to Virtually Inert Person

In this case every piece of Maple will have a unique WoodID number. However, having said that, I do not just list Maple as a cover for the whole family of maples. What I have is Big Leaf maple, Common Maple, Hard Maple, Japanese Maple, Red Maple, and Norwegian Maple (to name a few). Each of these "Maples" has a very distinct Botanical Name, and so a distinct WoodID Yes, they are all branches off the Acer genus, but they all have distinct characteristics that I am tracking (in other parts of the system).

I have over 4000 unique species of wood in the MasterTreeReference and each one of them has a unique Common Name. In reality (which I am currently trying to ignore) it is really a many-to-many relationship where a given common name can have many botanical names, and a given botanical name can have many botanical names. I have even seen a few cases where two pieces of wood are given the same common name AND the same botanical name, but they are really two different pieces of wood. There is no standard to go by in the botanical world, it sometimes seems that the difference is really who published what in what publication.

I will need to think about what should be the PK and what should be the FK and how that relates to the other tables in my system. I will need to go back to my ERD's and try to take and alternative path to the tables.

Thank you very much for your reply, it has given me something to think about

[Micron]

However I was able to assist, it's nice to know that I might have in some way.
FWIW I fancy myself as a bit of a wood-worker and have a smattering of understanding of some of the terms you use, but I can't imagine ever being interested in woods to such a deep level. I concern myself with things like does its hardness/brittleness/color stability/porosity/grain/figure/moisture content/suitability for planned finish/etc. fit the project. And that doesn't even encompass tone woods. I think I'm less a of collector than you for sure. I wouldn't even have room for half as much stock as you seem to have!

I must have misunderstood this

For my sanity’s sake I have reduced it to one common name has one and only one botanical name.

because I thought that meant that all variants of a species (?) were going to be in one bucket, e.g. maple. My suggestion was predicate on that.

Getting back to your original question, the fact that you have no duplicate records in any field does not matter. Your design permits it because one field is a pk (which dictates that in every record that field is unique) and in the other table, it is not - or at least I assume so otherwise you probably would have revealed that. You may be able to fix the problem by indexing the related table field and set its Allow Duplicates property to No. That does not mean everything is OK, but I say that because I'm not 100% clear on what the db is supposed to support.

[June7]

Not sure how to explain it better.

Access is forcing 1-to-many because that's what you have set up.

For 1-to-1, need common unique identifier key in both tables. Either WoodID or CommonName. Did you even try? Set both CommonName fields as PK then set Relationship between them. What happens?

If this really is 1-to-1 data, you don't 2 tables because 1 table can hold it all.

[ArviLaanemets]

Also, note that the MasterDataTable is of the wood I own (939 unique species of wood), and the MasterTreeReference is a list of over 4,071 (and still growing) different trees from around the world.

This is from where your problems are starting from!

The master table must be 'a list of over 4,071 (and still growing) different trees from around the world'. And what you have of them in corner of your garden or whereever, must be a child table (i.e. derived info). Like:
tblTrees: TreeID, BotanicalName, CommonName, ... (where fields BotanicalName and CommonName must be defined as unique indexes in addition to primary key TreeID);
tblYourTrees: YourTreeID, TreeID, ...
Or you can have simply a field in tblTrees, which is indicating, that you have it (values like Yes/no, or 0/1, or whatever).

In case you want a tree to have several different common names, the structure will be:
tblTrees: TreeID, BotanicalName, ... (where fields BotanicalName must be defined as unique index in addition to primary key TreeID);
tblTreeCommonNames: TreeCommonNameID, TreeID, CommonName (In case you want to ensure, that any common name belongs only to one kind of tree, then CommonName must be defined as Unique index in addition to primary key TreeCommonNameID. In case you want to allow the same common name for different kinds of trees, you don't make CommonName as unique index, and you probably need then additional field(s) to determine where or by whom this common name is used.). And in case you want to determine one of multiple common names as the name you use for your collection, you can have an additional field in tblTreeCommonNames where you can do this;
tblYourTrees: YourTreeID, TreeID, ... (Or again simply a field in tblTrees - it really depends on, are you adding any additional info specific for trees you own, or not.).

NB! Whatever design you prefer, the info about names (both botanical and common ones) is linked to tblTrees only.

[norseman]

However I was able to assist, it's nice to know that I might have in some way.
FWIW I fancy myself as a bit of a wood-worker and have a smattering of understanding of some of the terms you use, but I can't imagine ever being interested in woods to such a deep level. I concern myself with things like does its hardness/brittleness/color stability/porosity/grain/figure/moisture content/suitability for planned finish/etc. fit the project. And that doesn't even encompass tone woods. I think I'm less a of collector than you for sure. I wouldn't even have room for half as much stock as you seem to have!

I must have misunderstood this
because I thought that meant that all variants of a species (?) were going to be in one bucket, e.g. maple. My suggestion was predicate on that.

Getting back to your original question, the fact that you have no duplicate records in any field does not matter. Your design permits it because one field is a pk (which dictates that in every record that field is unique) and in the other table, it is not - or at least I assume so otherwise you probably would have revealed that. You may be able to fix the problem by indexing the related table field and set its Allow Duplicates property to No. That does not mean everything is OK, but I say that because I'm not 100% clear on what the db is supposed to support.

I am more like you than I seem to let on. This db is not just for me, but is intended to be used by SERIOUS wood collectors. The kind that get out microscopes to look at the grain structure. The wood I have is primarily intended to be used. Probably over 90% of my wood are in Pen Blank form. This way if I like it I can make a custom pen out of it. I do trading with some collectors if they have something I do not.

To the second part of your reply. The CommonName in the MasterDataTabled is indexed and set to no duplicates. My apologies for not including that little nugget of information. Unfortunately, it did not help.


Again, Thank You very much for taking the time to reply. It is appreciated

[Micron]

Then the index and no dupes is not enough; it probably needs to be as suggested in post 7.
I never knew people collected wood. I buy wood and collect sawdust/shavings! Have not downloaded your db because to make any sure fire suggestions would require a good understanding of the process and I don't think I have that yet. However I will say that my suggestion in post 4 is not correct based on what I think I know of the process. I now think that you need 3 tables. BotanicalIDpk is an autonumber in reference and an fk in tblCommonNames; tblCommonNames.CommonNameIDpk is an autonumber pk and an fk in tblWoodStocks. My guess is that you want to distinguish between the various maples that you can/do have. I will throw something together in a spreadsheet with that in mind.

[Micron]

TBH I don't have a great grasp of the whole botanical/species/genus/common name stuff so this is probably not spot on. Also, I don't know if you'd want to split maples out or group them by botanical (e.g. Acer). The following presumes not grouped by a class of Maple. It would not make sense to use functions to extract Maple from data like "Big Leaf Maple" if you want to search by "maple". You should have a field for whatever you'd call that which defines maple as a group.

tblBotanical
BotanicalIDpk	Bontanical
1	Acer
2	Fraxinus nigra
3	Malus
tblCommonNames
CommonIDpk	CommonName	BotanicalFK
1	Big Leaf Maple	1
2	Red Maple	1
3	Silver Maple	1
4	Black Ash	2
5	Green Ash	2
6	White Ash	2
tblStock
StockIDpk
1	CommonNamefk	more fields for item characteristics
2	1
3	1
4	2
5	2
6	3

HTH

[norseman]

Then the index and no dupes is not enough; it probably needs to be as suggested in post 7.
I never knew people collected wood. I buy wood and collect sawdust/shavings! Have not downloaded your db because to make any sure fire suggestions would require a good understanding of the process and I don't think I have that yet. However I will say that my suggestion in post 4 is not correct based on what I think I know of the process. I now think that you need 3 tables. BotanicalIDpk is an autonumber in reference and an fk in tblCommonNames; tblCommonNames.CommonNameIDpk is an autonumber pk and an fk in tblWoodStocks. My guess is that you want to distinguish between the various maples that you can/do have. I will throw something together in a spreadsheet with that in mind.

Yes, there is a large community of individuals (and clubs) all around the world that are very serious about wood collecting.

I can't thank you enough. There is no rush on this, I am doing it when I have time. I know what I have and I know how to identify it.

[Micron]

Please let us know where you end up, design-wise.

P.S. Just finished a solid cherry jewelry box today; 3 drawers, 2 doors and storage under the lid as well. Brass knobs with matching wood inserts. Doesn't look too bad, methinks.