Relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Currently all of my tables hold one to one relationships on each other forcing referential integrity. Often, the relationships disappear, and sometimes cause my tables to be non-uniform. Why is this and how can I prevent it?
 
Greetings

One - to - One relationships are not a great idea, you might as well combine
all the tables into one big table. Relationships are disappearing means that
they are being deleted. Use the groups security in Access to give users
specific permissions.

Hope this helps
Albatross Singh



SCHNYDES said:
Currently all of my tables hold one to one relationships on each other
Click to expand...
forcing referential integrity. Often, the relationships disappear, and
sometimes cause my tables to be non-uniform. Why is this and how can I
prevent it?
 
Currently all of my tables hold one to one relationships on each other forcing referential integrity. Often, the relationships disappear, and sometimes cause my tables to be non-uniform. Why is this and how can I prevent it?
Click to expand...

As Sudhir says, one to one relationships are most unusual - if you're
not doing Subclassing (or don't know what Subclassing is) you may have
a flawed table design. Why are there one to ones?

It may be that the Relationships Window is up to its tricks (it's been
pretty buggy for years). The relationship *images* may be disappearing
with the tables still being related. You can use this code to list all
the relationships actually in place (whether shown in the diagram or
not):

Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub
 
Actually having the one to one has been useful, in helping us keep duplicate values from the tables. I work for a mortgage company, and the loans we track we track by loan number. One Loan(unique) and it's values. Any suggestions on cleaning up my table structure? I refuse to use an autonumber, it must go by Loan number to prove accuracy.
 
SCHNYDES said:
Actually having the one to one has been useful, in helping us keep duplicate values from the tables. I work for a mortgage company, and the loans we track we track by loan number. One Loan(unique) and it's values. Any suggestions on cleaning up my table structure? I refuse to use an autonumber, it must go by Loan number to prove accuracy.
Click to expand...

Who said anything about using an Autonumber? If you have a good natural key such as a Loan Number (and it's unique to a loan, stable, and reasonably short) by all means use it as the Primary Key.

But what do you mean by "its values"? What values are these? If there are fewer than 255 fields you can use one table; if there are multiple loan recipients, payments, etc. then it would seem that one-to-many relationships would be appropriate, not one-to-one.
 
The only times 1 to 1 relations are really usefull are if you store BLOBS
(OLE/memo things) and don't want to retrive them all the time (and you're to
*lazy* to use select a,b,c when you don't need that field) and/or the rare
occassions where you need more than 127 (255) fields in a table

My guess is that you've set up something like
Table LOANS
LOANID (Autonumber)
LOANNUMBER (Text/Long Int)

Table LOANDETAILS
LOANID (long int - fk)
.....

and thus (since you've learned that "dumb" keys is *the* way to make
relational db's - and you didn't need a relation (?!) - you went by the
book)
you ought to merge you tables into one....
it would however make sense to use a "dumb" key to link to the customer
table (1 -> many) as a person might have several loans ....

Pieter

SCHNYDES said:
Actually having the one to one has been useful, in helping us keep
Click to expand...
duplicate values from the tables. I work for a mortgage company, and the
loans we track we track by loan number. One Loan(unique) and it's values.
Any suggestions on cleaning up my table structure? I refuse to use an
autonumber, it must go by Loan number to prove accuracy.forcing referential integrity. Often, the relationships disappear, and
sometimes cause my tables to be non-uniform. Why is this and how can I
prevent it?
 
Back
Top