473,729 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating relationships in front/back-end database

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
5 4699
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

"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
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

"Trevor Best" <no****@besty.o rg.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
"Mike Turco" <mi*******@yaho o-nospam4me.com> wrote in message
news:SZAEd.2700 2$Q%4.26447@fed 1read06...

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

30
2564
by: Sean R. Lynch | last post by:
I've been playing around with Zope's RestrictedPython, and I think I'm on the way to making the modifications necessary to create a capabilities-based restricted execution system. The idea is to strip out any part of RestrictedPython that's not necessary for doing capabilities and do all security using just capabilities. The basic idea behind capabilities is that you don't give any piece of code you don't trust a reference to something...
6
2703
by: Jade | last post by:
Hi, I am trying to create a dataset with 6 tables and 5 relationships created between them. I am creating it in the Form_Load event. However i keep getting the following error... An unhandled exception of type 'System.ArgumentNullException' occurred in system.data.dll Additional information: 'column' argument cannot be null. The code stops on this line... Dim rel_Booking_CaraBook As New DataRelation("dsFullBooking",...
2
2225
by: Andrea | last post by:
I'm having some difficulty creating a report in Access and I need some suggestions. My company issues "Return Authorizations" when customers need to return products. A customer calls in and we provide the number. The customer then returns items for a refund or for an exchange. Here is the basic set up of the tables in question. -ReturnAuthorizationTable- ReturnNumber_PK CustomerID ReturnType
8
4328
by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub Function/Module that creates relationships for my tables. The problem is that I need to provide for some tables that may have > 32 relationships (which is apparently the limit on Indexes that Access can support). How can I prevent Access from...
3
11725
by: GGerard | last post by:
Hello Does anyone know if it is possible with Access 2000 to create relationships between tables using code? Thanks G.Gerard
4
2025
by: Ronnie | last post by:
Ok let me just say first that I am a newbie in Access and I don't know much of SQL or VB programming. But I am trying to create this contact database using Access 97. I have created 2 tables, one Personal (it has all the personal information) and the other one Organization (this one has details of diff organizations). One person can belong to more than any (upto 5 in this case) organizations. So I included orgid1, orgid2, orgid3, orgid4...
2
6117
by: Gandalf | last post by:
I'm creating relationships between tables using VBA and ADOX. I can create one-to-one relationships with an inner join, but I can't figure out how to create these relationships with an outer join (specifically a left outer join). I'm including the code that creates the relationships with the inner join. Any help or suggestions would be greatly appreciated. Thanks! <----- CODE FOLLOWS ----->
4
2541
by: jmarr02s | last post by:
I am using Access 2003 and am having difficulty creating a subform that will save records associated with a particular parent record.... The error message I receive pertains to indexes, relationships, etc... Thanks!
1
1420
by: YouTube | last post by:
Hi All, Can anyone help with this problem: I have designed the tables, created the relationships etc, since this morning, when when I go to view the relationships there is nothing there. When I add the tables to the relationship five of the same table appear. For example TblCustomer1, TblCustomer2 >>>>>>>> TblCustomer5. When I delete them, bar the first table, I save everything. When I go out of the view relationship and go back in the same...
2
1788
by: Phil Stanton | last post by:
Sorry to re-post this question but still having problems with relationships I am running duplicate databases on 2 machines, Ak2 SP3 and windows XP SP2. I hace 2 databases both with FE & BEs. I can freely copy them between the 2 machines, both FE & BE On my home machine the relationships are normal, but on the office machine if I look at the relationships in the FE database, all the tables are there, but non of the linking lines. Equally...
0
8932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8767
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9222
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9163
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6028
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4537
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2702
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2173
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.