ODBC How to store a variable length array of strings?

Question

Y.A.N.Q. (yet another n00b question).

I have managed to design my db, defining tables & columns. I have even got code to access the db, yay!

Now, one bit that I haven't yet gotten to in the "MySql weekend crash course".

Let's say that I want to store something like "notes" which is a TStringList sort of thing. A list of strings which might be a different number of strings every time I submit a new set of data to the db.

How should I best handle that? Should I just concatenate the strings into one when writing and split them when reading back out? That way I would only need one TEXT column.

Or is there a preferred way?

Answer 1

It depends on your application.. One option is having two tables, Notebook and Note, and a 1-many relationship between them. So one notebook can have many notes. Each note consists of a string, and maybe a date created, or something else to use for ordering. Of course, Notebook could really be any table.

EDIT: Yes, I think you're on the right track. It could be:

Notebook:
  id INTEGER PRIMARY KEY AUTO_INCREMENT

Note:
  id INTEGER PRIMARY KEY AUTO_INCREMENT
  notebook_id INTEGER
  note_text VARCHAR(1024)

You could rename and add fields to each. For example, Notebook could be anything that needs notes attached (e.g. Project), and it could have other project attributes (e.g. a deadline). Note could have a date if you want to be able to order them other than by id.