Object dependencies vs. relationships

[MTLady]

I'm struggling to understand when you need a relationship set between 2
tables. The textbook I'm studying from, 'The Professional Approach-Access
2007', seems to highly recommend setting up relationships again and again. I
have the impression, that they are critical to the functionality of the
database. But...

When I insert a subdatasheet, into another table, I'm given a Warning that
there is no relationship setup, but I'm allowed to continue to insert the
subdatasheet without setting up a relationship. I look in the Relationship
window (all relationships) and nothing is there. I check the Object
Dependencies for that table and there's the other table from the subdatasheet.

So it appears that I don't "have" to create a relationship between these two
tables in order to insert a subdatasheet. So this makes me think, when do I
HAVE to create a relationship and what is the difference between a
relationships and object dependencies?

 

[George Hepworth]

Although there is a good deal more to it than can be presented in a few
paragraphs, it is safe to say that properly defined relationships are
integral to designing and developing relational databases and it would be
foolish not to do so.. In other words, it should have become more than "an
impression" that they are critical. You can make it a foundational principle
in your understanding of databases, if you intend to pursue the work.

It is possible to create a set of tables in any database application and not
define the relationships between them. That much is true. However, just
because you CAN do that, doesn't mean it is a good idea to do that.

What you need to understand is WHY and HOW to define relationships. I've not
read that particular book, but if the author reinforces the importance of
defining relationships, it must be a good book.

Now, to your particular question. In fact, you can insert a table as a
subdatasheet into another table, whether or not there is a relationship
between them. But in either case, I would say that doing so is a
spectacularly bad idea. Don't do it at all. Again, just because you can,
doesn't mean you should. Particularly in situations where your database is
deployed in a networked environment, subdatasheets are likely to prove to be
a drag on performance. Just don't go there. It's not worthwhile.

You must define relationships between tables whenever those tables represent
entities that have a relationship of some sort in the real world. I can't
see your tables, of course, so I don't know what you have to work with, so
we'll take a common example: People and phone numbers. You may have a roster
table of people. Most people have one or more phone numbers (phone, cell,
work). Those phone numbers will be in a separate table. There IS a
relationship between people and their phone numbers, and that is where the
relationship definition comes in. You assign a primary key to the people
table and a foreign key to the phone number table. For each phone number
belonging to a person, you insert that person's Primary Key from the people
table into the corresponding Foreign Key in the phone number table.

Let's say that in this same database, you also have a "Product" table, in
which you list all of the products offered for sale by a company. There is
no relationship between phone numbers and products. Therefore, there is no
need to define a relationship between the two tables.

"Object Dependencies" is purely related to the mechanics of the database.
It's based on the relationships defined between objects. For your purposes,
the MOST important thing is the relationships.

HTH

George

 

[pietlinden]

I'm struggling to understand when you need a relationship set between 2
tables.  The textbook I'm studying from, 'The Professional Approach-Access
2007', seems to highly recommend setting up relationships again and again..  I
have the impression, that they are critical to the functionality of the
database.  But...

When I insert a subdatasheet, into another table, I'm given a Warning that
there is no relationship setup, but I'm allowed to continue to insert the
subdatasheet without setting up a relationship.  I look in the Relationship
window (all relationships) and nothing is there. I check the Object
Dependencies for that table and there's the other table from the subdatasheet.

So it appears that I don't "have" to create a relationship between these two
tables in order to insert a subdatasheet.  So this makes me think, whendo I
HAVE to create a relationship and what is the difference between a
relationships and object dependencies?

Relationships enforce rules for you... basically not allowing you to
add records to child tables without already having a related record in
the parent table. Say you have a database for Invoicing. With
relational integrity enforced, you cannot insert/update records in the
InvoiceLineItems table without a related Invoice ID existing in the
Invoices table. You don't *have* to enforce referential integrity
this way, but it requires more skill/experience to do it yourself. If
memory serves, Tony Toews says he doesn't use the built-in referential
integrity... but then he's been working with databases for 20+ years
and can roll his own solution. I guess 20+ years of experience will
do that for ya...

 

[George Hepworth]

Reading this response makes me realize I overlooked a rather critical point
earlier. That is: Relationships exist whether or not we formally define them
with the tools in the database application. In other words, the relationship
between an Invoice and the line items in an invoice is a LOGICAL one;
invoices consist of line items whether we are tracking them in a database or
not. In a fundamental way, it matters not at all that the developer chooses
to enforce RI on that relationship at the database level. The relationship
is there and we can, as developers, choose to manage the data with the tools
provided by the tool, or with our own code.

So, rather than saying that we don't have to CREATE a relationship in the
database, I would say it is more accurate to say that we don't have to
FORMALLY DEFINE it in the database.

 

[De Jager]

I'm struggling to understand when you need a relationship set between 2
tables. The textbook I'm studying from, 'The Professional Approach-Access
2007', seems to highly recommend setting up relationships again and again.
I
have the impression, that they are critical to the functionality of the
database. But...

When I insert a subdatasheet, into another table, I'm given a Warning that
there is no relationship setup, but I'm allowed to continue to insert the
subdatasheet without setting up a relationship. I look in the
Relationship
window (all relationships) and nothing is there. I check the Object
Dependencies for that table and there's the other table from the
subdatasheet.

So it appears that I don't "have" to create a relationship between these
two
tables in order to insert a subdatasheet. So this makes me think, when do
I
HAVE to create a relationship and what is the difference between a
relationships and object dependencies?