Relationships question

[ARC]

Hello all,

Prior to going live with my app, I have questions on relationships theory.

My prior app was done in Access 97, and I did NOT use relationships at all.
I have 65 tables in my back-end database, and with Access 97, if you added
relationships, it would add multiple copies of the same relationships over
and over, so I decided not to use any relationships at all.

Additionally, when I needed to add or remove fields via code, it was a lot
easier without relationships, as you didn't need to worry about adding
relationships when adding new fields, or removing them if you needed to
delete an obsolete field.

So the question is, I'm about to deploy the app, re-worked in in Access
2007, and with the 65 tables, The relationships screen in the back-end
database is very full. I would think that so many relationships would
actually slow the database down, and would make table maintenance much more
difficult in the future.

Can anyone give me their opinions, pros and cons, on whether to have
relationships or not?

Many thanks,

--
Andy

[Tom van Stiphout]

On Mon, 20 Aug 2007 13:27:55 GMT, "ARC" <an**@andyc.comwrote:

Yes, such a db would be slower (there are additional indexes and
checks to be made), but the difference is not noticible and the effect
is negligible compared to the ENORMOUS benefit of data integrity.

I'm sure if you went back to your A97 db, you could pretty easily find
orphaned data, couldn't you?

-Tom.

>Hello all,

Prior to going live with my app, I have questions on relationships theory.

My prior app was done in Access 97, and I did NOT use relationships at all.
I have 65 tables in my back-end database, and with Access 97, if you added
relationships, it would add multiple copies of the same relationships over
and over, so I decided not to use any relationships at all.

Additionally, when I needed to add or remove fields via code, it was a lot
easier without relationships, as you didn't need to worry about adding
relationships when adding new fields, or removing them if you needed to
delete an obsolete field.

So the question is, I'm about to deploy the app, re-worked in in Access
2007, and with the 65 tables, The relationships screen in the back-end
database is very full. I would think that so many relationships would
actually slow the database down, and would make table maintenance much more
difficult in the future.

Can anyone give me their opinions, pros and cons, on whether to have
relationships or not?

Many thanks,

[ARC]

I just added the relationships in 2007, I didn't set any referential
integrities.

You're right about my 97 app. For example, when they wanted to delete a
customer, I ran code that would delete records in other tables, such as a
letters table, quotes, quote details, etc., and I would sometimes deleting
from a table.

So by orphaned data, do you mean to make sure I use the options to maintain
referential integrity?

"Tom van Stiphout" <no*************@cox.netwrote in message
news:ue********************************@4ax.com...

On Mon, 20 Aug 2007 13:27:55 GMT, "ARC" <an**@andyc.comwrote:

Yes, such a db would be slower (there are additional indexes and
checks to be made), but the difference is not noticible and the effect
is negligible compared to the ENORMOUS benefit of data integrity.

I'm sure if you went back to your A97 db, you could pretty easily find
orphaned data, couldn't you?

-Tom.

>>Hello all,

Prior to going live with my app, I have questions on relationships theory.

My prior app was done in Access 97, and I did NOT use relationships at
all.
I have 65 tables in my back-end database, and with Access 97, if you added
relationships, it would add multiple copies of the same relationships over
and over, so I decided not to use any relationships at all.

Additionally, when I needed to add or remove fields via code, it was a lot
easier without relationships, as you didn't need to worry about adding
relationships when adding new fields, or removing them if you needed to
delete an obsolete field.

So the question is, I'm about to deploy the app, re-worked in in Access
2007, and with the 65 tables, The relationships screen in the back-end
database is very full. I would think that so many relationships would
actually slow the database down, and would make table maintenance much
more
difficult in the future.

Can anyone give me their opinions, pros and cons, on whether to have
relationships or not?

Many thanks,

[Tom van Stiphout]

On Mon, 20 Aug 2007 13:49:06 GMT, "ARC" <an**@andyc.comwrote:

