Link to home
Create AccountLog in
Avatar of mitdaniels
mitdaniels

asked on

Database Design

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...)?
Avatar of SANDY_SK
SANDY_SK
Flag of India image

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.
Avatar of mitdaniels
mitdaniels

ASKER

Are you saying that the Orders table should not get these characteristics? Why not?
ASKER CERTIFIED SOLUTION
Avatar of SANDY_SK
SANDY_SK
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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!
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.
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?
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
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??
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?
Thanks for the valuable contribution.