Create proper relations

[arishy]

I have the following tables:
1. Client Table
2. Client Farms ( each client has one or more farm)
3. Client Crops (what the client produces, and the seasons they use)

The products we sell have these tables
1. Products
2. Stresses the products cure ( Product can cure more than one stress)
3. The Crops that these stresses occur ( same stress can be in many crops)

As you can see each group is separate BUT the common entity is Crop.

How can one design a good relation with all these tables

P.S There are some internal associate tables
ClientCrop
ProductStress
CropStress

These are used to solve the many to many issues

 

[jdraw]

Please see this tutorial to help get you tables and relations set up to meet your needs.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck.

 

[arishy]

Will do....May be if I post the relations initial take, I can get better guidance.
I will be back !!! Thank you for sharing the link

 

[arishy]

Here is my first take

I posted the database with the relations further down in my post
I also removed the link here due to objections from your admin.

Is the table design ok?
If yes Is the relations OK?

 

[spikepl]

Please post your pix here. Pix in outside locations tend to disappear after a while, and then no-one else will be able to glean any benefits from this thread.

 

[arishy]

Thank you for your remark, can you please direct me on how to it.

 

[GinaWhipp]

Please post the database, here's how...
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[arishy]

Here is the database as an attachmentView attachment BEDBschema1.zip

 

[GinaWhipp]

The table setup looks good insomuch that I can understand. This part...

2. Stresses the products cure ( Product can cure more than one stress)
3. The Crops that these stresses occur ( same stress can be in many crops)

...makes no sense to me. Perhaps if you stated what the Product is?

 

[arishy]

There is one to many between Product and Stresses
Crop can have many Stresses and Stress can be in many Crops. Many to Many.

In simple terms, A product can cure more than one stress
A Crop can have more than one stress.
A product can cure more than one stress... Am I chasing my tail !!!!

 

[arishy]

Gina, the product is Bio Fertilizer used in Farms to cure Fruits vegetables etc

 

[GinaWhipp]

You are not chasing your tail. I am just trying to understand the Data Model so I can make sure I am advising you correctly.

What is Product *stress* and Crop *Stess*? Only thing I can image for crop stress is flooding but I can't think of anything for product stress.

 

[arishy]

Gina, you are in my Client list .... just kidding
I have up to day 17 ....yes 17 (google the names Below)
tblStress StressName Soil-born Diseases Nematodes Early Blight Late Blight Downy Mildew Powdery Mildew Aphids Jassids Leaf Hopper Leaf Miner Mealy Bugs Mites Spider Mites Thrips Tuta Absoluta White Fly Cotton Leaf Worm

Is is an eye opener .....

 

[GinaWhipp]

Heh, I have houseplants... never called those Stresses, I got it now!

 

[GinaWhipp]

Okay, so after further review I only have one question...

Who/what does Product belond to the the Crop, the Client or both?

 

[arishy]

A very good question ? I will try to answer
If we address the first part ( Product, Stress, Crop ) Let us exclude the client part
If we get the relation right, then I can query a crop (in theory) to know all possible stresses AND the Product to cure these stresses. The permutations are endless.

If we look to stress, then I can query all the products for specific crop.

Now we add the client part, the client will specify the farms he/she owns and what crops he/she is producing.

From marketing point of view, I can look at a client and see what products I can offer based on the crops.... and queries/reports will mushrooms...!!!
If I can give some numbers to the whole database, by putting the product quantity needed per acre I can size the whole market potential.

AS you can see the database addresses two areas, one I call internal which deals with the three components (products, crops, and stresses) .

And an external which deals with potential clients and their crops.

The link between these two subsystems is the CROP. It is the common factor between the two subsystems. Do you agree with my logic ???

 

[GinaWhipp]

I understand what you are saying but...

In my mund Product should be attached to Crop because Crops produce specific Products AND Clients because not all Clients who produce those Crops will produce all Products.

 

[arishy]

Products is what I SELL. Crops are what they produce ( Apple, Banana).
These crops suffer from stresses and I come with my products to help them out.
So the triggering of the process is a farmer producing a CROP, got infected by stress.
My products address these stresses, but the trigger is the CROP.

 

[GinaWhipp]

Oops. I was thinking backwards... then you Data Model is fine!

 

[arishy]

Thank you for taking the time to look at my initial work