Results 1 to 5 of 5
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: Problem with DB Design

    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
    Last edited by PrinzLangweilig; 05-31-09 at 07:04.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PrinzLangweilig
    Do you think the rest of the ER-Model is correct?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.
    Last edited by mike_bike_kite; 05-31-09 at 10:55.

  4. #4
    Join Date
    May 2009
    Posts
    2
    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
    Last edited by PrinzLangweilig; 05-31-09 at 12:58.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by PrinzLangweilig
    Hmm how do i make this?
    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.

    Quote Originally Posted by PrinzLangweilig
    The way with 2 tables for the species would go,
    but is there not a way to do this in 1 table?
    Yes but why complicate matters?

    Quote Originally Posted by PrinzLangweilig
    Thanx, I'll try the tool if the ERM is ok
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •