Your (first) guess is exactly correct. Create a separate table with two columns...one being the employee (primary) key and the other being the client (primary) key. Then, whenever an employee works with a client for the first time, you can insert a row into this table. I say "for the first time" because storing multiple records of the exact same data will serve no useful purpose. If you want to track WHEN each employee works with a client then you need to add a third column to store the date or datetime that the interaction took place. With this third column, it then does make sense to store the same employee/client id combination as the datetime will be different.
Best regards, Jim at iDimensionz.com -- http://www.iDimensionz.com Professional web site design and affordable web site hosting. ------------------------------------------------------------------------ Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=38044&t=84> – Get the new open source web browser, FireFox, and enjoy features like pop up blocking, tabbed browsing and MUCH more! Get Thunderbird! <http://www.spreadfirefox.com/?q=affiliates&id=38044&t=178> – Get the new open source e-mail client, ThunderBird, and enjoy features like integrated spam filter, privacy protection, integrated RSS reader and MUCH more! edcasati wrote: > I'm working on my first 'real' PHP / MySQL database, and I'm somwhat > confused about how to 'relate' data. > > Imagine running a temp agency. You have two basic mySQL tables: > Table1 is employees > Table 2 is Clients > > Each client and employee has one entry in their respective database. > I want to setup the database so I can keep track of which employee has > worked with which client, and which client has used each employee. An > employee can work for multiple clients, and a client can use multiple > employees. > > How do I track those relationships? > > My guess is that the only way is to setup a separate table, with one > column keyed to employee and another table keyed to client, and have > them point to the appropriate entities. So in essence there is a third > table that tracks the relationships, but doesn't contain any 'real' > data, just pointers to the appropriate row in the 'real' tables. > > Is that correct? The only other answer that I can think of is to setup > a field in 'clients' that behaves like an array, and putting a list of > each employee in that array. Besides the fact that there seems to be > no such thing as a field[array] datatype in mySQL, it seems to break > even the most basic rule of normalization (each field contains one and > only one piece of data.) > > I know it's a very basic question... appreciate any answers. I have > the 'client' and 'employee' tables working individually quite > nicely... I just don't know what the CORRECT method is to establish > many-to-many relationships. > > (BTW... putting things in words really helps... thinking more clearly > already!) Thanks in advance. ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/CefplB/TM --------------------------------------------------------------------~-> The php_mysql group is dedicated to learn more about the PHP/MySQL web database possibilities through group learning. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php_mysql/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
