aztec Posted March 20, 2008 Share Posted March 20, 2008 Hello I Have a MySQL database with 12 tables, named gen_5, gen_6 etc. each table is identical regarding the number and name of the fields, and hold the names of family members from different generations. The tables are populated with test data while I prove the structure and the queries. There are 17 fields in each table but the first 2 fields I believe are the important ones, they are:- id unique, auto number fid unique, this is a family member number. There is also a field named link_to_fid which at the moment I am not using. I need to get for example the following data: From gen_10 four family members ie grandparents From gen_9 two family members ie parents From gen_8 up to fourteen family members ie children (years ago large families were the norm) At the moment using PHP I can get all of the data I need using multiple queries, but it does not seem flexible enough to me. I know it would be a complex query to get all the data I need using only one visit to the database but my questions are would I see a significant gain in time from using 3 queries against one query. Would the link_to_fid field create more flexibility. Kind Regards Quote Link to comment Share on other sites More sharing options...
fenway Posted March 20, 2008 Share Posted March 20, 2008 How are these tables related? Sounds like you need to read up the adjacency model. Quote Link to comment Share on other sites More sharing options...
aztec Posted March 20, 2008 Author Share Posted March 20, 2008 Hello Fenway They are related using the fid field and the now the link_to_fid field. I have not heard of the adjacency model could you please elaborate. Regards Quote Link to comment Share on other sites More sharing options...
aztec Posted March 20, 2008 Author Share Posted March 20, 2008 Hello Fenway At this stage of development the fields are fluid and any relationships can be implemented. Regards Quote Link to comment Share on other sites More sharing options...
aztec Posted March 20, 2008 Author Share Posted March 20, 2008 Hello Fenway I have spent the last hour reading upon the adjacency model and I do not think that it would be of use in that it appears to be hierarchical data which has a parent-child relationship that is not naturally represented in a relational database table. From what I have read so far it appears that working with this model is at best difficult and gets progressively more difficult has more levels are added. If you have any other thoughts I would appreciate your input. Regards Quote Link to comment Share on other sites More sharing options...
veridicus Posted March 21, 2008 Share Posted March 21, 2008 From my experience I would use separate simple queries. If your data grows large, or you need to add more depth, you might see a big performance hit. Mysql is very efficient at running many small queries. From what I can gather coding your logic into PHP will be pretty straightforward. Quote Link to comment Share on other sites More sharing options...
aztec Posted March 21, 2008 Author Share Posted March 21, 2008 Hello Thank you for your input veridicus, at this time the norm would be 3 queries, 1 to each of 3 different tables, the largest data return would normally be from the children table, but it would only be one query. Regards Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.