basic DB structure help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jim Bob - 14 Feb 2006 01:40 GMT

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.

Reply to this Message

Br@dley - 15 Feb 2006 01:28 GMT

> Hi,
> I can't seem to get my head around a simple DB structure problem...
[quoted text clipped - 18 lines]
> 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
[quoted text clipped - 6 lines]
> 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.

Signature

regards,

Br@dley

Reply to this Message