join line

When I draw a join line between two tables, A "1" appears
on one end and a small figure 8 type symbol on the other
end. What do these mean?

You have set up the tables in such a way that there is one record in one
table that matches many (the 8 is really the infinity symbol) records in the
other table based on the Primary Key.
e.g.
An Account table may have 100 accounts.
A Purchase table may list many purchases made by each of the Accounts in the
first table, so the relationship is 1 to many.

HTH
Mich

"navigator" wrote in message
...
When I draw a join line between two tables, A "1" appears
on one end and a small figure 8 type symbol on the other
end. What do these mean?

The 1 means "one" and the line is drawn to another table, and that is a the
"many" side.

One customer can have "many" invoices for example. This is a classic one to
many relationship.

the "8" type symbol is the omega sign, and simply means that the relation is
enforced. We call this Referential integrity (RI).

When you use RI, then the data engine will NOT let you add a invoice without
first having a customer.

You can also have the system delete all customer invoices for you by
deleting JUST the customer record (this done when you select
cascade deletes). That means by deleing one customer record, then
100's of invoices that belong to the customer will also be automatically
deleted. (I should note that cascade deletes are NOT shown on the
ER diagram).

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced. Note how some join
lines have a arrow head (left joins), and some do not!

http://www.attcanada.net/%7ekallal.m...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 (this makes sense) Now how full RI is enforced, and you can see the
side ways 8
"omega" sign AND THE ARROW HEAD. So, that arrow head means that
child records are optional.

If you look at tblPayments to tblHowpaid, note Note how no "1", or omega
sign exists In this case, the line was only drawn for developers to see
that
tblHowPaid is just a simple list of values that I use for a combo box. And,
this list of values is used in tblPayments.

Note however that no RI or relation is enforced. In fact, the line does
NOTHING in this case, EXCEPT inform you the reader that tblHowPaid is used
with tblPayments. (in looking back at this application, I fact very well
could have, and should have enforced relations even for these simple tables
used for combo boxes).

Also, take note of tables that MUST have a child records can also clearly be
seen.
(in the one to many relationship, I want to assume at least ONE child record
exists).

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 . This
again makes sense. Hence, if I create a booking, then I MUST add people
to the booking (I don't allow a booking without people!). Note however
this arrow head is just like the above tblpayments line in that you as
a developer can break this rule..but it would be a bad idea since my
code and the original developers assumed that when you make a booking
that people will be added. So, the RI does NOT force you to add
a child record(s), but the diagram does convey assumptions made
about the data design anyway.

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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Just a nitpick, but that's not an omega sign, but the
symbol for infinity. Omega kinda looks like a rounded
horseshoe.

-----Original Message-----
The 1 means "one" and the line is drawn to another table,
and that is a the
"many" side.

One customer can have "many" invoices for example. This
is a classic one to
many relationship.

the "8" type symbol is the omega sign, and simply means
that the relation is
enforced. We call this Referential integrity (RI).

"nitpicker" wrote in message
...
Just a nitpick, but that's not an omega sign, but the
symbol for infinity. Omega kinda looks like a rounded
horseshoe.


Yes, thank you kindly! (and..not that is not a nitpick...I am simply 100%
dead wrong on the symbol!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

"Albert D. Kallal" wrote:

"nitpicker" wrote in message
...
Just a nitpick, but that's not an omega sign, but the
symbol for infinity. Omega kinda looks like a rounded
horseshoe.


Yes, thank you kindly! (and..not that is not a nitpick...I am simply 100%
dead wrong on the symbol!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Thank you all for your replies. All were most helpful. Special thanks for Albert Kallal a true gentleman who admits to a "nitpicking" error. The word needs more people like you Al.

Thanks.

"Albert D. Kallal" wrote:

"nitpicker" wrote in message
...
Just a nitpick, but that's not an omega sign, but the
symbol for infinity. Omega kinda looks like a rounded
horseshoe.


Yes, thank you kindly! (and..not that is not a nitpick...I am simply 100%
dead wrong on the symbol!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Thank you all for your replies. All were most helpful. Special thanks for Albert Kallal a true gentleman who admits to a "nitpicking" error. The word needs more people like you Al.

Thanks.

"Albert D. Kallal" wrote:

"nitpicker" wrote in message
...
Just a nitpick, but that's not an omega sign, but the
symbol for infinity. Omega kinda looks like a rounded
horseshoe.


Yes, thank you kindly! (and..not that is not a nitpick...I am simply 100%
dead wrong on the symbol!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Thanks to all of you especially Albert Kallal. Only a true gentleman can admit to a "nitpicking" mistake. The world needs more like you Al.

"Albert D. Kallal" wrote:

"nitpicker" wrote in message
...
Just a nitpick, but that's not an omega sign, but the
symbol for infinity. Omega kinda looks like a rounded
horseshoe.


Yes, thank you kindly! (and..not that is not a nitpick...I am simply 100%
dead wrong on the symbol!).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

Thanks to all of you especially Albert Kallal. Only a true gentleman can admit to a "nitpicking" mistake. The world needs more like you Al.