Multi-dimension tables

[Joakal]

Similar to Arrays, I would like to know if it's possible to find multi-dimensional tables. If so, how? I've read many database/base documents and my search results keep getting mixed up with OpenOffice Basic's arrays.

Something like a ToyBox DB where it's possible a user might pick the same toy more than once;

Code: Select all

ID: 43
Name: John
Pass: tkm2
Toy1: 1
Duration1: 243 [minutes]
Toy2: 5
Duration2: 191
..
..
With no limits to amount of toys.

Code: Select all

ID: 44
Name: Sally
Pass: 4ni3
Toy1: 5
Duration1: 94
Toy2: Null
Duration2: Null
..
..

Second linked table Toys;

Code: Select all

ID: 1
ToyName: Stormtrooper
ID: 5
ToyName: Mercedes

I'm new to OpenOffice Base (and to multi-dimensional tables) and not an efficient-crazy parent but looking to make my databases more efficient to develop.

Thanks for any help!

[squenson]

You should use three tables:
1) Kids -- Number (Primary Key), FirstName (indexed), Pass, etc.
2) Toys -- Number (Primary Key), Name, Description, etc.
3) Toys_by_Kids -- PKey (Primary Key, Integer with Autovalue Yes), Kid_Number (indexed), Toy_Number (indexed), Duration, Date, etc.

This way, you can have unlimited toys per kid.

Edit: Following Kabing's comment, I added a Primary Key to the third table

[kabing]

<-- No need for Primary Key on this table

Really? won't that prevent the user from editing or adding data to the table--at least if they are using the built-in HSQL database engine?

kabing

[squenson]

You are absolutely right, the silly HSQL engine does not like tables without a Primary Key, something beyond my understanding by the way...

[Joakal]

Thanks, but my primary question was if Multi-dimensional tables was available within this software. Is it a myth? Heh.

[keme]

Thanks, but my primary question was if Multi-dimensional tables was available within this software. Is it a myth? Heh.

Tables analogous to a multi dimensional array (as your original posting suggests): No, there is not.
Multiple dimensions: yes there are.

Fundamentals:
In an array, you specify a position (a set of "coordinates") to extract a specific item (which may consist of several distinct data fields).
In a relational database, Relations (tables) use attributes (in data fields) to describe entities (each entity description is a record, i.e. a row in the table). The definition of relational databases states that a record can not be referenced by position, but only by content. With this in mind, each attribute can then be seen as "a dimension" of the entity.
A set of attributes (often one single attribute) that consistently will define one single record, is a candidate key. There can be multiple candidate keys in a relation.
Sometimes one of those "natural" candidate keys will be used as the primary key. At other times it is more practical to insert an "artificial" primary key (normally an autogenerated sequence number).
The relations can be interconnected through relationships, normally using the primary key for connecting. Note that many applications (including MS Access, and to some extent OOo Base) confuse the terms Relation and Relationship.

Any set of attributes can be used to select records. If the selection attributes constitute a candidate key (or a superset thereof), you are certain to retrieve at most one record. If not, the request may yield multiple records.

For the purpose of analogy between arrays and relations, it may be better to think more in the direction of the "associative arrays" of some programming languages, although they're not exactly equivalent.