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 <[EMAIL PROTECTED]> 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:
> >
> >[EMAIL PROTECTED]
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >"David Rayner" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>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/[EMAIL PROTECTED]
>
