Do I need Multiple Tables?

[DoubleDee]

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:

- id
- pretty_title
- meta_title
- meta_description
- meta_keywords
- page_title
- page_subtitle
- written_on
- author
- body
- endnote_listing
- created_on
- updated_on

(**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.)

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?!

Sincerely,


Debbie

[guido2004]

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.

[webdev1958]

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?!

This is related to Database Normalisation.

If you decide to add categories to your articles I would suggest having a Categories table so that only valid categories are assigned to an article.

[r937]

... 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?

the latter

[DoubleDee]

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.

That doesn't answer the question I asked...


Debbie

[DoubleDee]

the latter

What happens if an "Article" is not exactly like an "Interview" is not exactly like a "Case Study"?

Then would you mess different types of data/records in one "God Table"?

For instance, most people would store "Parts" and "Customers" and "Orders" in one table even though it is possible...

Chances are - for now - an "Article" and an "Interview" and a "Case Study" are more similar than different, but that could change, thus my question...


Debbie

[r937]

What happens if an "Article" is not exactly like an "Interview" is not exactly like a "Case Study"?

Then would you mess different types of data/records in one "God Table"?

depends on the degree of "not exactly"

[guido2004]

That doesn't answer the question I asked...

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

What happens if an "Article" is not exactly like an "Interview" is not exactly like a "Case Study"?

[DoubleDee]

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

What would make you think

"Articles", "Interviews", "Case Studies", "Current Events", "Famous Cases", and "Op-Ed

would be the same thing?

The names alone imply disparate data.

I'm surprised r937 thought that should go in the same table.

It seems like a possibility, but I wouldn't have posted if it was obviously one way or the other.

If you don't understand something that I post, Guido, then just ask, and stop nipping at my heals.


Debbie

[r937]

I'm surprised r937 thought that should go in the same table.

i'm surprised you're surprised


you presented it like this --

The way I am building my pages, is to have attributes in my "Articles" table like this:

- id
- pretty_title
- meta_title
- meta_description
- meta_keywords
- page_title
- page_subtitle
- written_on
- author
- body
- endnote_listing
- created_on
- updated_on

(**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.)

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.

does an interview have a title? does a case study have meta keywords? does a current event have a body? does a famous case have a created on date?

as your very first post suggested, all of these "articles" are more similar than different

[DoubleDee]

i'm surprised you're surprised


you presented it like this --does an interview have a title? does a case study have meta keywords? does a current event have a body? does a famous case have a created on date?

as your very first post suggested, all of these "articles" are more similar than different

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?!



Debbie

[r937]

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?

yes, they should

For example, maybe an "Interview" would break up content into what the "Interviewer" and "Interviewee" say?

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.)

Or maybe a "Case Study" would necessitate breaking up the content more into sections like "Background", "Problem", "Approaches", "Final Solution"

i still see the entire case study as a block of html

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.

my advice is to go ahead and build it that way

there is no better teacher than experience

[DoubleDee]

is to go ahead and build it that way

there is no better teacher than experience

That sounds like a vote against my thoughts...


Debbie

[r937]

That sounds like a vote against my thoughts...

i would never vote against your thoughts

i am suggesting that you go ahead and build your app with separate tables for all these different kinds of content

stop agonizing over it in advance, and save your agonies for what happens after you get going

but please -- get going

don't fall victim to paralysis by analysis

[DoubleDee]

i would never vote against your thoughts

You're allowed to disagree, you know?!

i am suggesting that you go ahead and build your app with separate tables for all these different kinds of content

stop agonizing over it in advance, and save your agonies for what happens after you get going

but please -- get going

don't fall victim to paralysis by analysis

But I do that so well!!

(Help me get 100 visitors to my website and/or my clients websites every day, and I'll stop being so paranoid/calculated... )


Debbie

[webdev1958]

Post your ERD.

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.