ryanking1809 — 2013-03-16T00:29:20-04:00 — #1
I'm a bit new to database design and wondering what I should store in tables and what should stored with a markup-language in a text field that could then later be processed by the server. My tendency is to make a table for essentially everything but I've noticed many applications like say mediaWiki use markup to store their data in a large text field. Does this save time by limiting the amount of Database Queries and the size of Tables? Or does the server side processing take up more time?
My page structure is as follows:
[I]Page[/I] has many [I]Sections[/I]
[I]Section[/I] has many [I]Paragraphs[/I]
[I]Section[/I] has many [I]Images[/I]
[I]Section[/I] has many [I]Page_Links[/I]
I like to design the database in mind of the possibility of reaching 100 000+ pages. Noting that, the last 3 tables could get very large very quickly. Would it be more efficient to store all that information in markup in the section table then processing the information using server-side code? Or am I underestimating the speed of database queries and this is more efficient as the data there ready to be manipulated? At which point should I stop making tables and use markup instead?
cydewaze — 2013-03-16T12:31:27-04:00 — #2
So I guess this is like a CMS you're building?
For our site, we store the page content in individual pages, and just use the database for page titles, contact info, and other details about the page, then use includes for the header and footer. Which header and footer is used (along with which stylesheet) is determined by the directory structure.
But I have a unique situation in that I didn't have a decent production database available (my office used Access up into this year) so I felt I had to limit what the database did. If I'd had a decent database when we started, I might have set things up differently.
oddz — 2013-03-19T19:13:41-04:00 — #3
I don't really understand why you would need a separate table to break-up paragraphs. In most cases that information is just stored in a text column. What is the benefit of breaking up each paragraph of a page into a different table row? For me there is none but you might have some weird idea in mind…
Besides content of pages within a content management system are rarely only defined by paragraphs. In most cases there will probably be some type of editor that allows some level of control over style, layout, etc without fully exposing HTML vulnerable to XSS. The content of a page will most likely and practically be HTML, bbcode or something in between – not just raw text, as that is quite limiting.
Most content management systems these days use some type of editor for content entry. A good example is aloha editor which seems to be all the rage these days. If you were to use an editor like that is would not be very practical to define content within the context of paragraphs.
ryanking1809 — 2013-03-23T06:31:25-04:00 — #4
Yeah you're right I should just have it stored as a text column. Coming from the perspective of the page object in the application it makes sense to separate the paragraphs, images and links for computing but after reading a bit on database design I need to think of a column as what is most atomic from the data retrieval perspective - meaning that since I never retrieve and store the paragraphs on a separate occasion the rest of the section there's no need to store it in separate table.
Thanks for your help!