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

[norseman]

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

I will take a hard look at this and let you know what I end up with.

[norseman]

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.

I would love a picture if you have one.

BTW do you do any wood turning? (like stuff on a lathe)

P.S. Do you do anything with Entity Relationship Diagrams (ERDs)? I think I am going to go back to square one and start with some new "Business Rules" and then work those into a set of ERDs.

[Micron]

As soon as I vacuum some of the particulate from the felt I will take a pic tomorrow. Here's what a white/purple 2 drawer one looks like. I have made 3 of this style so far, but I won't make 2 the same.
EDIT - No I don't do any turning.

[norseman]

You did a GREAT job on this, well done.

Do you do any lathe work?

[Micron]

I might have edited that post after you saw it to say I don't do any turning, so no, no lathe.
Thanks.

[Micron]

As promised (hopefully images are not too small after resizing:

[Minty]

Very Nice.

I'd consider placing an order for one, but that would encourage Mrs Minty to buy more expensive things to fill it up with...

[Micron]

Thanks. I expect zero orders given that people generally don't want to pay for quality. There's over $100 in material costs there and that doesn't include glue, stain, urethane or the wood (which for that one is 100% solid cherry).

[norseman]

Thank you for your reply

I am coming to the conclusion that I need three tables, much like your tblYourTrees: YourTreeID, TreeID. I am working on it.

The real problem may be looming in the wings. There are members of the group (wood collectors of which I am not one of) want me to make the relationship from common name to botanical name a many-to-many. In reality, it depends on what source you use as to what the common name is and what the botanical name is. There no "master" reference of the relationship of common name and botanical name. I am pushing for the one-to-one so that (at least within the group) there will be agreement of what goes with what. After this is built, it could be published so that others may weigh in and get some consistency that everyone (good luck) can agree on.

[norseman]

What wood and finish did you use on this box?

[norseman]

For the woodworking I do, I like the doing, not the having. When I complete something I ask the wife if she wants it, and if not I give it away to someone who wants it. I am with you I do not accept any commissions for any work I do. For me, to make any money, the buyer would never want to pay what I would need to charge.

[Micron]

What wood and finish did you use on this box?

That is cherry with cherry stain and Varathane water based finish.
EDIT - I would accept commissions, it's just that I don't seem to have the marketing skills to get them. One exception so far would be this:
https://www.access-programmers.co.uk...a-gent.310154/

[mike60smart]

Hi
I have attached a Form that deals with the relationship between the two tables.
Instead of using CommonName I added Autonumber Primary Keys to both tables.

I then linked the PK from the MasterTreeTable to the FK in MasterData Table

[norseman]

Thank you Very Much. I will take a look at this when I can. This project has to tabled for right now to deal with some personal issues. I will let you know when I get a chance to look at it