Hello,

I want a kind of balcony with MySQL to create community in which users add their plants on the balcony. Then they get possible and actual pests displayed.
Now I do not know more.

How do I make the relationship between plants and pests.
On the one side i want the biologically possible, that pests which can infect a plant.
On the other i want the pest which is sitting on the user plant.
How do i make these difference?

ER-Model: http://img198.imageshack.us/img198/2397/balcony.gif

Do you think the rest of the ER-Model is correct?

Thanks in advance
PrinzLangweilig

in the sense that it will get you started to build your application, yes it is

in the sense that it can be handed in as an assignment, no it ain't

the plant-pests relationship is many-to-many, and will be implemented by creating a relationship or association table, which will consist of two columns, which together form the primary key, and each of which will be a foreign key to its respective table

plants
24 tulip
25 begonia
26 rowdoughdendron

pests
105 aphids
106 ants
107 kittens

plant_pests
24 105
24 106
26 107

I have only had a very brief look at your "ER" diagram so this may not be all:

Roles
1:1 relationship between Role and User is unlikely - could role fields be added inside User table? Would it be better to have actual roles rather than read/write/delete ie admin, guest etc. Could a user ever have more than one role?

Users
Date joined might be useful. Presume password will be encrypted and not plain text. Do you need a UserLog table?

Balcony
Why have this table at all - I can't see the purpose?

Species
You are storing two types in here but only one id - I assume you wouldn't have a plant with id 1 and a pest with id 1. You should have 2 tables here. Also these tables could have a parent child relationship so you can see plants of the same species etc.

Plants & Pests
Only allows one picture. Has many to many relationship with Pests and Pestable - need join tables here. Pestable is not a word! What is the point of the count field?

Mike

PS I presume you're going to display this on the web using PHP - if so then I'm in the middle of building a tool to display simple databases on the web and I'm looking for guinea pigs It would take 10 mins to set up. Here's a simple example involving pictures and data.

PS2 Sorry Rudy - you must of replied while I was typing.

Hi,thanks for your answers r937 and mike_bike_kite!

@r937
Yes thats right. That is the first Relation I mean. The user-specific (which user has which plants)
The secound i want to implement with "pestable" is a table which checks that the pest is biological correct assigned to the plant. I don't want e.g. ladybugs assigned on roses because ladybugs help them and eat lice. But i want the user can assign e.g. lices to their roses.
Hmm how do i make this?

@mike_bike_kite
Good idea with "roles" and "users", i will grab them.
Yeah right the balcony table is nonsense .
The way with 2 tables for the species would go,
but is there not a way to do this in 1 table?
Thanx, I'll try the tool if the ERM is ok

Why not just have a pest flag in the insect table. If the insect is a pest for some plants but not for others then you'd need to put the flag in the join table.

Yes but why complicate matters?

Your ERM is not an normal ER diagram and I'd say it makes things look more complicated rather than simpler. I'd suggest looking up ER diagrams.

Few other thoughts:

• You could add an environment table so you could say that a plant likes the shade, hot temp, alkaline soil. This may or may not make the eventual page more useful.
  
• Do you need a user plants table or could a user just go to the page, search for a plant and see what the pests were?
  
• If using images then best to store url of image in the database and not the image itself.

Mike