Hello,
I am starting to use BASE but I have doubts about why should I create relationships. I have seen tutorials and Youtube videos, and all people explain how to create relationships. Okay, I know what should I press, what should I click and so on⌠but what I donât understand is WHY?
Why should I create a relationship among several tables? what benefits I do get with this?
There are 3 kind of relationships:
One to Many, Many to One, Many to ManyâŚ
I would need that someone please, put me several practical examples, please, EASY, that I can understand about why should I do this?
Also, I would need please, you explain me what happen after I create a relationship? I mean, when I create a relationship in the table design view mode I wonât get any practical use or advantage about this?
So? when I create a relationship I would get practical use of this, when doing forms? or queries? or reports?
If you could specify me a bit please?
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.
@mrmister - in your answer you write:
⌠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.
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?
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?
@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âŚ
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.
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.
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?
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.
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
I updated my answer to take up the request for confirmation in this post.