jaymc Posted September 19, 2007 Share Posted September 19, 2007 I have 2 tables, one is `MEMBERS` which contains member info such as age, name, gender Then I have another table called `FRIENDS` which contains members friends, however, it also contains a gender field The reason for this is to avoid a double query or a join to pull out the gender from a query that pulls out friends For example, if I had a script that displayed all members friends and there gender, it would be one query to my friends table to get everything I need However, if there was no gender field in the friends, I would need to add a join to pull it out from my members table Normalisation and joins are supposed to be an optimized way of handling database, Im just wondering if my approach would work better Feedback would be great Quote Link to comment Share on other sites More sharing options...
Illusion Posted September 19, 2007 Share Posted September 19, 2007 I really wonder if GENDER column helps you to normalize the two tables.Ofcourse it depends on what you are trying to retrive. Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 19, 2007 Author Share Posted September 19, 2007 That was just an example, I have 20 tables with similar setups like that In some scripts I will need table info and in addition there email address for instance, which is also available from the members table So, rather than using a join or 2 queries I just put the email in with the table. The alternitive is to just have it left in members, and use a join in the query to pull it out. I suspect that will work against me though in regards to optimizing mysql queries Quote Link to comment Share on other sites More sharing options...
Illusion Posted September 19, 2007 Share Posted September 19, 2007 rather than adding the column , I would suggest go for JOIN. because EMAIL is unique for each member it doesn't make any sense having that coulmn in 2 tables as long that is not a PRIMAY KEY for one table and FOREIGN KEY for another table.Although its just an example it depends on the data you put in the coulmns determines wheather you can add column and go for a join. Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 19, 2007 Author Share Posted September 19, 2007 But using a join is essential 2 query tables where as even if I had email repliacted accross a few tables, I can get away with using 1 query to get what I need Why do you advise to use join when focusing on optimization? My reason for join is mainly to reduce the size of the database, the less replication the small the tables which I suppose is optimization ni its self. Its just a case of how well it fairs up Larger databases = less queries Smaller databases = more queries Who wins Quote Link to comment Share on other sites More sharing options...
Illusion Posted September 19, 2007 Share Posted September 19, 2007 as per my knowledge , it is always smaller databases which helps you to optimize. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 19, 2007 Share Posted September 19, 2007 Where you put the column usually has little to do with optimization, but rather normalization. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 19, 2007 Share Posted September 19, 2007 If C's gender is male when a friend of A, but female when a friend of B then, yes, you need the gender column in the friend table. Quote Link to comment Share on other sites More sharing options...
jaymc Posted September 19, 2007 Author Share Posted September 19, 2007 Cheers 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.