Is there a easy was to post relationships, and tables on here w/o typing them out? I need some advice on table, relationships, keys etc..
thanks

You can take a screen shot (Alt|Print Scrn), save it as an image, and post it on here.

Ax

not sure I am doing the pict correct... give it a try

You did it right, good job! OK, a couple things I see right off:

1. In the FarmAgreements table, you can remove the FarmAgreementID and just use the FarmID and AgreementID as the primary key.
2. In the SubFarmDetails table, you can remove the SubFarmDetailID and just use the FarmID and SubFarmID as the primary key.

What other advice do you need?

Ax

I only changed the FarmAgreement junction table, is that correct? Make both FarmID and AgreementID primary keys?

So a unique (pk) is not needed in the junction tables?


It has been a long time sense doing this stuff. The whole conceptiual relations thing is difficult (and to relate it to DB design). I am a little confused, when I make a form to add farm information and try and connect a subform within the farm form, the existing defined relations do not allow for me to add it that way. What am I doing incorrect, need to have separate forms, create a query with different relations to make forms based on new relations. Let say to add, farm information, which can include sub farms names and agreement types.

thank you for your help & advice.

You may not even need the junction tables, come to think of it. Let me explain.

If each Agreement can only be specific to one farm, then you can just put in the FarmID in the Agreements table as a foreign key. The FarmAgreements table could then go away. If an Agreement can include multiple farms, you'll want to leave it as it is.

If a SubFarm can only be specific to one farm, then as above, you can just put in the FarmID in the SubFarms table as a foreign key. The SubFarmDetails table could then go away. If a SubFarm can be included in multiple farms, you'll want to remove the SubFarmDetailID and just use the FarmID and SubFarmID as the primary key in the SubFarmDetails table.

A better explanation of your requirements may be necessary to give you a better answer. It will be best to nail down the requirements for the relations before attempting to troubleshoot form problems.

I will try, seems like the more you look at it, I keep discovering something new. Hope I am expressing it correct.

1 farm can have 1 to many agreement types.
Agreements types can have 1 to many farms.
If many agreement types existed for 1 farm. They would need to be associated some how, I would presume by name (sub farm name). Maybe I need to move the table relations for table agreements to subfarms?

1 farm can have 0 to many sub farms.
I could always use the same name “farm name” as a subfarm name? This would be 1 to many relations if always 1 name is used. So that would be -
1 farm can have 1 to many sub farms (parent to child)
Sub farms (child to parent)

HarvestInfo, Contracts, Tickets are all child to parent (sub Farm) And I should add Agreements as a child to sub farms?

If the agreements relate more to the sub farm, then I would suggest moving the relation to the sub farm.

Correct me if I'm wrong, but this is what you would have then:
Farms-->SubFarms (one-to-many)
Contracts-->SubFarms (one-to-many)
HarvestInfo-->SubFarms (one-to-many)
Tickets-->SubFarms (one-to-many)
SubFarms-->Agreements (one-to-many)

Ax

Yes, I believe the relations are correct and I moved the Agreement table relations to the subFarms Table.

I also removed the FarmAgreements table, and the SubFarmDetailID primary key from subFarmDetails table. Made both SubFarmID and FarmID into primary keys and joined them with Farm and SubFarm Tables. Thanks for your help.. how is this looking?

sure glad you can upload attachments.

I think you're close.

1. You will only need the SubFarmDetails table if two or more farms can have the same SubFarm. Otherwise, just use the FarmID as a foreign key in the SubFarms table.
2. You have it set up so that one Agreement can relate to multiple SubFarms, is this correct?

So the relationships you have right now are as follow:
Farms<-->SubFarms (many-to-many)
Agreements-->SubFarms (one-to-many)
Contracts-->SubFarms (one-to-many)
HarvestInfo-->SubFarms (one-to-many)
Tickets-->SubFarms (one-to-many)

Ax

Somehow I deleted the products table, added it back in.

2 or more farms can have the same sub farm "name" field. Example; You have a farm named home, I have a farm named home.

2 farms normally will never have the same sub farm records, just maybe same name. At least for the type of information I am trying to store here. I am a little confused, not sure I am understanding or explaining myself well.

Agreements should be many to many. One sub farm could have more than one Agreement. I need a junction box through the subfarms table?

added the junction table

Be careful not to 'over-complicate' or 'over-normalize' your relational diagram. Keep in mind that the more "junction" type tables you create (such as those tables with only 2 id fields), the more tables you have to link into each query/form/subform to return/update the data (which usually means additional subform complexity/longer query returns). It looks like your last diagram compared to the first few is tending to overnormalizing the structure. This may or may not be a necessity. I usually avoid tables which ONLY hold 2 ID fields (unless it's a necessity). This tends to complicate coding and form design where it would've been easier to simply keep the foreign key ID in one of the other tables versus creating a separate 'intermediate' table which only holds 2 id fields.

Without knowing the details of your requirements though, I can't say if this is something you should/should not do. I just usually avoid tables with just 2 id fields (unless again, the requirements dictate the absolute need of doing so.)

It's very easy to over-normalize which is not necessarily good. The more 'separate' joining tables you have, the more complex the form/subform design will be.

This project started off as a spreadsheet, being asked for more and more information. Long story said.

Starting off with object IDing for tables. Now this is slowly going backwards to ER and normalization and functional dependencies. Of course, like to keep this as simple as possible.

This part to me this is one of the hardest steps, and if not done correct often leads to much complications down the road.

The 2 junction tables are for many to many relations. Should I join those tables?

Sub farms are functionally dependent on farms.

All other tables - are sub farm dependent