Pointers to tables in SQLite

Question

Say I have a table of apples (apple_id). And for each apple I have a history of its weight over time. How to structure it so that every time an apple is added, an associated table is created with its weight history?

Having only one table of weight history for all apples (apple_id,age,weight) seems like a performance drain when looking to view an apple's weight history, since it has to look through the weight history of all other apples. So it seems I need to create a new table for every new apple (and delete it every time an apple is deleted). How to do this?

My idea is to use take the apple_id and convert it to string. That way the table's name is unique and easy to generate.

Answer 1

What you just described sounds like a maintenance nightmare. What you have here is a one-to-many relationship between apple and its weights over time. You can do:

apples table: apple_id, other apple fields
apple_weights table: aw_id, apple_id, weight, age

Then to pull all weights for a given apple, just

SELECT weight, age 
FROM apple_weights 
WHERE apple_id=X 
ORDER BY age ASC

If it's properly indexed, performance won't be a problem. Relational databases are very efficient when doing stuff like this because they were designed for it.