Absolutely. That's what I was assuming. Without checking the box to
enforce RI, you've only added some pretty lines that may be helpful
with query design.

-Tom.

>I just added the relationships in 2007, I didn't set any referential
integrities.

You're right about my 97 app. For example, when they wanted to delete a
customer, I ran code that would delete records in other tables, such as a
letters table, quotes, quote details, etc., and I would sometimes deleting
from a table.

So by orphaned data, do you mean to make sure I use the options to maintain
referential integrity?

"Tom van Stiphout" <no*************@cox.netwrote in message
news:ue********************************@4ax.com.. .

>On Mon, 20 Aug 2007 13:27:55 GMT, "ARC" <an**@andyc.comwrote:

Yes, such a db would be slower (there are additional indexes and
checks to be made), but the difference is not noticible and the effect
is negligible compared to the ENORMOUS benefit of data integrity.

I'm sure if you went back to your A97 db, you could pretty easily find
orphaned data, couldn't you?

-Tom.

>>>Hello all,

Prior to going live with my app, I have questions on relationships theory.

My prior app was done in Access 97, and I did NOT use relationships at
all.
I have 65 tables in my back-end database, and with Access 97, if you added
relationships, it would add multiple copies of the same relationships over
and over, so I decided not to use any relationships at all.

Additionally, when I needed to add or remove fields via code, it was a lot
easier without relationships, as you didn't need to worry about adding
relationships when adding new fields, or removing them if you needed to
delete an obsolete field.

So the question is, I'm about to deploy the app, re-worked in in Access
2007, and with the 65 tables, The relationships screen in the back-end
database is very full. I would think that so many relationships would
actually slow the database down, and would make table maintenance much
more
difficult in the future.

Can anyone give me their opinions, pros and cons, on whether to have
relationships or not?

Many thanks,

[ARC]

This may take some doing with 65 tables. I've never used the Ref. integrity
before, so it looks like I'll have to examine each relationship (and it's
already hard to look at the relationships screen with all the objects it
contains).

For example, if you have the following:

Parts table, PartID

PartID is also contained in:

InvoiceDetail, QuoteDetail, Inventory, PartSpecs, PartNotes, JobPartsUsed,
BillingMonthlyCharges, BillingMChargesTemplate, RMADetail.

Currently, however, I wouldn't let them delete a part that is in use in any
of the above tables. For example, if they wanted to remove a part contained
on quotes, and all the quotes it appeared in, I would have to write code to
recalculate all the quotes, so I just prevent them from deleting.

I have lots of code like this. Another example, I won't let them delete a
customer if the custID is in use in invoices, quotes, jobs, etc.

Any strategies you can offer for going through them?
Thanks for the replies!

"Tom van Stiphout" <no*************@cox.netwrote in message
news:d2********************************@4ax.com...

On Mon, 20 Aug 2007 13:49:06 GMT, "ARC" <an**@andyc.comwrote:

Absolutely. That's what I was assuming. Without checking the box to
enforce RI, you've only added some pretty lines that may be helpful
with query design.

-Tom.

>>I just added the relationships in 2007, I didn't set any referential
integrities.

You're right about my 97 app. For example, when they wanted to delete a
customer, I ran code that would delete records in other tables, such as a
letters table, quotes, quote details, etc., and I would sometimes deleting
from a table.

So by orphaned data, do you mean to make sure I use the options to
maintain
referential integrity?

"Tom van Stiphout" <no*************@cox.netwrote in message
news:ue********************************@4ax.com. ..

>>On Mon, 20 Aug 2007 13:27:55 GMT, "ARC" <an**@andyc.comwrote:

Yes, such a db would be slower (there are additional indexes and
checks to be made), but the difference is not noticible and the effect
is negligible compared to the ENORMOUS benefit of data integrity.

I'm sure if you went back to your A97 db, you could pretty easily find
orphaned data, couldn't you?

-Tom.

Hello all,

