Tom,
Yes. I confirmed that right-clicking on a join line does allow deleting
or editing.
If I understand you correctly, no, relationship definitions should not
be part of the back-end. The table relationship is defined for the
back-end by the front-end through the SQL statements. For example, I
have a table of suppliers, with names and addresses and other contact
information. This table is related to almost all of my database (main)
tables. Additionally, each "main" table has its own set of table
relationships with other (sub?) tables, most of which are for selecting
options with a join. Each record of these option tables contains a
primary key and a text field for the option. For example, I have a
table of statuses for the item in the main table record. An integer
foreign key in the main table contains a primary key value corresponding
to the text element of the statuses table record. That way, I am only
storing an integer (key value) in the main table, rather than the option
text, and with no repeated option text. It also standardizes the option
texts. All of these multiple relationships must be defined by me - ergo
it needs to be in the front-end.
Hope this helps clarify this.
Girvin Herr
Tom Davies wrote:
Hi :)
Can you right-click on a relationship's join-line and edit it's properties? Shouldn't the relationships be part of the back-end rather than defined in the front-end?
Regards from
Tom :)
________________________________
From: Dan Lewis <elderdanle...@gmail.com>
To: users@global.libreoffice.org
Sent: Wednesday, 9 January 2013, 4:14
Subject: Re: [libreoffice-users] Re: Base scenario
Comment inline below.
--Dan
On 01/08/2013 07:07 PM, Girvin R. Herr wrote:
Ian,
Have you actually drawn any relationships? Base will not do that for you.
Just adding the tables in the 'Relationships Window' will not create the
relationships automagically. You must click and hold on the one table element
(remote key) and drag over to the related table's element (primary key), then
release the mouse button. Base will then draw a line between the two. Note,
the order of the drag is important. It determines the type of join. Joins are
confusing to me too, so I can't help much there. I had to experiment with the
direction to get it to work right. I think it was remote key to primary key,
but I am not sure of that any more.
Warning! The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record
will be discarded and not show up in your result set. No warnings, no errors. Data records will just be missing.
IMHO, this is stupid (my mantra is: "thou shall not lose data"), but that is how the SQL language was set up.
So, make sure any joined data elements in all of your table records are not NULL. Note that NULL is not zero (0) and
vice-versa! NULL means that there is no data in the record element. I use a lot of remote keys in my database main
tables that point to primary keys (options) in other tables. In those other tables, I have made it a point to make the
data elements of the first record to be "-", which is my equivalent of unknown, just to have something to
select that is not NULL. You could probably use a blank (" "), but I prefer seeing the "-" in
forms and reports. Most times in reports, it is hard
to see anyway. Seeing the "-" tells me the field is not NULL.
Hope this helps.
Girvin Herr
These statements about joins do not seem to be quite correct. What you
are describing is an Inner Join: you will only see the rows of data in which
both the foreign (remote) key and the primary key have a value.
Suppose we have two tables A and B and that the foreign (remote) key is in
table A and the primary key is in table B.
Example 1: table A Left Outer Join table B. The output (result set) for
this contains all the fields in table A and their values on the left side of
the combined table. The right side contains all the fields in Table B. The rows
in which the primary key value matches the foreign key value, data from both
table appear in the output. However, where there is no primary key value in
table B that matches the foreign key value in table A, all the fields from
table B for that row will be NULL.
Example 2: table A Right Outer Join table B. The output for this contains
all the fields in table B and their values on the right side. For each output
row in which the foreign key does not have a value that matches any value of
the primary key, the fields in the left side of it will be NULL.
Example 3: table A Cross Join table B. This is also referred to as a
Cartesian Product. In this case, each row of table A is joined to all the rows
of table B. This contains all of the possible combinations of combining both
tables. Usually, some rows of the output will have the table A fields all
showing NULL while others will have the fields of table B showing all NULL.
Ian Whitfield wrote:
Hi All
Re - My previous post.... Have been doing some Googling etc and found the
'Relationships Window' for setting Relationships.
I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting
lines or set any Relationships!!??
Is this another "Gotcha" of using MySQL and Base together? As it does _NOT_
seem to work at all!!
I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55
IanW
Pretoria RSA.
-- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted