Rodrigo Madera wrote:
Imagine a table named Person with "first_name" and "age". Now let's make it fancy and put a "mother" and "father" field that is a reference to the own table (Person). And to get even fuzzier, let's drop in some siblings: CREATE TABLE person( id bigint PRIMARY KEY, first_name TEXT, age INT, mother bigint REFERENCES person, father biging REFERENCES person, siblings array of bigints (don't remember the syntax, but you get the point) ); Well, this is ok, but imagine a search for "brothers of person id 34". We would have to search inside the record's 'siblings' array. Is this a bad design? is this going to be slow? What would be a better design to have these kind of relationships? (where you need several references to rows inside the table we are).
Create a table "sibling" with parent_id, sibling_id and appropriate FKs, allowing the model to reflect the relation. At the same time, you can drop "mother" and "father", because this relation is covered too.
Regards, Andreas
