coolphpdude Posted July 15, 2008 Share Posted July 15, 2008 I've found a potential future problem with my code. at present i have a teachers table and a lesson table. I have a teacher unique id which is their login id. for the lessons i just have it set to integer auto-increment. I was planning on letting the table automatically assign id's for the lesson upon adding a new row. however, ive thought of a potential problem which could be corrected using a prefix to the lesson id of the username. So far example user = bob ID Lesson Teacher bob1 = english = Bob Smith bob2 = maths = Bob Smith bob3 = french = Bob Smith user = Joe ID Lesson Teacher joe1 = english = joe Smith joe2 = maths = joe Smith joe3 = french = joe Smith Rather than... ID Lesson Teacher 1 = english = Bob Smith 2 = maths = Bob Smith 3 = french = Bob Smith 4 = english = joe Smith 5 = maths = joe Smith 6 = french = joe Smith How would i make this happen? As i mentioned above im currently using an auto-incrementing integer field for the ID. Quote Link to comment Share on other sites More sharing options...
trq Posted July 15, 2008 Share Posted July 15, 2008 If your using an auto incrementing field (as you should) then you can't append anything to it. Seems to me you need to look into some database normalization techniques. There's an entire chapter dedicated to it in the Hudzilla link in my sig. Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 Well, i've read that, and i do understand it. I've got my tables broken down in my opinion perfectly. The information i gave above is just a very basic stripped down example. I just need help on creating an id that consists of... username(1) username(2) username(3) . . . . . . username(9865056486) etc... Quote Link to comment Share on other sites More sharing options...
trq Posted July 15, 2008 Share Posted July 15, 2008 You can create that id, but I wouldn't use it as a primary key. From what I can see of your structure (its pretty hard to tell) it doesn't look very normalised at all. Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 It is, the example i gave (probably poorly explained) is just a hypothetical scenario and nothing to do with my actual site or database structure... It was just easier explaining it that way than 30 pages of writing to explain the real scenario... lol Quote Link to comment Share on other sites More sharing options...
samshel Posted July 15, 2008 Share Posted July 15, 2008 what is the problem you are worried about in existing structure ? running out of integers? Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 yeah pretty much. the problem is that i am running a message table. the message can correspond to records on 1 of 5 different tables. each one of the 5 tables runs a primary id with the structure of auto-incrementing integer... however the message needs to display some of the information from the record. Now say for example we have got table A, B, C, D, E. Each table will have 10 rows so each will have existing primary ID's of 1-10. If a teacher receives a message and the message needs to contain information about record 3 from table E then there is nothing to distinguish that. If i run a search query to select all records where ID = 3 FROM tables A B C D E (i know this is not the correct syntax) then there are 5 records with the same primary id... do you get me? Although that primary ID might be unique to that table it won't be unique accross my database. where if i had the ID as A1, A2, A3, etc... or similar then it would be unique within my database. Quote Link to comment Share on other sites More sharing options...
samshel Posted July 15, 2008 Share Posted July 15, 2008 You can go for a combined primary key of auto id and teacher_id, strings should be avoided in primary keys.. Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 tell me more? Sounds like what i'm after... Quote Link to comment Share on other sites More sharing options...
samshel Posted July 15, 2008 Share Posted July 15, 2008 please provide table structures if possible, Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 something like Primary Key DBuniqueID Name ETC ID UID Bob etc and then pass the UID rather than the primary ID? Quote Link to comment Share on other sites More sharing options...
samshel Posted July 15, 2008 Share Posted July 15, 2008 yes exactly Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 Champion, i guess talking through things helps! I just cudnt get my head around it when i was thinking about it!! Cheers for your help guys. Quote Link to comment Share on other sites More sharing options...
samshel Posted July 15, 2008 Share Posted July 15, 2008 pl mark as solved Quote Link to comment Share on other sites More sharing options...
coolphpdude Posted July 15, 2008 Author Share Posted July 15, 2008 how do you mark the topic as solved??? I thought they took that away Quote Link to comment Share on other sites More sharing options...
MadTechie Posted July 15, 2008 Share Posted July 15, 2008 click topic solved bottom left.. if its not their just post solved i guess 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.