Question Singly linked, dynamic array, or both?

[kopbad]

Hello everybody, I have done a lot of thinking and have come up with a few solutions to my problem. As for determining which solution is best, I'm hoping you might help me out. I Pardon the lengthy setup and let me know if this is confusing.
Using this as an example, say my array of information looks like this:
Organism Array
Kingdom Phylum Class Order Family Description
Animal Chordate Mammal Bestia Sus Pig
Animal Chordate Mammal Bestia Dasypus Armadillo
Animal Chordate Mammal Ferae Canis Dog
Animal Chordate Mammal Ferae Felis Cat
Animal Chordate Fish Abdomanales Salmo Salmon

Yes, I know this is not correct, it is just an example. Also keep in mind Animal could Plant, Fungi or whatever.
Now to make things interesting, any user may own one or more of these species at their location. The end goal is to allow users find other users that own a certain species or a type of species in a given area.

Following normalization rules I think this array would be split into many different tables. For example, the kingdom table lists kingdoms and links to animal, plant, fungi, .... tables. The animal table links to chordate, .... tables. Plant table links to monocot, .... tables. This continues to breakdown until the last set of order tables. Ultimately this means there are a lot of tables and each search moves through several nodes.

An example search could be: User 123 is searching for other users within a given radius who own some type of fish AND some type of ferae. First step is to find all users within the radius. Second, out of that list eliminate users who do not own a fish. Third, eliminate users who do not own a ferae. Lastly, Display that list to User 123. Is this correct?

The ultimate question is this, am I better off having a normalized trove of tables, one table for each category along the line.(Each table would have a description column) One large table which would repeat animal, plant, and others many times. Or would it benefit me to have both? In the large table each listing would have its own id which could be indexed, and the many smaller tables would be used to generate the correct id. Whenever an update was needed it would occur on the corresponding small tables and the large table would be regenerated from the updated small ones. User searches would use the smaller tables to generate an id or range of ids to display which it then grabs from the large table. User accounts who own a species would link to the large table in a many to many relationship I believe.

Am I correct in my thinking? What is everybody else's thoughts on this conundrum?

 

[spikepl]

You have a hierarchy. For that the storage mechanism is commonly Adjacency list http://www.sqlsummit.com/AdjacencyList.htm

Basically, for each item you just record which higher level item it belongs to, and that's it. It's like a company - which employee, and which employee is his boss.

Other mechanisms exist (preorder tree traversal) , depening on what you want to do with the data.

 

[Pat Hartman]

You are better off with a set of normalized tables. The search you describe is how you would do it if you were a human sifting through paper. The query engine is much better at finding things. The lowest level ID is stored in the user record. To do the search, you need to join to the tables back up the tree and then provide criteria for one of the levels. I'll give a partial sample of what the where clause would look like:

Where (Kingdom = Forms!yourform!Kingdom OR Forms!yourform!Kingdom Is Null) OR (Phylum = Forms!yourform!Phylum OR Forms!yourforms!Phylum Is Null) ...

Take note of the parentheses. They are critical since this expression uses both AND and OR operators. By checking the form field for null, you allow it to be optional.
My biology class was too many years ago to even mention but I seem to remember that some values in some levels are duplicated. An example (not true of course) would be that Dog could exist in both Canis and Felis Families. That's why you would include all the levels.

 

[kopbad]

That is terrific, thank you!

 

[kopbad]

Doh! I just thought of something I meant to ask should I have a universal id to help with grabbing info? For example, Kingdom Animal is worth 10000, Phyla Chordate is worth 02000, and so on then adding them. Does it simplify searching at all by looking for 12345 rather than Animal in Kingdom then Chordate in Phylum and so on.

 

[Pat Hartman]

No. You're over thinking this. The database engine will optimize the search provided you have created primary keys and also indexes where needed.