Results 1 to 4 of 4

Thread: Table Design

  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Table Design

    I admit to dropping database design in college when I hated the prof....thankfully you all probably know much more than she did.

    My goal is to create a database that allows me to track information about plants. This includes the many varieties in many families, along with trading and purchasing info, and growing info.

    I used only one family - gesneriads - for the following. I'm not sure what will happen when I branch out and include additional families, as they have completely different characteristics. I'll be asking more about that later, I imagine. Having them all in one database seems daunting, so I thought I'd see if I'm even on the right track with this one family first.

    I thought about as many characteristics as I could about the plants and identified them in logical areas. I think all of these would be tables, but am looking for guidance. I haven't identified primary keys, data types, etc. I just want to see if I'm thinking about this logically.

    Thanks for looking and offering any input.

    General Plant Info
    Plant Information
    Plant ID
    Plant Name
    Year Hybridized
    Plant Description
    Plant Personal Rating
    Plant Family
    Plant subfamily
    Plant Genera
    Species or Hybrid

    Plant Status
    Classic
    Vintage
    AVSA Most Wanted

    Plant Registration
    Registration Y/N
    Registration Number
    Registering Body
    Registration Date

    Plant Hybridizer
    Lastname
    Firstname
    Company

    Plant Size
    Mini
    Semimini
    Small standard
    Standard
    Large

    Plant Type
    Regular
    Trailer
    Chimera

    Flower
    Flower lobes
    Single
    Single/semi
    Semidouble
    Semidouble-double
    Double

    Flower shape
    Star
    Pansy
    Wasp
    Bell
    Tubular
    Other

    Flower drop
    Drops
    Sticktite
    Unspecified

    Flower edge
    Geneva
    Thin
    Wide
    Glitter
    None

    Flower color
    Main petal
    Top petal
    Eye
    Edge
    Band
    Tip
    Overlay
    Fantasy
    Shading
    Rays
    Splotches/patches
    Thumbprint

    Flower color characteristics
    Two-tone
    multicolor
    Bi-color
    Shaded
    Sparkle/glitter
    Pinwheel
    Reverse

    Flower petal characteristics
    Ruffled
    Frilled
    Fringed
    Fluted
    Wavy

    Leaves
    Leaf main color
    Light green
    Medium green
    Dark green
    Black green
    Olive green

    Leaf variegation type
    Crown
    Mosaic
    Tommie Lou
    Unspecified
    None

    Leaf variegation colors
    White
    Red
    Pink
    Cream
    Yellow
    Light green

    Leaf back color
    Pink
    Red
    Silver

    Leaf shape
    Heart
    Round
    Ovate
    Holly
    Longifolia
    Girl
    Clackamus
    Bustle-back

    Leaf characteristics
    Serrated
    Scalloped
    Round
    Glossy
    Pointed
    Plain / tailored
    Pebbled
    Hairy
    Wavy
    Bustle
    Quilted
    Supreme
    Cupped
    Ruffled
    Strawberry

    Growing (including purchasing and trading)
    Purchase Info
    Purchase Date
    Shipping Date
    Received Date
    Vendor ID
    Item type ordered
    item type received
    Number received
    Cost per item
    Condition item received
    Purchase Notes

    Trading Info
    Trading Partner ID
    Send or Receive
    Item sent
    Date item sent
    Shipping method
    Item type sent
    Item size
    Number sent
    Item type received
    Item size received
    Date item shipped
    Date item received
    Condition item received
    Number received
    Items owed
    Trading Notes

    Propagation
    Propagation ID
    Date Started
    Propagation Method
    Dated potted
    Visible Plantlet (date)
    Number offspring
    Offspring removed (date)
    Potting Media
    Fertilizer used
    Other (hormone, keiki grow, etc.)
    Propagation Notes

    Growing Details
    Currently Growing Y/N
    Reason Growing
    Reason not growing
    Bloom stalks identified (data)
    Pot size
    Potting Media
    Fully Open Bloom (date)
    Lighting received
    Plant Location
    Growing Notes

    Care
    Date last care
    Care performed (repot, crown, trim leaves, prune, suckers removed, etc)
    Root rating
    Fertilizer used
    Overall plant status

    Disease/Pest/Issues
    Routine treatment date
    Routine treatment method
    Symptoms
    Issue (pest, disease, other)
    Date identified
    Date resolved
    Treatment method
    Treatment date
    Treatment notes

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Taxonomies are a common and perplexing problem in database design.
    There are several methods to handle this. I'd recommend storing consistent data in standard relational database form, and storing type-specific data in an XML field.
    But that is just one solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2011
    Posts
    2

    consistent vs type-specific?

    Thank you for the reply, blindman. I am not familiar with XML fields, having dropped that database class in 1998, lol. I looked them up and seems SQL Server added them in 2005.

    What is the benefit of storing some data as an XML field? And, as a novice, is this something that I can do without too much pulling of hair and gnashing of teeth?

    Help me understand what you mean by consistent and type-specific data. For example, what would you classify as type-specific data here - things like growing data, personal ratings of plants, trading information, etc?

    If I did that, how would it influence how the tables were set up? I broke out the data in ways that seemed logical to me in that they were only about that topic, but do they make sense as tables if you break them out as you've suggested?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    No, much hair-pulling and teeth-gnashing is virtually guaranteed.

    Consistent data fields would be information that applies to all of your data records. For example, all plants have a name. All plants are toxic or non-toxic, annual or perennial, etc.

    You can create fields for storing all these data elements of which you have prior knowledge, and even leave the values null for plants to which they do not apply.

    Some characteristics may be specific to single plants and may not be anticipated in your design. If you encounter one, you either have to modify your schema to add that attribute, or you'll need to set up an XML data column.

    The best approach, if possible, is still to anticipate and plan for all the elements you need to capture. I'd suggest you set up a table for Plants, and a table for Hybridizers, and see how many of the attributes you've listed fit logically in that two-table schema.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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