dharm Posted June 9, 2007 Share Posted June 9, 2007 Hi, i have designed a database generally without using auto instruments.. Users have an option to create and account from 2 different options "employee" or "employer". each will be given a 6 digit account number which is stored as a CHAR(6). when both members interact, for example exchange messages, the messages table will have an unique id called id_id CHAR(12) made up from both employeeID and employerID (000001000001). Will it be ok to use the LIKE command to extract a message. so if i wanted all messages from a employee i would do this: WHERE id_id LIKE '".$employeeID."%' i have tested the structure so far with a few members interacting but i would like to get reassurance that this way of identifying rows will work on a larger scale? any advice would be much appreciated .. thank you Quote Link to comment https://forums.phpfreaks.com/topic/54829-solved-will-this-database-structure-work/ Share on other sites More sharing options...
bubblegum.anarchy Posted June 9, 2007 Share Posted June 9, 2007 There would be an issue with searching against the employer ID since LIKE '%{$employer_ID}' would return more than expected. Normally, linked tables are structured this way: message.id message.employer_id message.employee_id message.content etc... That is if employee to employee or employer to employer messages are not allowed, otherwise: message.id message.author_id message.recipient.id message.content etc... Quote Link to comment https://forums.phpfreaks.com/topic/54829-solved-will-this-database-structure-work/#findComment-271215 Share on other sites More sharing options...
dharm Posted June 9, 2007 Author Share Posted June 9, 2007 Hi, bubblegum anarc, thanks for your response.. There would be an issue with searching against the employer ID since LIKE '%{$employer_ID}' would return more than expected. so if i had rows like.. 000001000001 000010000010 000100000100 001000001000 010000010000 and i search for $employer_ID = "000100"; employer ID LIKE '%{$employer_ID}' my return would be the one row 000100000100. could you please tell me how it would get more results then expected? Quote Link to comment https://forums.phpfreaks.com/topic/54829-solved-will-this-database-structure-work/#findComment-271593 Share on other sites More sharing options...
bubblegum.anarchy Posted June 10, 2007 Share Posted June 10, 2007 oops - you would not... sorry... I was mixing regular expression and MySQL LIKE. well... the issue would then be in the amount of time taken to create a joins to employee and employer tables. The combined id method requires that the ids be extracted before joins are created where as keeping id seperate does not. Quote Link to comment https://forums.phpfreaks.com/topic/54829-solved-will-this-database-structure-work/#findComment-271641 Share on other sites More sharing options...
dharm Posted June 14, 2007 Author Share Posted June 14, 2007 yes i agree.. Another point i figured was by using the combined ID's will limit the maximum amounts of entries. so my above example would limit me to 999999 entries only whereas separate ID's can be limitless case closed (",) Quote Link to comment https://forums.phpfreaks.com/topic/54829-solved-will-this-database-structure-work/#findComment-275018 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.