Welcome Guest, Not a member yet? Register   Sign In
Pagination of a db field, or not.
#1

[eluser]Herb[/eluser]
Sorry if the title of this post is too vague or ambiguous; but, I just couldn't find a better way to describe what I'm after. I am after some suggestions on the best way to handle my situation. Please consider that I am a fledgling with both php and CI.

Let me lay some ground work for my question:

I am attempting to create a story section of a website. A story can consist of one or more pages|parts|chapters etc. and I wish to have uri's that includes the title rather than the id.

I have seen this handled via Expression Engine by creating a single entry with tag(s) inserted into the content that indicate the break point of a page. Furthermore, inside this tag you can pass addition parameters such as page title. I don't like relying on the individual entering the story to insert this metadata into the story.

I have two options that I can see; but, perhaps there are more. This brings us to the purpose of this post: I am seeking advice on what would be my best course of action.

Option 1:

Make a table that is story titles which would contain the title, url_title, and any data that is about the story as a whole, irregardless of whether it is one or more pages.

Make a table that is story content which would contain the intro, body, and any data that is about the page|part|chapter. This table would have a foreign key pointing to the title of the story as a whole.

I would, of course build the interface that would allow the creation, editing, deletion, etc. of the story; but that issue is out of the scope of this post.

Option 2:

Make a single table that contains all information about the story. And through programming insert the necessary tag(s) into the post. Essentially hiding all this from the story author and of course the world when displaying the story.

Once again, I would build the interface to handle creation, edititng, deletion, etc. of the story.

Hopefully, I have provided enough information to have you provide some meaningful discussion and suggest an option that would be best in this case.
#2

[eluser]Cro_Crx[/eluser]
Hey Herb

I'd recommend building the tables best to represent how the users will view and edit the content. For example, if a user will go read a story and then only be able to see page1, then there isn't much point in storing the whole story in one field (as the user will only need to access page1).

There's no point in loading a 1000 word story every time a user wants to view the first 200 words etc.... When an author wants to edit a story you can obviously put together the story from the pieces and then break them up again and save in the DB. This will obviously be slower than editing one column, although i'm sure you will have users viewing articles much more than editing them (i could be wrong here though), it's something you'll need to consider.
#3

[eluser]Herb[/eluser]
Thanks for the reply Cro_Crx.

I've been leaning toward option 1, which is what you are suggesting (I assume). It seems more manageable in the long run.

For the present, there will only be one author. And yes, hopefully, there will be many more view requests than entries. Kinda a waste of time if no one reads.

The project is actually a rewrite of a site my wife has; but, I'm hoping from what I learn to grow this into a code base that I can use on a much more complex site. So, I'm putting a lot of thought into the process so I won't have to reinvent the wheel. I understand that the database is the most important part of a dynamic content site. If done wrong you can find yourself beating your head against a wall.

I think the back end is going to be the most challenging part. I want to keep it simple for the user; yet, make it's logic simple enough that I don't get lost in putting it together.
#4

[eluser]Cro_Crx[/eluser]
Sounds good.

Before designing your website you should read up on normalization if you're not already familiar with it. Although as you were mentioning foreign keys before, i'm assuming you are.
#5

[eluser]Herb[/eluser]
Yep. I actually have much more experience with database (dating back to db4) than php and have been doing a lot of research on MySql specifics.

I have been looking at how others have built systems (mostly open source), and am amazed that very few seem to be able to develop to 3NF. I guess they are afraid to do either complex sql statements or do a lot of queries. Shucks, unless you are pulling huge chunks of data per query or have badly thought out indexes, your not saving time and creating head aches when trying to manage your data.




Theme © iAndrew 2016 - Forum software by © MyBB