This is where it would be good to have some in-depth knowledge on forms/subforms design. The more you keep the relationalship without the intermediate table, the more you can easily design the subform. The best advice I can give is to look at the tables whcih have only 2 id fields and see (given your requirements), if you could make it fit into one of the other tables as a foreign key. Otherwise, plan on a little more complex form design to acommodate the intermediate table.

This doesn't mean that my farm is the same as your farm. I could have different contracts, harvest data, agreements, tickets, or products than yours does, even though my farm is named "home" as well. If you don't need to use the same subfarm in multiple farms, I'd remove the SubFarmDetails table between the two.

Now a subfarm, as you have it laid out, can only have one harvest, contract, agreement, product, and ticket. In fact, a subfarm is basically defined by the combination of these items. I may be mistaken, but these relationships seem reversed. You would want multiple harvests, contracts, agreements, products, and tickets per subfarm. The subfarm would exist outside of these items (I hope). If I'm on the right track, then you should include the SubFarmID as a foreign key in the HarvestInfo, Contracts, Agreements, Products, and Tickets tables.

pkstormy's advice is sound, needlessly over-normalizing will make form creation more difficult.

The question would be do you actually need those 2 junction type tables?

If you have 2 'identity' type items in 2 different tables and you want to relate the 2 together in multiple different combinations (ie. 1 or more farms associates to more than 1 record in the other table and you can have many different combinations), you can use the junction type tables as your last diagram shows. This allows you more flexibility whereby you can again, join 1 farm to multiple other items in the other table (or vice-versa if looking at it from the other table's perspective). If though, you only need to join 1 item from the other table to 1 specific farm (or vice-versa) (and won't need to join that specific farm to another item in the other table....or join the other item in the other table to multiple farms), I'd then go the route of just adding the ID from the other table to your farm table or just add the farmID to your other table.

When you look at the 2 relational tables, you need to decide if you need to make multiple different combinations between the 2 id records from the 2 tables. If so, then use the junction type table. If only 1 record in 1 table assoicates to a record in the other table or vice-versa (ie. 1 to 1 or 1 to many versus many to many), then look at adding your ID field to either one of the tables.

For example:
Ex:
Many different Farms and many different 'contacts' for any specific farm and 1 farm can have many contacts or a specific contact can be assoicated to many different farms (along with other contacts to the same farm) - I'd use a junction type table.
Ex:
1 Farm only ever has 1 contact or 1 contact is tied to only 1 specific farm (or tied to many farms but no other contacts to those farms) - I'd just put the ContactID in the Farm table or FarmID in the Contacts table.


I hope that helps.

thanks for everyone's help... still trying to make sense.

The use of the table “contracts” is for a unique name and number given to the seller by the purchaser.

It is possible for 1 farm to have several seller contract and names. Thus a sub name could have could have several contracts and names. Products types are normally associated with contracts.

Agreements are agreements between us and the leasers, and for entities “farms” that hire us for custom work. I wanted to make this many, in case a single farm might want different agreements.

Example: Lets say you have 5 acres that you want to care for yourself and hirer us to pick them, and you also rent us 50 acres. The two agreement types would be different. This is not common.

Tickets – to enter delivery data. 1 farm can have several tickets.

Product - 1 farm can have several products.

1 farm can have 1 to many contracts
1 farm can have 1 to many sub farms
1 farm can have 1 to many harvest details
1 farm can have 1 to many tickets
1 farm can have 1 to many agreements
1 farm can have 1 to many products

So, are the agreements, contracts, harvest details, products, and tickets directly related to the farm or to its subfarm(s)?

If 1 farm has 1 to many subfarms. Can I just remove the junction table? No 2 farms share the same sub farms. Parent farm can have child farms, different parents do not share the same child. Having a hard time with a join types.

The sub farm table has only 1 field in it called SubFarmNames. Names by themselves can be shared by different farms. Not sub names associated with a Farm or Sub Farm details like, harvestinfo, products, agreements, contracts, etc.

yes, to be accurate.. they need to be related to subfarms.

Maybe a..

1 to many relationship between TBL FARMS & SUBFARMS. All other relations flip?

Still thinking about them, and remove the other junction table?

OK.

Yes

I'm thinking it won't matter if farms can share names, you still want to relate the subfarm to different agreements, contracts, harvest details, products, and tickets.

So here's what you'll need:
One Farms table.
One SubFarms table that has the FarmID as a foreign key.
One Agreements table that has the SubFarmID as a foreign key.
One Contracts table that has the SubFarmID as a foreign key.
One HarvestInfo table that has the SubFarmID as a foreign key.
One Products table that has the SubFarmID as a foreign key.
One Tickets table that has the SubFarmID as a foreign key.

No junction tables would be required.

something like this? trying some forms..

That looks to be correct and should make form creation quite a bit easier.

These rules currently define your database:
A Farm can have one or more SubFarms.
A SubFarm can have one or more Agreements.
A SubFarm can have one or more Contracts.
A SubFarm can have one or more HarvestInfo.
A SubFarm can have one or more Products.
A SubFarm can have one or more Tickets.

If that will take care of all the data, then you're all set.

Regards,

Ax

So far looks good, thanks for everyones help..

Yes, making forms seems a lot easier.

Still looking at how data is going into tables, through a form. It has been a long time for sense I have done this. Need to make sure data goes in and can be retrieved back out correct.

If you do decide to use the junction tables, I'd also look at the form deisign on how you want to populate the junction table. This can be simply done but if you've never designed one, it can be a bit tricky. I personally use 3 listbox where a user can select the first listbox (farmID), then the 3rd listbox (ContactID), and click a buttom to move both highlighted values to the middle listbox (ie. junction table.)

You'll have decide though if (for example) if you need many contacts associated to different farms and some farms having multiple contacts.

If so, I'd go the route of using the junction table. If not (and a definiate not), I'd then store the ContactID or FarmID in the appropriate table (A junction table serves no purpose if only 1 contact per farm or vice-versa is going to be entered. It only complicates query/form design.)

Your discription on how to populate tables is interesting. I do not have any experences working with them, at least that I can recall.

Is not the way the relations and tables are set up now 1 farm can have many sub famrms. 1 sub famr can have many contracts?

Is this correct thinking, for forms.

>1 form to enter farm details and that includes entering sub farm names.

----All other forms are dependent on having on sub farm names.

>2nd form that allows sub farm details >Agreements, Contracts, HarvestInfo, Products

>3rd form to Enter Tickets