4 ms·
A simple solution is to have a single table with the fields: id, parent, name id is your primary key. parent is the id of the parent (if one exists) or a sent
by maxk42 7y ago
A simple solution is to have a single table with the fields: id, parent, name
id is your primary key. parent is the id of the parent (if one exists) or a sentinel value (NULL or 0) if no parent exists, and name is "Dog", "Cat", "Pet", etc.
If you structure your queries well this will be very performant up to 1 - 2 million records on commodity hardware (e.g. an EC2 t2-large instance) and may be suitable for your needs for much larger data-sets, depending on your application.
For improved lookup performance, you might split the fixed-width and variable-width (varchar) columns into separate tables. Then you'd end up with an "animal_names" table consisting of (id, name) and an "animal_relationships" table consisting of (id, parent_id, name_id) -- but again, that depends on usage patterns and which specific queries you need to be performant.
- tabtab 7y agoThe question is not really about hierarchies, but more about representing diverse-but-similar domain entities. It's a problem found in GUI modeling also, where various GUI widgets will or can have different properties. Some properties will be shared, such as "parent_ID" (AKA "container"), but many are specific to a particular widget or subset of widgets. Further, new widgets may be added down the road, and we don't want to have to invent a new table for each one. Current RDBMS modelling techniques handle these situations poorly. I've been a fan of "dynamic relational" to handle these kinds of problems, but so far it hasn't been implemented: https://news.ycombinator.com/item?id=15352786 https://news.ycombinator.com/item?id=15352786