Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    408
    Thanks
    17
    Thanked 2 Times in 2 Posts

    normalization confus.. ization.

    I'm struggling to decide which way to go. or if I could overnormalize my database.

    I'm working with plants. I started to build tables on the topic, as I was learning (both about plants and about databases). For example, I started with one field for color... then, when I realized there could be more than one color per plant, I separated them out into a column of boolean T/F for each individual color. That quickly got unwieldy, as I discovered more terms. For example, there is a standard terms for two colors at once, "variegated"... if this kept up, I was going to make wayyyy too many columns. And that's just colors. I've also realized there are multiple leaf shapes on a single plant, as well as several other multiplistic parts.

    I'm thinking i need to build a new table for each of these characteristics. But my head is swimming. I'm assuming it's a many-to-many scenario? or is it many-to-one. And how would I structure the table? or is it overdoing it, when there are easily 5 different descriptors that could be pulled into a secondary table to join.
    Last edited by turpentyne; 10-25-2011 at 06:05 PM.

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Having a column for each of those attributes like you realized is not the correct approach. What you would do is have a lookup table of colors. Then another table that links your plant table with the color table. That table would then just need two columns, the plant ID and the color ID. You can use the same methodology for your leaf shape table as well.

    Here is an example of that type of structure:
    Code:
    Plant Table:
    PlantID, Plant Name, etc.
    
    Color Table:
    ColorID, Color Name
    
    Plant_Colors:
    PlantID, ColorID
    I would use a composite primary key on the Plant_Colors table.
    OracleGuy


  •  

    Posting Permissions

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