Prior to going live with my app, I have questions on relationships
theory.

My prior app was done in Access 97, and I did NOT use relationships at
all.
I have 65 tables in my back-end database, and with Access 97, if you
added
relationships, it would add multiple copies of the same relationships
over
and over, so I decided not to use any relationships at all.

Additionally, when I needed to add or remove fields via code, it was a
lot
easier without relationships, as you didn't need to worry about adding
relationships when adding new fields, or removing them if you needed to
delete an obsolete field.

So the question is, I'm about to deploy the app, re-worked in in Access
2007, and with the 65 tables, The relationships screen in the back-end
database is very full. I would think that so many relationships would
actually slow the database down, and would make table maintenance much
more
difficult in the future.

Can anyone give me their opinions, pros and cons, on whether to have
relationships or not?

Many thanks,

[Tom van Stiphout]

On Mon, 20 Aug 2007 22:45:29 -0500, "ARC" <an**@andyc.comwrote:

No, the relationships are only deleted if you delete the lines between
the tables, not if you remove a table from the view (by "deleting"
it).

-Tom.

>If you delete all tables from the relationship view, won't it delete the
relationship? I like your idea of paring down the view of tables, thanks for
that.

<clip>

[ARC]

Tom and Albert,

Many thanks again. And Tom, your idea of searching the tabledefs is working
perfectly. I missed PartID in 2 tables. Actually, clicking the RE to enforce
is helping me see what I need to code for in my upgrade routine. I've found
a couple cases where the partID was no longer there, and a couple where
PartID = 0.

Also, for anyone in the same boat, and can't find the code, I came up with a
quick routine where you pass the search text, such as PartID, and then view
the results:

Public Function FindTextGlobal(searchtxt As String)
On Error Resume Next
Dim fld As Field, mydb As Database, SaveYN As Integer, i As Integer, DelText
As Integer, FName As String, cntrl As String, searchin As String, db As
Database, mytable As TableDef, r As Report, f As Form, ctrl As Control
SaveYN = acSaveNo
Set mydb = DBEngine.Workspaces(0).Databases(0)
For i = 0 To mydb.TableDefs.Count - 1
Set mytable = mydb.TableDefs(i)
For Each fld In mytable.Fields
If fld.Name = searchtxt Then
Debug.Print "Field: " & searchtxt & " found in table: " &
mytable.Name
End If
Next fld
Next i
mydb.Close
db.Close
End Function

[ARC]

Ok, couple of bad designs, although my intentions were good: To have less
objects.

I have about 3 multi-use tables:

Addresses:

This stores additional addresses for both Customers and Suppliers. To make
it multi-user, I have 2 fields: CustSupplierID and CustYN. So if CustYN is
set to yes, then the CustSupplierID holds the CustID. Otherwise, if CustYN
is no, CustSupplierID is the SupplierID.

Same goes for a table: tLetters. It has the same CustVendID and CustYN, with
the same logic.

Finally, I have a table called SerialNos that is multi-use in the respect
that it holds partID's and serial numbers for tracking serial numbers on
both the Purchase side and Invoices (Sales) side. I allow the tracking of
serial numbers that are taken in on a purchase, and when the item is sold on
an invoice, I create another entry in SerialNos that adds in the invoice.
Unlike the other 2 tables, however, there are no multi-use fields. I have
both an InvoiceNo field and a PurchaseNo field, each storing the primary
keys of the invoices and purchase tables. In hindsight, I should have been
updating the original purchase and using only one entry for BOTH the
InvoiceNo and PurchaseID. However, I have one record for the purchase, and
one for the Invoice. This design happened because purchases was not part of
my original design, and additionally user's can choose not to use the
purchase screen at all, and simply track serial nos on the invoice side
only.

So that's it, the question is what to do with RE and relationships for the
above 3.

Thanks!

Andy

[David W. Fenton]

"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:ATUyi.81811$rX4.63798@pd7urf2no:

