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					Any animal
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?

[jzwp11]

Normalized tables are fundamental to good database design. Without proper normalization, you will create a big mess.

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.

However, your interpretation of normalization as described above is incorrect. The kindom table would hold 6 records (animal, plant, fungi, protozoa, bacteria, chromista). Your phylum table would hold all phyla as records. You would have a field that relates the particular phylum back to the particular kingdom to which it belongs.

tblKindoms
-pkKindomID primary key, autonumbe
-txtKindomName

tblPhyla
-pkPhylaID primary key, autonumber
-txtPhylumName
-fkKingdomID foreign key to tblKingdoms

tblClass
-pkClassID primary key, autonumber
-txtClassName
-fkPhylumID foreign key to tblPhyla

The tables will continue to cascade through the remaining taxa down to species. The species table would look like this

tblSpecies
-pkSpeciesID primary key, autonumber
-txtSpeciesName
-fkGenusID foreign key to tblGenus


Now if a user owns many species (one-to-many relationship), you would capture it as follows:

tblUsers
-pkUserID primary key, autonumber
-txtFName
-txtLName

tblUserSpecies
-pkUserSpeciesID primary key, autonumber
-fkUserID foreign key to tblUsers
-fkSpeciesID foreign key to tblSpecies

[kopbad]

That is an amazing reply, with this could a user search for all the species that fall within a given phyla? This is more of a question of how the query is setup, correct? Also, would a master table help in terms of speed and resources?

[jzwp11]

That is an amazing reply, with this could a user search for all the species that fall within a given phyla? This is more of a question of how the query is setup, correct?

Exactly

Also, would a master table help in terms of speed and resources?

Just the opposite, a normalized table structure with proper indicies would be more efficient. Having a "master" table implies that you repeat information that is already housed in other tables--that violates normalization rules & is inefficient because of the redundancy.

[kopbad]

So I think the last thing I am concerned about is values that repeat themself. What If two classes with different phyla have the same name? In terms of a universal id for each entity would some thing like this be acceptable:

Kingdom	Phylum	Class	Order	Family	Universal ID
Animal	Chordate	Mammal	Bestia	Sus	12345
10000	02000	00300	00040	00005

Or is having a universal id completely unnecessary?

Thank you for your help!

[jzwp11]

Would two classes ever have the same name? I would think that it would not be allowed--but I am not an expert on the subject of the taxa. If it is possible, then we would have to look at a different structure.

[kopbad]

Unfortunately yes, it is possible. Would an easy fix be to use a universal id for each entity?

[jzwp11]

Since in the class table you would have the foreign key to the phylum and a unique autonumber for that particular class name, I do not think adding a universal ID is necessary. Do duplicate names occur in the other taxa: i.e. order, family, genus & species? If duplicate names is common then we should probably alter the structure; if it is somewhat rare, then I think you can go with the structure I proposed without any issues--you would just have to do some filtering which you would probably have to do anyway.

[orange]

Just to say I agree with jzwp11. If each record in each table has a unique identifier, then a repeat of a Name should not matter.
If you trace the hierarchy, you will be at the "correct Named "(Class in your example).

As regards taxonomy, there are references to zoological and botanical hierarchies here
http://en.wikipedia.org/wiki/Taxonom...oological_taxa

[kopbad]

I have been going through it and it seems to work just fine. Thank you for your input