Database design question!

[dippy_x]

Hi,

I've got a database design dilemma and was wondering if I could get
some thoughts on it.

I have a Goods table that tracks items that are imported from a country
and exported.

for Example a typical record reads:

[Import Country] -> [Export Country] -> [Description]
Britain -> USA -> Woodenbox

I have a table called country which lists all available country choices
and country codes.

I have [Import Country] set as the foreign Key / linked to the primary
key of the Country table. This works fine, as expected and as required.
My Dilemma comes from the [Export Country] field. The choices of
which are the exact same as the [Import Country] but access wont allow
me to define two relationships to the primary key of the country table
linking to different fields in the goods table. Only one or the other.

Whats the best way around this? Have a table each, ImportCountryList
and
ExportCountryList? It could be a nightmare keeping the tables the same
as when a new record's inserted into one I have to ensure its inserted
into the other etc. This leads to data repetition (bad) etc which is
making me think I'm going about this the wrong way. Ideally I would
like referential Integrity for both field in the Goods table (Import
Country and Export Country).

I'm hoping there's something simple I'm overlooking...

Thanks

Asi

 

[Bill Edwards]

In line...

Hi,

I've got a database design dilemma and was wondering if I could get
some thoughts on it.

I have a Goods table that tracks items that are imported from a country
and exported.

for Example a typical record reads:

[Import Country] -> [Export Country] -> [Description]
Britain -> USA -> Woodenbox

I have a table called country which lists all available country choices
and country codes.

I have [Import Country] set as the foreign Key / linked to the primary
key of the Country table. This works fine, as expected and as required.
My Dilemma comes from the [Export Country] field. The choices of
which are the exact same as the [Import Country] but access wont allow
me to define two relationships to the primary key of the country table
linking to different fields in the goods table. Only one or the other.

Don't know how you are attempting to create the relationship but Access will
allow this.
Go into the relationship window and add the Country Table twice. Access
will automatically name the second virtual country table Country_1. Create
a relationship between ImportCountry and the Country table and a
relationship between ExportCountry and Country_1.

 

[dippy_x]

Sorry,

I meant to add that I've tried adding in the table twice in the
relationship window. While it solves the relationship problem it
messes up the queries.

In the query builder If I add in the goods table, followed by the
country codes it creates two relationships from country codes to the
goods table. Where by running a select * on the Goods table returns
nothing. Adding in country codes again twice just leaves
country_codes_1 with no relationship defined though there is one in the
acual relationship diagram....

 

[Wolfgang Kais]

Hey Asi.

I've got a database design dilemma and was wondering if I could
get some thoughts on it.
I have a Goods table that tracks items that are imported from a
country and exported.
for Example a typical record reads:

[Import Country] -> [Export Country] -> [Description]
Britain -> USA -> Woodenbox

I have a table called country which lists all available country choices
and country codes.
I have [Import Country] set as the foreign Key / linked to the primary
key of the Country table. This works fine, as expected and as required.
My Dilemma comes from the [Export Country] field. The choices of
which are the exact same as the [Import Country] but access wont allow
me to define two relationships to the primary key of the country table
linking to different fields in the goods table. Only one or the other.

[snip]

Wrong. You can create your two relationships, separately, just try and
read the message and click the apropriate button (No). Access will
display one of the tables twice, one with "_1".

 

[Bill Edwards]

In the query builder add the goods table.
In the query builder add the country codes table. It will create two
relationships -- delete one of them
In the query builder add the country codes table again. Re-create the
relationship that you deleted in the previous step only using the
country_codes_1 table.

When you click on SQL view the FROM clause of the query should look
something like (you field and table names probably will be different):

FROM (tblCountry INNER JOIN tblImportExport ON tblCountry.CountryName =
tblImportExport.ImportKey)
INNER JOIN tblCountry AS tblCountry_1 ON tblImportExport.ExportKey =
tblCountry_1.CountryName;

 

[dippy_x]

Thanks for the feedback guys. It was because I was having to do extra
fiddling in the query design window (as mentioned by Bill) that I
thought maybe I was doing something wrong. But if thats the way to go,
and I'm not doing anything thats considered bad by design or a no-no,
then I can dig it.

Rgds

Asim