Help conceptualizing a database with fields containing arbitrary-length arrays of data

[wf]

I have no experience with databases (yet), although I have read the Libreoffice Base tutorial and documentation, and am having troubling figuring out how to conceptualize a database to address my problem.

I am faced with the following situation (much simplified): imagine having multiple copies of a book, printed with different typefaces and other unique identifying information, that have been chopped up and intermingled. Each of the pieces is labeled with a unique number, and the range of chapters and pages for each piece has been identified. Each individual piece can contain multiple ranges of chapters and pages. It is also known which pieces join together to form a copy. From that data, I wish to generate the number of existing copies, the content of each copy, and some data about it (e.g., number of pages covered). The basic data for each piece is easy to envision:

Piece Number - Start Chapter - Start Page - End Chapter - End Page - Links to Other Pieces.

At the outset I am facing two issues:

1. What is the best way to enter multiple start chapter/start page/end chapter/end page ranges for the same piece? There can be an arbitrary number of such ranges for each piece. Given that there is other data for the piece that is consistent over the different ranges (e.g., typeface, font size), it doesn’t seem to make sense to have multiple entries for each piece for each range repeating all of the identical data.

2. I really can’t figure out how to manage the “links to other pieces” field, which can contain an arbitrary number of links. Ideally, I would like to be able to enter the number of other pieces as a range/array of comma-delimited numbers, from which I can then later generate a table of copies, numbered in descending order by amount of the book preserved in each one.

Any and all tips and pointers would be much appreciated. Thank you in advance.

[ajlittoz]

I’d recommend you read tutorials about SQL.

As always, computers don’t like data structures with arbitrary number of components. SQL offers an “elegant” way to implement 1-to-N relationship.

The basic structure in SQL is TABLE which describes a record (made of various fields). When one of the fields is an array of indeterminate size, the trick is to create another TABLE describing only one element of the array. You add a KEY which is a backlink to the original TABLE. You tell you already have unique numbers to identify your data, use it: in the original TABLE and in the “array” TABLE.

The elements of the array are inserted in the DB one by one. When you want to retrieve the array contents, you execute an SQL request with the key corresponding to the effective array. If the array contents is not the data itself but some “locator” to other TABLEs, you submit another SQL request.

Experiment and practice on simple examples before tackling your real problem.