04-27-12, 14:00 #1Registered User
- Join Date
- Apr 2012
Unanswered: Singly linked, dynamic array, or both?
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:
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?
04-29-12, 05:30 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
There are two things that I believe are worth considering:
1. Going beyond the thid normal form does not necessarily improves a database in terms of security and performances. It depends of the kind of data you handle and must be considered on a one by one basis.
2. Whatever the level of normalization of a database can be, you can always use queries to de-normalize it when needed. This is usually the case when you want to perform complex searches using human-readable terms (i.e. a user will search for a name or a range -or a set- of names, while the database is organized in a relational model using relationships and primary key/foreign key shemas).Have a nice day!
04-30-12, 04:45 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
personally as this is a tree structure you can deploy it either as 6 tables or one table with a self referencing 'parent Item'
as its a long established biological structure the 6 table approach would probably be best as its extremely unlikely that you will need to add (or for that matter remove) another order. the flat model with a self referencing PK would make more sense if there was a risk that another level could be introduced or removedI'd rather be riding on the Tiger 800 or the Norton