MySQL - Second level records

[TGecho]

I'm trying to design a database for the first time. I've got a field for an article's contents, but I want to be able to define multiple sets of content for each article (multiple pages). I don't want any restrictions on number of pages.

So the ideal option seemed to be storing a PHP array containing each set in the content field. However, this doesn't seem to be possible.

Are there any standard approaches to dealing with this sort of thing?

[surfgatinho]

I'm not really sure I understand the question but as much of the storage and data manipulation should be done on the database as possible.
If you want multiple content for a single entitiy you will need 3 tables - a table which holds details about the entity eg ID, name , DOB - a table that holds the content - and a table that links the two together.

[TGecho]

>> data manipulation should be done on the database as possible

That's what I've just been reading.

>> If you want multiple content for a single entitiy you will need 3 tables

That's what I was afraid of. So I guess if there's no way of knowing how many "pages" there will be in an "article", I'll have to make an educated guess about the maximum amount. If it doesn't fill all the spaces, they'll just be left blank. Maybe include a way for the system to add columns if needed...

Thanks

[killroy]

Try this:

[ARTICLES]
article_ID
title
topic
etc

[ARTICLE_PAGES]
page_id
pagenumber
pagebody
article_ID

then you can do this:

SELECT title,COUNT(1)AS pagenumber
FROM ARTICLES,ARTICLE_PAGES
WHERE ARTICLES.=ARTICLE_PAGES.article_ID
ORDER BY title;

SN

[coopster]

What is going to define a page? And why do you need to know how many pages there are per article/content?

[lorax]

I think I'd make 3 tables:

[articles]
id
title
topic
etc

[copy]
cid
copy

[full_article]
id
cid

The article table contains the info regarding the article as it pertains to describing the article. The copy table contains copy segments. Break up the full content as many times as you'd like. The full_article table is where you blend the two together to build a complete article. It would look like:

[full_article]
id ¦ cid
1 ¦ 01
1 ¦ 02
1 ¦ 109

This allows for the most flexibility based on what you're asking for.

[Storyteller]

TGecho, in this case multiple tables seem to be overkill. You can easily deal with PHP arrays stored in single fields using PHP's

serialize

and

unserialize

functions.

A more elegant way would be to use a page-breaking delimiter in the text and use PHP's

split

function to break the page into an array once fetched.

[TGecho]

Thanks for all the ideas guys. I'm still trying to get up to speed on basic techniques and the like, so this gives me a great starting point.

[killroy]

lorax, what you described is a many to many join. This is ONLY neccessary if a page could apear in more then one article. Since that is clearly not the case, it is enough to associate each page with it's article in the page table. This is a so called on-to-many connection. No intermediary link table is needed.

SN

[lorax]

killroy,
Correct and that was my intention. It would allow for a chunk of copy to be used in any number of articles.

>> Since that is clearly not the case

Not to me it wasn't. ;)