I can't recall the last time I used cascade updates. It means in
the effect your change the key value used in a relationships. I
suppose if you were to move a incorrect invoice "details" from one
customer to another, you might use cascade updates, but even that
is rare.

I can't see how that would involve a cascade update. If you moved
the invoice to a different customer, you'd update the CustomerID FK
value in the invoice, but that would have no effect on the invoice
items, so there's no update to cascade. If you moved the invoice
items to a different invoice, you'd update the InvoiceID FK in the
invoice items table, but there's nothing to cascade there, either.

So, when you are using surrogate keys, there is never any need for
CASCADE UPDATE -- it is only needed when the parent value could be
edited.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"ARC" <an**@andyc.comwrote in
news:mI**************@nlpi061.nbdc.sbc.com:

The more I think of it, the more it sounds like I only want to use
cascade deletes on customers. But must first check that invoices
are not present for a customer, and not allow deletion if they
are. If there are no invoices, I don't mind if they want to delete
everything associated with a CustID: Customer rec, Letters
records, Quotes, Quote Detail, and the like.

In that case, have CASCADE DELETE turned ON for all those tables,
and OFF for the INVOICES. That's the standard structure in all the
billing apps I've built -- anyone with $$$ transaction history can't
be deleted.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tony Toews [MVP]]

"ARC" <an**@andyc.comwrote:

>You're right about my 97 app. For example, when they wanted to delete a
customer, I ran code that would delete records in other tables, such as a
letters table, quotes, quote details, etc., and I would sometimes deleting
from a table.

Now I'd never do that. History can be useful. I'd have an inactive or a status flag
and move that customer to the bottom of various forms and combo boxes. But that
customer would still be available for inquiry purposes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

"ARC" <an**@andyc.comwrote:

>- When to check the cascade updates and cascade deletes.

I never, ever use them.

I have an intense dislike for cascade deletes in Microsoft Access. And I don't like
cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

>For example, if I
have a parts table, quotesDetail and InvoicesDetail table, that both contain
the partID, if a part is deleted, naturally I would want the data deleted
out of quotes and invoices detail,

Never, ok, very, very seldom, delete data. Now your historical reports will be
wrong. Inactivate the part and put it at the bottom of forms and combo boxes but
never delete it.

>- Note quite sure where / when you would use a cascade update.

If you use an autonumber primary key then never.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[ARC]

Hi Tony,

I should have said that I already had code in place for NEVER allowing
deletes of invoice data when payments were present. I allow an invoice
delete Only in the even that they created the invoice by mistake, but if
payments exist, I don't allow it.

I'm going to create a new post on RE and cascade deletes, because I've found
a good case for using them.

As always, Thanks for your reply! Always appreciated.

Andy
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:p7********************************@4ax.com...

"ARC" <an**@andyc.comwrote:

>>- When to check the cascade updates and cascade deletes.

I never, ever use them.

I have an intense dislike for cascade deletes in Microsoft Access. And I
don't like
cascade updates.
http://www.granite.ab.ca/access/cascadeupdatedelete.htm

>>For example, if I
have a parts table, quotesDetail and InvoicesDetail table, that both
contain
the partID, if a part is deleted, naturally I would want the data deleted
out of quotes and invoices detail,

Never, ok, very, very seldom, delete data. Now your historical reports
will be
wrong. Inactivate the part and put it at the bottom of forms and combo
boxes but
never delete it.

>>- Note quite sure where / when you would use a cascade update.

If you use an autonumber primary key then never.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

"ARC" <an**@andyc.comwrote:

>I should have said that I already had code in place for NEVER allowing
deletes of invoice data when payments were present. I allow an invoice
delete Only in the even that they created the invoice by mistake, but if
payments exist, I don't allow it.

That's almost a good reason. <smile But even then I'd inactivate the invoice.
After all you've consumed the invoice number and auditors don't like missing invoice
numbers.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/