CODEWITHSUNDEEP
databasedatabase-design
Recursion
Recursion

Reputation: 3091

What is the most effective way to handle lots of tables in a database?

I am new to database programming and am using sqlite and python. As an example lets say I have a database named Animals.db which I open with and get the cursor for in python. Now if I wanted to separate the animals by species I would have a different table per species and since it can get even more specific I would likely need something more specific than just a table of species.

I am a bit confused on how one allocates the correct data to the correct area of a database, how is it separated. Are there tables of tables?

if I wanted to lets say have a table for every land animal and another for every animal of the sea, but each table would need further specification(homo sapiens, etc), how can I do that?

Upvotes: 0

Views: 64

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95771

Now if I wanted to separate the animals by species I would have a different table per species

Maybe. Maybe not. You might use a table that looked like this. It depends entirely on what you mean by "separate the animals by species". Here's one reasonable interpretation.

Animal_name      Sex  Species
------
Jack             M    Leopardus pardalis
Susie            F    Leopardus pardalis
Kimmie           M    Leopardus pardalis
Susie            F    Stenella clymene
Ginger           F    Stenella clymene
Mary Ann         F    Stenella clymene

To find all the Clymene dolphins, you might use a query along these lines.

select Animal_name
from animals
where species = 'Stenella clymene'
order by Animal_name

Animal_name
--
Ginger
Mary Ann
Susie

Start by collecting data. Your goal is to collect a set of representative sample data. Sample data, because the full population is too big to handle. Representative, because ideally it represents all the problems you're likely to run into with the full population. If "animal name" to you doesn't mean "Jack" or "Ginger", but "ocelot" and "Clymene dolphin", representative sample data will make that clear.

Upvotes: 2

Related Questions