List:General Discussion« Previous MessageNext Message »
From:SGreen Date:September 3 2004 7:47pm
Subject:Re: Understanding Joins : Baffled
View as plain text  
Let me see if I can summarize what I know so far:

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)
)

The first column gets an intDevID value that exists in tbl_development. 
The second column gets a value from the intID column of tbl_urldetails. 
The third column can be used by your website to automate the "publishing" 
of a virtual tour to your website. Because that is a date specific to the 
"assignment" of a tour to a development, that information belongs on this 
table. It makes no sense to put that date on either of the other two 
tables, does it?

Lets say that development 50 will have the type of property that 
corresponds to URL 7 ready for touring as of 2004-11-01 but you, being 
rewarded for your months of hard labor, plan to vacation that week. 
Because you have included that date column in the association table you 
can set up the data now but because the date is a future date, your will 
decide to not to show it until that date comes around. Now you look like 
the true GURU because you can schedule a update to your site. Automation 
is our friend.

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)

I chose the null value for the dates as a signal for "always on" (they 
don't have a starting date...). 

Because the associations between a Development and its Virtual Tour URLs 
are being maintained in a separate table, you will no longer need the 
fields names txtDevVirtualTour## on your tbl_development.

Did I help you at all or did I just muddy-up your situation?

Yours,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




news <news@stripped> wrote on 09/03/2004 02:17:59 PM:

> On Fri, 3 Sep 2004 13:06:45 -0400,  wrote:
> 
> >I don't understand.... what do you mean by "trim out the irrelevant"? 
If 
> >certain columns aren't important then why are they in your tables? I 
don't 
> >need any data, only the structures. You do know that you don't need to 
> >post to an actual NG to get your message to the list, right?. You only 
> >need to CC your emails to:
> >
> >mysql@stripped
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >"David Rayner" <david@stripped> wrote on 09/03/2004 01:02:01 PM:
> >
> >> Shawn,
> >> Will reply directly to NG (just had an access problem today) reason 
> >> am delating post is that one table is huge and I need to 
> >> trim out the irrelevant Thanks so far!!!
> >> 
> >> > 
> >> > 
> >> > You will normally get better responses if you always CC the list 
> >> in each of your replies (unless you 
> >> > are intentionally going off-list). 
> >> > 
> >> > Yes, it sounds like you are talking about a relationship to me, 
> >> too. If the we had your existing table 
> >> > structures in front of us we could help you to understand just how
> >> easy this is. This is a very 
> >> > important topic of database design. Please post as soon as you are 
> >able. 
> >> > 
> >> > Shawn Green
> >> > Database Administrator
> >> > Unimin Corporation - Spruce Pine 
> >> > 
> >> > "David Rayner" <david@stripped>wrote on 09/03/2004 11:15:41
> 
AM:
> >> > 
> >> > > Shawn,
> >> > > Will post & email table info later.
> >> > > 
> >> > > tbl_urldetails is essentially a lookup table containg a list of 
30 
> >or 
> >> > so urls.
> >> > > 
> >> > > each record in tbl_development references up to 8 of these URLS 
by 
> >> > > storing the id of the particular record in tbl_urldetails.
> >> > > 
> >> > > When I retrieve a record from tbl_development, I read the index 
and 
> >> > > then want to get the full record, I suppose I could do 
> >> > > this by doing a new query of tbl_urldetails (for each referenced 
> >> > > URLid) , but that seems wasteful or is it?
> >> > > 
> >> > > I suppose I'm talking about is a relationship? 
> >> > > 
> >> > > Surely this a bread and butter thing to do for a database (but I 
> >cant 
> >> > see it?)
> >> > > 
> >> > > > 
> >> > > > 
> >> > > > You say you have a table that changes how many columns it
> has? 
> >> > > Please post the results of: 
> >> > > > 
> >> > > > SHOW CREATE TABLE tbl_developent\G 
> >> > > > SHOW CREATE TABLE tbl_urldetails\G  (lookup table)
> 
>  CREATE TABLE `tbl_urldetails` (
> intID` int(11) NOT NULL auto_increment,
> `vtEhouseName` varchar(50) default NULL,
> `vtBarrattName` varchar(50) default NULL,
> `vtDescription` text,
> `vtInternalNote` varchar(50) default NULL,
> `vtHyperlink` varchar(50) default NULL,
> `dtmVTLastUpdate` timestamp(14) NOT NULL,
> `vtRandom` int(3) default NULL,
> PRIMARY KEY  (`intID`))
> TYPE=MyISAM
> 
>  CREATE TABLE `tbl_development` (
>  `intDevID` mediumint(9) unsigned NOT NULL auto_increment,
>  `txtBarrattID` varchar(20) default NULL,
>  `txtDevName` varchar(60) default NULL,
>  `txtDevWebSiteName` varchar(80) default NULL,
>  `dtmDevDate` datetime default NULL,
>  `dtmDevDateLastUpdate` datetime default NULL,
>  `txtDevDivision` varchar(30) default NULL,
>  `txtDevAddress` text,
>  `intDevStage` smallint(6) default NULL,
>  `txtDevPostCode` varchar(20) default NULL,
>  `txtDevCity` varchar(40) default NULL,
>  `txtDevRegion` varchar(40) default NULL,
>  `txtDevPropertyType` varchar(15) default NULL,
>  `txtDevVirtualTourURL` varchar(60) default NULL,
>  `txtDevVirtualTourURL1` varchar(60) default NULL,
>  `txtDevVirtualTourURL2` varchar(60) default NULL,
>  `txtDevVirtualTourURL3` varchar(60) default NULL,
>  `txtDevVirtualTourURL4` varchar(60) default NULL,
>  `txtDevVirtualTourURL5` varchar(60) default NULL,
>  `txtDevVirtualTourURL6` varchar(60) default NULL,
>  `txtDevVirtualTourURL7` varchar(60) default NULL,
>  `txtDevVirtualTourURL8` varchar(60) default NULL,
>  PRIMARY KEY  (`intDevID`))
>  TYPE=MyISAM 
> 
> 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/mysql?unsub=1
> 

Thread
Understanding Joins : Baffledzzapper3 Sep
  • Re: Understanding Joins : BaffledSGreen3 Sep
Re: Understanding Joins : BaffledSGreen3 Sep
Re: Understanding Joins : BaffledSGreen3 Sep
  • Re: Understanding Joins : BaffledMartin Gainty3 Sep
Re: Understanding Joins : Baffledzzapper3 Sep
  • Re: Understanding Joins : BaffledSGreen3 Sep
  • Re: Understanding Joins : Baffledzzapper5 Sep