How do I Store the table of contents of articles in MySQL? (not the article itself)

Question

The table of Content has a unknown length, and I have a lot of articles. I want to store the table of content in my MySQL. I don't know if I should use BLOB or not, because then I won't be able to alter them through SQL queries.

I will need to be able to get the table of contents into an array object back into Java.

Are you looking for a [One to Many](http://stackoverflow.com/a/12402535/823393) relationship between article and TOC? — OldCurmudgeon, Aug 15 '13 at 08:09

score 2 · Answer 1 · answered Aug 15 '13 at 07:49

2

I guess that you would want to use Text not a blob as it will handle text not binary right. The Text fields are like a Varchar but with all the size you need.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

answered Aug 15 '13 at 07:49

Scary Wombat

44,617
6
35
64

I think TEXT type is deprecated. – Juned Ahsan Aug 15 '13 at 07:50
I know, but it's a table of content for the article not the article it self. – Louis Hong Aug 15 '13 at 07:50
@JunedAhsan Really? I Google a little bit nothing about it came up – Louis Hong Aug 15 '13 at 07:53
You're getting the question wrong, it's not for the articles them selves, it's for the articles table of content! I'm storing table of contents. I can't even down vote this anser, dammit! – Louis Hong Aug 15 '13 at 07:55
as user2310289 said use a TEXT column BLOB is for binary data. – Frank Olschewski Aug 15 '13 at 07:58
Sorry why would you want to down vote this answer. I am assuming that the table of contents is some text (you do not previously mention). The Text field like a Varchar will store the text. – Scary Wombat Aug 15 '13 at 07:58
I need to get the table of content into an array object in java. – Louis Hong Aug 15 '13 at 07:59
Is your question that you want to know how to serialize your table of content? – Frank Olschewski Aug 15 '13 at 08:00
Oh do you now. Did not mention that before did you. Why do you need to convert text into an array of objects in Java? – Scary Wombat Aug 15 '13 at 08:02
You're not getting the question. – Louis Hong Aug 15 '13 at 08:13
@Louie - be fair. The real problem was that the Question was impossibly unclear ... until you edited it. – Stephen C Aug 15 '13 at 08:14
@StephenC It's not a matter of fact of fairness, it's not the right answer and it's being up voted, it's needs to be brought down so it won't be misleading – Louis Hong Aug 15 '13 at 08:16
@Louie - No fairness >>is<< important. You want people to spend time answering your Questions ... don't you? Changing the ground rules (by changing your question) and then campaigning for people to down-vote answers is (IMO) unfair. – Stephen C Aug 15 '13 at 08:56
@StephenC I'm sorry, it's my fault for not describing the question clearly. – Louis Hong Aug 15 '13 at 09:30

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

OK, so your table-of-contents is actually an array of strings.

Your choices are:

Create a new table so that each array element is a row in the table. You will probably need columns for external key and each string's index within its table-of-contents. This gives you a proper One-to-Many relationship between the ToC and its elements (or the Article and the ToC elements ... depending on how you chose to model it.) (Borrowed from @oldCurmudgeon's comment.)
Use Java serialization to serialize the String[] and use a BLOB column type.
Create (or choose) a custom encoding scheme to turn the String[] into a textual representation and store it in a TEXT column.

If you want to be able to query and/or update the individual table-of-content elements, then the first scheme is the way to go.

@ppeterka points out (correctly) that it would be good for you to read up on the topic of Database Normalization ... which is about designing schemas to reduce duplication of information. In this case, it will help you weigh up the alternative ways of turning an abstract model (e.g. a "One to Many relationship") into a database schema.

edited May 23 '17 at 11:56

Community

1
1

answered Aug 15 '13 at 08:09

Stephen C

698,415
94
811
1,216

For the first option, does it mean I'll need an table for each array? – Louis Hong Aug 15 '13 at 08:10
No. You need one table for to represent all of the ToC arrays, and you use the other columns to determine which ToC each row belongs to. This is normal SQL database stuff ... – Stephen C Aug 15 '13 at 08:12
It would also be wise to add a brief notion of [database normalization](https://en.wikipedia.org/wiki/Database_normalization), as it might be useful for the OP, and anybody finding this page in the long term. – ppeterka Aug 15 '13 at 08:54
@ppeterka - hmmm, maybe. But I don't think this question is particularly searchable. – Stephen C Aug 15 '13 at 09:00
@StephenC I've been surprised many times what and how people search the intartubez - who knows? :) – ppeterka Aug 15 '13 at 09:02

score -1 · Answer 3 · edited May 23 '17 at 11:49

-1

See what-is-object-serialization for serialization and deserialization of Objects. Then store the serialized data bytes in a BLOB column in SQL.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 15 '13 at 08:06

Frank Olschewski

500
4
9

1

Yea ... but putting serialized objects into a database is usually a bad idea. – Stephen C Aug 15 '13 at 08:12

How do I Store the table of contents of articles in MySQL? (not the article itself)

3 Answers3