Solved

Database Design

Posted on 2012-03-10
16
427 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
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 11

Expert Comment

by:SANDY_SK
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mitdaniels
Comment Utility
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 76

Assisted Solution

by:arnold
arnold earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the valuable contribution.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Let’s list some of the technologies that enable smooth teleworking. 
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
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.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now