Creating Database Relationships

In the last database post I said I’d talk about creating relationships in the next one. So, that’s what I’m going to do. But first I thought I’d link to the official LibreOffice documentation for anyone who wants to look anything up.

LibreOffice Documentation

If you scroll down the page, you’ll find the LibreOffice Base Handbook and the LibreOffice Base Guide.

I only looked at selected parts of some of these pdfs, because there were things I already knew about creating databases. I didn’t find these as useful as a couple of the videos I watched (I’ll link to them at a later stage), but if you’ve never created a database before, you’d be well advised to do so.

Relationships

We’ve already partly defined the relationships in this database, but now we need to confirm the structure.

Remember that screen cap I put up in my first post when I was working through how the structure was going to be? It’s nothing like that now. In fact, it’s not even like this one, because I’ve added a new table today, but we won’t worry about that (it’s just a stand-alone table, more like a mini spreadsheet in my database!).

This is the current structure.

New Relationship File

What we’re going to do is put all those interconnecting lines between the tables. If you zoom in on the picture, you’ll be able to see that the lines either have a 1 or an n at the end of them. The 1, obviously, indicates the ‘one’ side of the relationship and the n indicates the ‘many’.

(NB: Don’t focus in too much on the individual table elements. Some of those may well get deleted yet as I put in data and I find I don’t need them, and there may be a few rogue ID fields still lurking from when I was planning how it was going to be structured.)

In the main database window, click on the Tools menu and select Relationships. That will bring up the window above, but completely blank. Underneath the menus, you’ll see a little icon with a + sign on it. If you hover over it you’ll see it’s the Add Table button. Click on it and you’ll get a list of the tables in your database. Click on each one and select Add.

The tables will appear on the screen in a random pattern.

The first thing I would suggest you do is to move them around so that the ones that have relationships with each other are next to each other. You’ll probably find there is a starting point (for me that’s the Series table) that you can follow along. There will be places where it gets more complicated, especially if you’ve got a number of many-to-many relationships, but get it as close as you can.

Once the tables are in place (and don’t forget, you can move them around later if you want to), you need to join them. The easiest way to do this is to use your mouse. Click on the ID at the ‘one’ end of the relationship and drag over to the ‘many’ corresponding ID. A line should then appear between the two IDs as shown above. Check that the ‘one’ and ‘many’ symbols are the right way around.

At this point, sometimes another window comes up, sometimes it doesn’t, but you can access it by double-clicking on the relevant line. This is that window.

Cascade

You’ll see that it indicates the two IDs and the tables they’re in. Now, I’ve seen different advice about this next bit, but either click on Update Cascade (under Update Options) and Delete Cascade (under Delete Options), or click on Update Cascade and leave the Delete Options unchanged.

As I understand it, allowing update and delete cascades allows for information that is linked in tables to be updated accordingly. However, in some situations, you may not want information to be deleted in all other tables when you delete a record in one table – this is when you would not select Delete Cascade. This would be relevant in companies, for example, where you needed to keep an electronic ‘paper trail’ of what has happened in the past. Please, if you are in doubt about which you should choose, read up on the LibreOffice info and make your own choice (and do some internet searching as well for what others consider best practice for your situation). I cannot advise you on which is the best to choose for your situation.

With my database, it’s not exactly life or death if I delete something, so I’ve gone for the Delete Cascade option.

One word of warning. Try not to make your relationships too complex. I would have loved to be able to link the Character table to other tables in my database as well, but I was ending up with a kind of circular relationship, which would never have worked. If your relationship screen looks too much like spaghetti junction, then you’ve probably gone too far. Try rethinking your tables. Maybe you can split information out further, or adjust your expectations a little.

Next time I’ll start you off with Forms. They’re what you use for entering your data (because entering it directly into the tables would be just as awkward as using a spreadsheet!).

See you then.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s