Help to understand relationships in tables? LibreOffice BASE [closed]

edit retag reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-14 21:40:24.943056

Sort by » oldest newest most voted

Here is another tutorial. Read from page 3 about relations and the example in there.

It all depends what you want to do if you need relations or not.

more

Hi, thanks for the replies. I'll try to assimilate and understand all that bunch of info. Just a little doubt... when I create a relationship, I do that to obtain a certain result in a query or a report, right? Basically I cannot do anything with tables when the relationships are applied... I can set up relationships, but entering data inside the tables will continue being the same... the aim of relationships is to combine databases to obtain certain results from queries and report, right? Just that doubt. Cheers

more

I updated my answer to take up the request for confirmation in this post.

( 2013-11-23 16:16:12 +0200 )edit

Has Google (or DuckDuckGo) been any help yet? ;)

Here's one example of an explanation of table relationships in relational databases. And Microsoft provides some help related to Access which transfers to Base. There are, of course, loads of online tutorials about this.

Also, do check out Chapter 3 of the LIbO Base Handbook which covers this info; that should help with Base itself.

... the aim of relationships is to combine databases to obtain certain results from queries and report, right?

No, not quite.

The aim of relationships is to ensure the integrity of the data stored in related tables of a single database.

It's important to keep the terminology straight. Think of it this way (the analogy is limited, and will only help so far, though!). Your database is like a bookcase, your tables are like the shelves, and the fields in the tables like the books, magazines (different types) on the shelves.

• One bookcase has some/many shelves, each shelf has numbers of books (which contain your information).
• One database has some/many tables, each table has numbers of fields (which contain your information).

Back to relations, then. In a relational database, the "1:many", "1:1", etc., relations connect fields in different tables.

An example. Let's stick with the library analogy. Say we have a database for our library collection. Two of our tables would be "Authors" and "Books":

• Table:Authors / Fields: AuthorID, Author_LastName, Author_FirstName, Author_DoB
• Table:Books / Fields: BookID, Title, PubDate, Publisher, Location

(We could have another for publishers. After reading this example, you could work out how a "Publishers" table would be "related" to "Authors", "Books".) How to connect the data between these tables in our "Library" database?

One way would be to add an "AuthorID" field to the "Books" table. (In databases, one rule you try to work with: never enter the same data more than once!) (We'll let the database worry about how the numbers of these ID fields work: it's designed to do that.)

So, one author could write many books, but any title will only have a single author (for the sake of this example). Or, in abstract:

Authors.AuthorID : 1 ... ∞ : Books.AuthorID

That is, any one instance of an AuthorID field from the "Authors" table, could have many (∞) corresponding entries in the AuthorID field of the "Books" table. This one-to-many relationship definition ensures this connection between tables in your database links the information correctly. For example, one author, "Charles Dickens", connects to many books: Great Expectations, and Bleak House, and Oliver Twist; but the book entry Oliver Twist ONLY connects to one author, "Charles Dickens".

Here's another database tutorial site with some practical explanations and examples.

Hope that helps.

more

Hi, I think the example you put is very clear, exactly, database of authors related to database of books is a good example of what a relationship one-to-many could be, because, yes, an author can have many books. But now what I do with this? You said the aim is to ensure data integrity, ok, but outside a query or a report? I don't see any practical application for this? every table would have their own data, so why to relate'em if I don't get any kind of advantage when doing queries?

( 2013-11-24 04:44:55 +0200 )edit

I mean every table contain its own data, authors contain authors, books contain books, basically you are relating the AuthorID field with BookID field, both I assume must have the same data type, let's say INTEGER, but, okay now they are related and what? If I print all the registers from the Books table I won't see the corresponding authors, every table have its own data and that's all... so if I am relating them, must be (I assume) because I should be able to get something when doing queries?

( 2013-11-24 04:49:04 +0200 )edit

@mrmister: in the first comment you write: "...database of authors related to database of books is a good example of what a relationship one-to-many could be, because, yes, an author can have many books." No! There is ONE database; it has different tables - a "book" table, and an "author" table. You keep this info in different tables in the SAME database. To link an "author" to a "book" requires defining the relationship" between the connected **fields in those tables....

( 2013-11-24 14:33:47 +0200 )edit

In comment 2: "basically you are relating the AuthorID field with BookID field". No! We're relating the AuthorID field in the "Author" table ("primary key"), with the AuthorID field in the "Book" table (now a "foreign key"). In a query, an Author (by ID) look up geta all their Books (titles). A comment thread isn't best place for this! :) Best advice: get a sample database and see how it works. Check sample DB and sample SQL Q&A's.

( 2013-11-24 14:44:37 +0200 )edit

Getting a sample DB it's okay, but it's better not dealing right now with SQL too complex for this question. Okay, thanks for explaining the relationships, ok, understood. But again, now what? what I do with that? I have the relationships applied, and what? If you could put me an easy example of what I could be able to do with this when performing a query, I would be greateful, It'd be more easy to understand.

( 2013-11-24 23:01:49 +0200 )edit

I mean, we have 2 tables: Authors and Books. Let's imagine we don't have a relationship, ok? I can perform a query to the table Authors and gather information from that table. Ok. I can do the same with the table Books. Ok. But now, let's imagine we apply the relationship you suggest, ok? We have now the relationship created, and now, what can I do with this relationship when doing queries? may I obtain more data? or combined data? or what? if you could put me an easy example, please?

( 2013-11-24 23:04:19 +0200 )edit