I’ve finally got authentication (password access) working on my laptop for the birding site. I think I finally have enough pieces to start putting the site together, at least in terms of the coding. Content is another issue.
I have a database of nearly 10,000 bird species. Naturally that’s too large to search through if you’re just entering your backyard birds in New Jersey. I need a way, database designwise, to incorporate species lists by area…state, park, country, region, etc. THere are many ways to slice this task, but what’s the fastest? What’s the smallest? What’s the most intuitive? DO I create a separate database table for each area that I want to have a list of potential species for? That seems wasteful. A table listing the areas is reasonable, along with an ID, as a lookup table. Do I insert the species range right with the species table? That also seems awkward. The best solution it seems would be one single table that would cross pollinate (excuse the pun) and become a many to many table. One field for species ID and one field for location ID. THis table will become HUGE, however, and then slow. FOr example, say I’ve got 2000 bird species for the US, and each bird is found in 1/5 of the US states, that makes 10 states per bird, or a table with 20,000 entries. Yikes, no way! And that doesn’t include areas like national parks, sanctuaries or wildlife reserves. Mabye the best solution is to have a separate table for each area that I’d like to have a list for. Any thoughts? Did anyone follow that?
I’m still not over the loss of my last amazing medical post, so forgive the technical spewing of thoughts above.