Solved

Database Design

Posted on 2012-03-10
16
438 Views
Last Modified: 2012-06-27
Hi there,

I am trying to design a database that should hold the following:

Carnivores animal Orders that are divided into Families that are divided into Genera, that are divided into Species. I also need to include characteristics like scientific name, habitat, foods, etc.

How do I decide on which table should hold the characteristics (scientific name, habitat, foods...)?
0
Comment
Question by:mitdaniels
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37705472
It depends on which entity these characteristics are to be saved against.

What i mean is if these characteristics are to be saved at the Species level the it will be in that table. Or if there is a further classification under species it will get into that table.
0
 

Author Comment

by:mitdaniels
ID: 37705483
Are you saying that the Orders table should not get these characteristics? Why not?
0
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 250 total points
ID: 37705490
By you explanation what i understand is that it is like a hierarchy(tree structure) so the first 3 tables (Order, Families,Genera,) are meant for  classification the final table Species(Treated as a the leaf) is where the animals will go so hence i said it should go into species or any other table which is below it.

Correct me if my understanding is wrong
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mitdaniels
ID: 37705510
I think you understand it correctly, though this is the only information I have available. I must be able to do the following:
Return all Genera which belong to any given Family and which for any given Species, can return its Order, Family and Genera.
Return all of the information about a given animal

Would adding the characteristics to the Species table be the best option, given what we know?
0
 
LVL 78

Assisted Solution

by:arnold
arnold earned 125 total points
ID: 37705758
You may want to separate the habitat, foods into their own tables as well. This way you can locate species based on habitat, or common foods.
Or is the food reference is the chain which species consume which other species.
Separating habitat and food would provide the flexibility of having a species reference multiple foods and habitat for species spanning multiple environments/locals.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 125 total points
ID: 37705971
If you set up a table to hold ech of the datasets (i.e. the Orders, Families, Generas, the Species, Habitats, common foods, and whatever alse you need), then you can treat those as "LookUp" tables and start doing things like creating a cross-reference table wthat has the provides the Families that can be associated with Orders and Genera that can be associated with Families. Now, you can set up your Species table so that it has a column for the Order_ID, the Family_ID, and the Genera_ID, as well as the various names that may be associated with it (e.g. Common_Name and Scientific_Name).  By having the cross-reference tables, you can create an application that will allow you to populate the Species table and the app can allow only those Families that are appropriate for the Order that the user selects and only the Genera that are appropriate for the Fimaly that was selected.

Once that is done, you can set up a Common_Habitats and a Common_Foods table to that have columns for the Species_ID, Habitat_ID, and Food_ID.  that will let you start at almost any piece of inforamtion and find out what else is associated with it.  For example if a Food is "Apples" then you could find out which Species have "Apples" as a common food and, then, from there you couldfind which habitats have species that have "Apples" as a common food.
0
 

Author Comment

by:mitdaniels
ID: 37706217
I like the path and explanation SANDY_SK has given, but need further input as to how he thinks the Characteristics could be developed further inside the Species table. I have my own ideas around this, but would like to hear how he would go about developing it further and also why it should be done this way.

Thanks for the input everyone.
0
 
LVL 11

Assisted Solution

by:SANDY_SK
SANDY_SK earned 250 total points
ID: 37706722
Since every Species will have parent in Genera which in turn will always have a parent in Families and in turn always have a parent in Order, that way at any point of time you can always get all the required details for a given animal. so i would like to store the characteristics at the species level.

Another way  to look at the whole picture will be that if you want to store the characteristics as a separate table,  some common characteristic at each of the parent will be in the table so the final characteristics of the animal is the combine characteristics of all its parents.
0
 
LVL 78

Expert Comment

by:arnold
ID: 37707168
I think Sandy_sk deals with the minimum number of tables required.
I am unfamiliar with the underlying data to see whether there is cross use of foods, habitat, etc. such that those should be in their own tables where you would have habitat and the specifics for the habitat included there. Similar with the food category. This will enhance your cross referencing i.e. presumably you will have each species identified with the time frame when they existed, such that you could then cross reference the habitat, food that and the time frame where a set of species coexisted. etc.
This way you would also maximize the amount of information while minimizing the database space used.
0
 

Author Comment

by:mitdaniels
ID: 37707275
The requirements are really very basic. I am looking for the most efficient way of designing a simple database that seems to be hierarchical in nature, as SANDY_SK pointed out. The part that is still unclear is the further development of the Species table.

If I were answering this question and had a way to develop the Species table further, I would say something like:
The Species table would hold a foreign key to the Genera table, the Genera table would hold a foreign key to the Families table, and so forth.
The Species table would also hold the following column attributes...
This way of doing the Species table is ideal because...

Any additional information for the Species table can be gleaned from any animal characteristics like habitat, food, location, etc. The question is how would you suggest putting this plus any additional tables together to provide a good third normal format?

Since the Species table is the main issue at this point, there needs to be a clear explanation and understanding of how to develop this table or any additional supporting tables further.

Thanks!
0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37707442
one more idea for the characteristics can be as follows.

If you are not sure the various characteristics that are present, what you could do is have a master table for characteristics that will hold values such as habitat,food,location, etc

And there will be another details table where for each species the values for applicable characteristics will be stored. this way if any new characteristics  after you have developed, all you will have to do is add it into the master table and insert the value in the details table for that species.
0
 

Author Comment

by:mitdaniels
ID: 37707447
Okay, so to help you help me, I've designed a Species table:

Species:
It has the GeneraID as a foreign key
A few necessary column attributes

Seperate characteristics tables:
Habitat table
Foods table
etc...

How do I setup the relationship between the characteristics tables and the Species table?
0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37707456
No what i was trying to tell is

Species:
1. It has the GeneraID as a foreign key
2. A few necessary column attributes

Characteristics Master Table:
1. Has characteristic type and description

Species Characteristic Details Table
1. Has SpeciesId
2. Has CharacteristicTypeId
3. Value for the characteristic
0
 

Author Comment

by:mitdaniels
ID: 37707512
Yes, I had originally thought of having a characteristics table, but thought it might need too many additional tables to maintain 3NF, or would this work? I think I would still need to have separate tables for most of the characteristics, and perhaps bring them together in the Characteristics table??
0
 

Author Comment

by:mitdaniels
ID: 37707595
The Characteristics tables would certainly bring things together, but I definitely see a problem trying to add new values to an individual characteristic, unless they each have their own table, and then brought together into a single Characteristics table, or am I missing something?

If that is the case, then could one simply just as well bring them together in the Species table?
0
 

Author Closing Comment

by:mitdaniels
ID: 37732853
Thanks for the valuable contribution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question