By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

basic DB structure help

P: n/a
Hi,
I can't seem to get my head around a simple DB structure problem...
Currently I have
[MainTable]
DBID
LastName
FirstName
DateOfInterview
SitePreference1ID ->SiteTable
SitePreference2ID ->SiteTable
SitePreference3ID ->SiteTable

[SiteTable]
SiteID
Site

I understand it is not correctly normalized. If I remove the
sitepreference from [MainTable] and have a third table that does the
join, ie
[thirdTable]
SiteID
DBID

that is ok. My Question is:
I want to query and find out what the 3 site preferences are, for a
particular person on a particular date. eg
002 | John | White | 1-jan04 | USA | Japan | Australia

-SitePref2 and sitePref3 are optional.
-only a max of 3 site pref allow.

I had a thought about think in terms of a "vertical" structure as
opposed to the "horizontal" structure of excel but still lost..

Any help appreciated.
Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Feb 14 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Br
jim Bob wrote:
Hi,
I can't seem to get my head around a simple DB structure problem...
Currently I have
[MainTable]
DBID
LastName
FirstName
DateOfInterview
SitePreference1ID ->SiteTable
SitePreference2ID ->SiteTable
SitePreference3ID ->SiteTable

[SiteTable]
SiteID
Site

I understand it is not correctly normalized. If I remove the
sitepreference from [MainTable] and have a third table that does the
join, ie
[thirdTable]
SiteID
DBID
It is not always necessary to normalise to this extent, depending on your
requirements.
that is ok. My Question is:
I want to query and find out what the 3 site preferences are, for a
particular person on a particular date. eg
002 | John | White | 1-jan04 | USA | Japan | Australia

-SitePref2 and sitePref3 are optional.
-only a max of 3 site pref allow.

I had a thought about think in terms of a "vertical" structure as
opposed to the "horizontal" structure of excel but still lost..


Using the "not fully normalised" structur where you have three fields in the
main table to store the preferences a query like this would get the
preferences:

SELECT tblMain.ID, tblMain.Name, tblSite.SiteDesc AS Pref1,
tblSite_1.SiteDesc AS Pref2, tblSite_2.SiteDesc AS Pref3
FROM ((tblMain LEFT JOIN tblSite ON tblMain.SiteID1 = tblSite.SiteID) LEFT
JOIN tblSite AS tblSite_1 ON tblMain.SiteID2 = tblSite_1.SiteID) LEFT JOIN
tblSite AS tblSite_2 ON tblMain.SiteID3 = tblSite_2.SiteID;

If you normalise it then there is no limit on the number of preferences in
the db structure. That would have to be implemented in the user interface.

In that case the following query would return the preferences:

SELECT tblMain.ID, tblMain.Name, tblMainSites.SiteID, tblSite.SiteDesc
FROM (tblMain LEFT JOIN tblMainSites ON tblMain.ID = tblMainSites.ID) LEFT
JOIN tblSite ON tblMainSites.SiteID = tblSite.SiteID;

Of course you can then put further filters in it to get specific dates etc.

just add ....

WHERE [Person] = "John" AND [Date] = #01/01/2006#

Hope that makes sense.
--
regards,

Br@dley
Feb 15 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.