Neo Base: Relationship

[valterb]

I'm playing with database these days to see if I can learn something.
I started a new database. File -> New -> Database. Click on Finish and save the database as prova.odb
Then I created 4 tables in design view:
- books (id_book, integer, autovalue yes, primarykey; title, text; author, text; language, text; type, text)
- author (author, text, primarykey)
- language (language, text, primarykey)
- type (type, text, primarykey).

Since I want to choose the name of the author, the language and the type from a close list I created these 3 tables with just one field. Now I want to make relationships among entries in books table and the three extra tablers .

Well, if I do just one relationship and than save everithing goes OK, but if I do the 3 reletionships and then I save Neo crashes.
Everything works as expected with OOo 2.0.3. I have also tryed to open the file done with OOo 2.0.3 in NeoOffice (I use NO 2.0 beta 2 patch 2)) and I can open the Reletionship window. I see 3 reletionships but when I close the window Neo crashes again!!!

I have uploaded the file here and issue a bug (#1660).

Valter

[amayze]

I don't think this will help with your crashing problem, but as a general rule every table should have at least two fields - a primary key (integer, auto increment) and some other data. Having a primary key that is text is prone to typos. (The structure you propose is actually a flat file data-base with restricted data entry)

So I would implement your tables as follows:

books:
book_id - primary key, integer, autovalue yes
title - text
author_id - integer
language_id - integer
type_id - integer

authors:
author_id - primary key, integer, autovalue yes
author - text

languages;
language_id - primary key, integer, autovalue yes
language - text

types;
type_id - primary key, integer, autovalue yes
type - text

Then you set up relationships between books.author_id and authors.author_id, etc.

You may have to do a bit of work to display the author name, language and type in a drop down list for data entry, but with the right querey extracting the data is trivial. Also if you realise later that you spelt an author's name wrongly you just update it in the author table and it will appear correctly everywhere.

Also it lets you do more things later, e.g. you might want to know the dates of your authors, so you could add fields for DoB and DoD to the authors table and write appropriate queries that would, say list all books written by authors who were born between 1950 and 1969.

HtH

Andy

[valterb]

Everything you say Andy is correct, but I was just doing something I've found in an italian guide to OOo Base. And the real problem is that this in OOo 2.0.3 works: I checked this with Mac X11 version and Linux version (while the author of the tutorial use Windows).

Valter

[valterb]

Now it start to be not surprising , but after 10 hours Patrick has fixed the bug!!!

So this time how many of these 10 hours did you sleep, Patrick?

Valter