attributes dependency

[Jürg Schaufelberger]

Hello

I have a lot of data in one table with about 10 columns. Two columns
are not
independent. Column A stores the foreign key of table A1, column B
stores
the foreign key of table B1.
So far it seems to be OK, but there is a dependency between these
columns.
If I choice a value in column A, the range of the second colum B is
limited
to certain values. What's the correct design to avoid inconsistance in
the
database ?
Thanks for your advices.

Jürg

 

[Jeff Boyce]

Let me try an example...

If I pick a value for ColumnA, which records the foreign key from TableA (a
list of countries), then the "legitimate" values in ColumnB will be foreign
keys from TableB, where country = the country I chose.

If this is analogous to your situation, I don't see any issues.

By the way, the "limiting" would need to be done via forms, as it is not a
feature of Access tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jürg Schaufelberger]

Hello
The example you have described is analogous to my situation. Thanks
for your answer.

 

[Tim Ferguson]

If I pick a value for ColumnA, which records the foreign key from
TableA (a list of countries), then the "legitimate" values in ColumnB
will be foreign keys from TableB, where country = the country I chose.

If this is analogous to your situation, I don't see any issues.

By the way, the "limiting" would need to be done via forms, as it is
not a feature of Access tables.

I always worry when I disagree with someone like Jeff, but I think that
Access does this, very easily. Can we put some concrete on the abstracts:

Nations(*NationCode, FullName, SpokenLanguage)
France, The Republic of France and its Overseas Dominions, French
England, England, English
USA, The United States of America, English-US

Cities(*CityName, CountryCode+, Weather)
Paris, USA, Good
Paris, France, Okay-ish
Birmingham, England, horrid
Birmingham, USA, hot & sticky
Washington, USA, cold
Washington, England, wet

People(*PersonID, Nationality+, CityOfBirth+)
Eric, France, Paris
Fred, England, Birmingham
Gavin, USA, Washington


Now, the Cities table is already broken: we'll have to reassign the
primary key to (CityName, CountryCode). In that case the second FK in the
People table has to change to (Nationality, CityOfBirth), but the
advantage now is that the OP's original problem is completely satisfied.
It is not possible to enter a CityOfBirth that is foreign to the
Nationality.

Point one: it's a silly example because (a) French people are sometimes
born in England; (b) there is more than one Newcastle in England; and so
on.

Point two: any database design only makes sense in the context of the
actual semantics of the modelled world. If, for some reason, this
database defined people according to their place of birth, then it
becomes a sensible database again. But that is why I worry about posts
that mention "Table A" and "Table B" because it's very hard to abstract
some of these models.

Just a thought!

All the best


Tim F

 

[Jeff Boyce]

Tim

I agree ... the problem with a simple analogy is ... that it is simple and
only an analogy.

The models/structures I end up building lean much more to the practical (get
something done) than the theoretical.

And I'm surprised you're concerned about disagreeing ... there's so much I
have yet to learn!

Jeff