database schema design question
-
Hi, I have two objects, both point to each other in OO. e.g. Class Father { private int id; //id of father private Son son; } Class Son { private int id; //id of Son private Father father; } so I need to have two tables in database as follow, Table Father : (id, SonId) Table Son : (id, FatherId) The problem is, should I eliminate the one of the reference in one of the table, e.g. Table Father : (id) Table Son : (id, FatherId) so I just have the fatherID in the son table. But if I remove the sonID from the father table, I need to join the two tables when I retrieve fathers from the father table. So what are the pros and cons of the two schema design ? Thanks
-
Hi, I have two objects, both point to each other in OO. e.g. Class Father { private int id; //id of father private Son son; } Class Son { private int id; //id of Son private Father father; } so I need to have two tables in database as follow, Table Father : (id, SonId) Table Son : (id, FatherId) The problem is, should I eliminate the one of the reference in one of the table, e.g. Table Father : (id) Table Son : (id, FatherId) so I just have the fatherID in the son table. But if I remove the sonID from the father table, I need to join the two tables when I retrieve fathers from the father table. So what are the pros and cons of the two schema design ? Thanks
If you keep the references in both the tables, you can improve the efficiency by eliminating joins. You can find all 'children' of a 'father' and vice versa by a simple query on a single table. The down side however is an additional overhead in maintaining the redundant data consistently. You need to make sure to update the respective fields whenever any child is to be deleted or the other way. Just see what is preferable for your application, efficiency or simplicity... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~