Linking tables

[Joseph]

I am having a problem linking tables in my database.

The main Table: Borrower
Sub tables: Street Address; Mailing Address, Current Work, Previous Work,
Co-Borrower, References,School,...

The problem is that the table Street Address is accesses by six other
tables. Why? Because all those tables need a Street Address. The problem is
when I put the tables into forms, with subforms to Street Address, I get the
error that I can not assign a value to the object.

Is there a case where there are too many cascading master to child links?
This is the only thing that I can figure out. I have thought about putting
Street Address in the the tables, but then the size of the table would
triple, and the fact that the information is also redundant in alot of cases.


Please give me some information on this.
Thank you
Joseph Robert Martinez

 

[John Vinson]

I am having a problem linking tables in my database.

The main Table: Borrower
Sub tables: Street Address; Mailing Address, Current Work, Previous Work,
Co-Borrower, References,School,...

The problem is that the table Street Address is accesses by six other
tables. Why? Because all those tables need a Street Address.

Ummm...

Why?

Are you recording the Streed Address of the Co-Borrower or of the
School? If so, then you do indeed need a separate street address
field, and there shouldn't be any difficulties with it if you're
careful to use [Co-Borrower].[Street Address] to resolve the ambiguity
in name. Or do they all need the borrower's street address? No, they
don't! You can always look that up with a Query.

John W. Vinson[MVP]

 

[Joseph]

So you are say, for instance, put the fields for Street Address in School,
Current Work, and Previous Work, and keep a linked table for Borrower,
Co-Borrower and Student, since they could be at the same shared address? But
doesn't that mean redundant fields across the database?

 

[peregenem]

So you are say, for instance, put the fields for Street Address in School,
Current Work, and Previous Work, and keep a linked table for Borrower,
Co-Borrower and Student, since they could be at the same shared address? But
doesn't that mean redundant fields across the database?

'Addresses' are tricky. Generally speaking, an address is an attribute
of an entity. However, sometime it is more appropriate, or convenient,
to model as a entity (generic or specific) in its own right. You have
to avoid both redundancy *and* attribute splitting (where the database
gets too fragmented because attributes are inappropriately modelled as
entities, requiring complicated SQL DML to get the simplest of
resultsets).

One scenario I urge you to consider early on is when a 'person' moves
house and hence their 'address' changes: do you UPDATE their address
entity so that all 'persons' with the same 'address' receive the
change? do you create a new address entity relate all affected persons?
if the former address entity is now orphaned, do you remove it from the
database? etc (My advice is to revoke UPDATE permissions from any
'Addresses' table.)

Another issue is what to use as a key in an 'Addresses' table. No, I
don't mean an artificial/surrogate 'key' such as an autonumber; I mean,
a natural key. How do tell one address is distinct from another address
so you can avoid storing the same address twice? There are address
identifiers out there, depending on country or region; you need to do
some research.

As for CASCADE paths, the current version of Jet (version 4) is very
effective - better than the current version of SQL Server/ MSDE (2000
release) - and should have no trouble with six direct CASCADE paths.

 

[John Vinson]

So you are say, for instance, put the fields for Street Address in School,
Current Work, and Previous Work, and keep a linked table for Borrower,
Co-Borrower and Student, since they could be at the same shared address? But
doesn't that mean redundant fields across the database?

Well... you know your data better than I do, so I can't really say for
sure. I would assume that you have two choices:

1. Every Entity which has an Address attribute should have an Address
field (or fields, as address/city/state/postcode fields). The downside
here is that, as you indicate, different entities (e.g. a borrower and
a student) may well have the same address, causing wasted space and
(more seriously) the risk of update anomalies when you change one
address and not the other.

2. There is a separate Address entity, related many-to-many to each
entity which has an address, or addresses. The downside here is a more
complex table structure, with one address table but multiple linking
tables.

As peregenem says, it can be complicated!

John W. Vinson[MVP]

 

[Joseph]

Thank you both for your input.

I think that the most simpliest and effiecient for everybody is to code it
in C++! I'll just have to figure out how to link between C++ code and VB
interface.

No, may be not.

I will have to do alittle bit of both your anwers.
peregenem: updating the data should not be a problem. I will just add
code to tell the database to check for redundacy before storing the data, or
making the primary key the first part of the address, ie: street address of
17061 Some St. and saying no dups.
John: I will work on that many-to-many relationship to allow multiple
tables to access one table.

Thank you
Joseph

So you are say, for instance, put the fields for Street Address in School,
Current Work, and Previous Work, and keep a linked table for Borrower,
Co-Borrower and Student, since they could be at the same shared address? But
doesn't that mean redundant fields across the database?

Are you recording the Streed Address of the Co-Borrower or of the
School? If so, then you do indeed need a separate street address
field, and there shouldn't be any difficulties with it if you're
careful to use [Co-Borrower].[Street Address] to resolve the ambiguity
in name. Or do they all need the borrower's street address? No, they
don't! You can always look that up with a Query.

John W. Vinson[MVP]

 

[Joseph]

entity which has an address, or addresses. The downside here is a more
complex table structure, with one address table but multiple linking
tables.

Ok, I have the tables, that was easy, but how do I enter/manipulate the data?

There are one way that I know how, but is not very user friendly. It
envolves making a form with all the Borrower information and having a
combo/list box displaying all the addresses in the data base. Entering the
data is, by either by double clicking on the box or clicking a button,
opening another form, entering the data, requeiring the data, sorting it by
newest entry so that it is on top in the combo/list box.

Please do not tell me that this is the only way?