On Fri, 3 Sep 2004 15:47:26 -0400, wrote:
>A. Any record in tbl_development can be associated with up to 8 URLs that >represent virtual tours of the dwelling types in that particular >development. >B. tbl_urldetails is a table acting as a "master list" of the virtual >tours you have ready to use. The records in tbl_development should >reference these URLs. > >This is what we call a many-to-many relationship and is commonly modeled >in a relational database with a 3-table structure. It is a many-to-many >because: > 1) Each development can associate itself to several (up to 8, you >say) URLs > 2) Each URL can be associated with any of several (or none) >developments. > >You already have two of the tables you need, what you are missing is the >"association" or "relationship" table. It is usually just a two column >table but more columns can be added if you need to store information about >the "combination" of a development to a virtual tour URL. I will make up a >column that fits that last category and explain why it fits on this third >table. Here is what your association table could look like: > >CREATE TABLE development_URL ( > tbl_development_intDevID mediumint(9) not null, > tbl_urldetails_intID int(11) not null, > dateAvailable datetime null, > PRIMARY KEY(tbl_development_intDevID, tbl_urldetails_intID) >) > >INSERT development_URL VALUES(50,7,'2004-11-01'); > >Let's say that property 50 also needs to show the URLs for tours 1,2, and >4 right now. > >INSERT development_URL VALUES (50,1,null), (50,2,null), (50,4,null) > Shawn, I wrote this query using your relation table. select t1.tbl_development_intdevid,r1.* from development_url as t1 inner join tbl_urldetails as r1 on r1.intid = t1.tbl_urldetails_intid where tbl_development_intdevid='50'; Thanks very much for your help, I still struggle to think "database", but my understanding will now (hopefully) leapfrog. It's always a struggle when you are at such a low level of your learning curve that you cannot easily formulate your pleas for help!! zzapper (vim, cygwin, wiki & zsh) -- vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?" http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
