I am creating a home-grown Content Management System (CMS), and could use some advice on how to separate my data.
When this started, I just had “Articles”, but as things have grown, I will also have other content like: Interviews, Case Studies, Current Events, Famous Cases, etc.
The way I am building my pages, is to have attributes in my “Articles” table like this:
[INDENT]- id
pretty_title
meta_title
meta_description
meta_keywords
page_title
page_subtitle
written_on
author
body
endnote_listing
created_on
updated_on
[/INDENT]
(**NOTE: In the “body” field I just copy and paste my raw HTML code in there, so all of the content and markup are there.)
[b]Because of how my table is structured, it seems to me that I could not only have “Articles” in this one table, but also “Interviews”, “Case Studies”, “Current Events”, “Famous Cases”, “Op-Ed”, etc.
So, should I have a separate table for each type of content OR should I add a “Category” field and dump all of the content into just one table?![/b] :-/
If an article can have only one category, add a category field.
If an article can have multiple categories (many to many relationship), you’ll need an articleCategories table.
The answer was implied (and very obviously). Sorry if you didn’t understand, luckily r937 gave it to you.
Actually, I even gave you some more info just in case you left out important information about your problem like you usually do, and indeed you did as demonstrated by this question
Yes, on the surface, they are similar - if not maybe even identical.
But to follow that logic, you could say that “Employees” and “Customers” and “Contractors” are all people and therefore should be in the same table, right?
Like most business problems, I won’t know how things should be until I know how things should be?!
Today, I would say an “Article”, an “Interview” and a “Case Study” are identical and could go in the same table since each has meta-tags and I am lumping the entire content/copy in one field.
But over time things could change.
For example, maybe an “Interview” would break up content into what the “Interviewer” and “Interviewee” say?
Or maybe a “Case Study” would necessitate breaking up the content more into sections like “Background”, “Problem”, “Approaches”, “Final Solution” whereas an “Article” is pretty much just one thing?
Of course I am asking about these unknowns now, so that I don’t choose an architecture that was poorly conceived for growth and scalability.
To me, I am leaning towards have an “Articles” table, and an “Interview” table, and a “Current News” table because things are more like to diverge than converge.
Of course, 500 tables later I may hate that approach?!
you would still have the entire interview inside a block of html, and you would not want to break down and track the individual questions and answers unless each question and its answer needed to be isolated (and have its own primary key, etc.)
i still see the entire case study as a block of html
If you don’t have an ERD for your database then you will be more likely to stuff something up when you create your database and later on when you code up your back end processing. If you are sure your ERD is correct, then creating the db and the back end processing should be relatively straight forward.
You normally wouldn’t start building a house without a set of architect’s plans and so the same should apply to building a database, especially a largish one.
