> systems > ------------------------ > sys_name ID > ------------------------ > Abrams 202 > Patriot 544 > Stinger 229 > > ... and ... > > new_req > ------------------------ > proj_name ID > ------------------------ > Test Bed Alpha 344 > Tracked Wheels 989 > Battle Monitor 823 > Shoulder Harness 654 > Ammo Flask 454 > Spotter 773 > > There may be multiple new requirements for a single system. To > accommodate them, I created a new table -- an intersecting entity is > what I recall it being called -- to bridge the two. I can't keep the > multiple references to new_req in the systems table, nor can > I keep them > in the new_req table. As best I can figure, I need to have a bridge > table ... e.g.,
You mean a Many-to-Many or N-to-M join. IE, a system can be used in more than one project, and a project can have more than one system. > > ie_sys_req > ------------------------ > sys_id proj_name_id > ------------------------ > 202 344 > 202 989 > 202 823 > 544 654 > 544 454 > 544 773 > > If this seems peculiar, have patience with me I'm new at > this. If it's > right, let me know so I can breath a sigh of relief. And if the That is in fact how to implement an M-to-N relation in a relational database. Sigh away. :) > structure is right, could someone offer some guidance on how a query > that allows me to query new_req using the intersecting entity (or > three-way join, whatever it's called) would be written. > OK, so if you want to see all the systems involved in each project, do something like: select proj_name, sys_name from systems, new_req, ie_sys_req where new_req.ID=ie_sys_req.proj_name_id and ie_sys_req.sys_id=systems.ID If you'r going for speed, you're going to want an index on each column that contains an ID, so two indices in ie_sys_req. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
