By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,165 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Creating relationships in front/back-end database

P: n/a
What is the difference between creating relationships in the front-end vs.
the back-end database? I was trying to create a relationship in a database
front-end and noticed that I could not check the referential integrity box.
What gives?

Continuing on with that line of thinking, I understand what do the
relationships do for you in a database, but what do they do physically to
the tables?

Thanks,

Mike
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
yes, you have to enforce relationships in the back end.

What would happen if you have two different front ends with different sets
of rules? Clearly the back end is the boss, and it really could not work any
other way. The fact that two front ends could have different rules is kind
of the tip off here (ie: rules are not rules if they can't be enforced!!).

Continuing on with that line of thinking, I understand what do the
relationships do for you in a database, but what do they do physically to
the tables?


Likely, not much is done to the tables in a physical sense. I mean, when you
ENFORCE a one to many relationship, the tables are not physically changed in
a special way. (sure, likely some indexes are added to the tables..but
nothing else real special happens).

However, that rule is saved somewhere, and the data engine (JET in this
case) looks at those rules..and follows them. So, for a typical one to many
relationship, a child record can NOT be added unless you supple it with a
proper parent record (ie: you can't orphan child records..and must supple a
parent record id first). Further, if you have cascade deletes enforced, then
of course when you delete the parent record, then all child records are
deleted.

So, I can't really say the tables are modified in a special way physically,
but when you create and set enforce relationships, the JET engine does
follow and enforce those relationships that you setup.

And, the relationships windows is very useful tool. I have re-posted a long
post of mine on this below:
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKal...Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

Nov 13 '05 #2

P: n/a

"Albert D. Kallal" <Pl*******************@msn.com> wrote

So, I can't really say the tables are modified in a special way
physically,
but when you create and set enforce relationships, the JET engine does
follow and enforce those relationships that you setup.


Thanks for the detailed post and all the information, I appreciate it.

I've been thinking about this relationship thing. My thought was Access
might create some sort index or lookup table between the two tables between
the pri and foreign keys.

Mike


Nov 13 '05 #3

P: n/a
Mike Turco wrote:
"Albert D. Kallal" <Pl*******************@msn.com> wrote

So, I can't really say the tables are modified in a special way
physically,
but when you create and set enforce relationships, the JET engine does
follow and enforce those relationships that you setup.

Thanks for the detailed post and all the information, I appreciate it.

I've been thinking about this relationship thing. My thought was Access
might create some sort index or lookup table between the two tables between
the pri and foreign keys.


The primary key already has an index, Access does automatically index
the foreign key columns, these (indices) are hidden in design view but
are present.

--
This sig left intentionally blank
Nov 13 '05 #4

P: n/a

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...
Mike Turco wrote:
"Albert D. Kallal" <Pl*******************@msn.com> wrote

So, I can't really say the tables are modified in a special way
physically,
but when you create and set enforce relationships, the JET engine does
follow and enforce those relationships that you setup.

Thanks for the detailed post and all the information, I appreciate it.

I've been thinking about this relationship thing. My thought was Access
might create some sort index or lookup table between the two tables
between the pri and foreign keys.


The primary key already has an index, Access does automatically index the
foreign key columns, these (indices) are hidden in design view but are
present.


What exactly is an index? Is it a physical table, the way a table is sorted,
or something else? Database theory, I believe, holds that data tables have
no particular beginning or end. So I envision an "index" as kind of a
look-up table. But what is it really?

Mike

Nov 13 '05 #5

P: n/a
"Mike Turco" <mi*******@yahoo-nospam4me.com> wrote in message
news:SZAEd.27002$Q%4.26447@fed1read06...

What exactly is an index? Is it a physical table, the way a table is
sorted, or something else? Database theory, I believe, holds that data
tables have no particular beginning or end. So I envision an "index" as
kind of a look-up table. But what is it really?

Mike


A index usually uses the data engine to store its data also! So, in fact, an
index is a form of a table. And, the data in this table is usually organized
as a what we call a tree, or in this case a binary balanced tree. The
computing concepts of recursion, and linked lists are *usually* used to
create a balanced binary tree.

As far as I know, ms-access does not expose the hash table, or machinery by
which record ID are retuned (and, in fact, ms-access does not even expose
record ID's (but, they do exist!!). A hash table is a form of indexing that
does NOT requite an additional file, or "extra" index to resolve where on
the disk drive the data is *likely* to be. Ms-access uses pages to store
data..and a hash system to get at that data. This hash code system is used
to implement a higher level (traditional) index where record ID's can be
resolved to a particular record on the disk drive.

In simply terms, those indexes eventually resolve to a record ID, and then
hash code is used to get that record ID. In older systems (like dbaseIII,
and FoxPro, the record ID was exposed, but then these systems where fixed
length record systems, and resolving to a record was based on its position
in the file...and systems like ms-access don't work that way (they use a
hash code).

Conceptually, the binary tree looks very much like the leaves on a branch of
a tree.

You could design and write your own indexing routines in ms-access if you
wanted to. With this structures, only about 4 or 5 disk reads occur when you
search a file with 1 million records. If they had exposed the hash-table,
you could get to one record likely with only 1, or 2 disk reads! (but, would
have to use the record ID, which in modern systems is not exposed).

Do a Google on the web on "balanced trees database systems". You will get
more then you ever want to read...

Here one that comes up:

http://www.bluerwhite.org/btree

And, you can do some Google stuff on hash tables and databasesw if you want
more info...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.