Creating Database Tables

As promised, today I’m going to show you how to create your tables in LibreOffice. I was going to cover the final defining relationships stage, but this post is already too long. So, I’ll leave that for next time. I’ll try to explain it in layman’s terms (that’s all I know really, although even I can get a bit technical sometimes). Also, I’ve made the graphics big, because there’s nothing I hate more than not being able to read them when they’re too small.

Hopefully, if you read my first post, you’ve spent the last week thinking about the data for your tables. Now, we’re going to create those tables. I’m assuming you’ve opened up LibreOffice, created your database document and given it a name. If you haven’t done so, do this now, and when it asks you if you want to register it, say yes. It’s only registering it within the application so that you can share info between applications (not something I’ll be doing, but you might), not sending your data off somewhere else across the web.

Right, first things first. The Tables are where the data is stored, so what you are actually doing here is creating data fields.

Create Table

Click on the tables icon on the left (here you can see all my tables – and some I’m not using because it was too complex, must remember to delete those [or maybe not, I don’t want to risk doing anything stupid!])

You could use the wizard to create a table, but I didn’t bother with that so can’t advise you on how easy it is to use. However, I think creating your tables is easy enough anyway, so I’ll show you a very simple one of mine and how you create the fields.

Integer

This is my Series Table. It only has two fields – the ID field and the Series Title field. Every table needs to have what they call a primary key. The primary key is used to define the records (eg, record 1, record 2, etc – although it starts with 0, which I find odd, but I sure there’s some techincal reason for that). I’ve always put in special ID fields, and I think that’s the way most people do it.

The ID field needs to be defined as an INTEGER in the pull-down list in the second column and needs to be defined under the AutoValue field at the bottom, as a Yes. This defines it as the primary key (the little key symbol gives it away), but you can also do it another way by right-mouse clicking next to the first column and bringing up a small menu, as below.

Primary key

All the other fields can be defined as you wish by using the pull-down menu in the second column. You can tab between the fields, or just click with your mouse.

Other table fields

As you can see, there are text fields, date fields, yes/no fields, etc. If you are putting in text fields, make sure they are long enough for the data you want to input. You’ll find the Length box in the bottom section of the window. And before you ask, no, I don’t know what all of them mean.

If you are going to link a table with another, you will need to put in a linking ID field.

ID field in other table

As you can see, in the Book Table, I have added the Series_ID field, because I want to link the two as a one-to-many relationship. The ID field in this table needs to be selected as an INTEGER, but not as a primary key, so don’t make it an AutoValue of Yes.

If you want to make a many-to-many relationship, you need to create an extra table to link the two main tables (once again, I’m not completely up on the technical side, but I think this is because it needs to keep the data in a separate place because of the multiple relationships involved).

Relationship Table

The only thing you need in this table is the ID fields from the two tables you want to link. As you can see, both are primary keys, but don’t change the AutoValue field. Highlight both rows and right mouse click to bring up that little menu where you can select primary key (see above).

That’s all for today, folks. See you soon, when we will define all the relationships between the tables.

 

 

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