How are all you fellow programmers on this fine "Monday" morning?
I wonder if anyone can help with a database dilemma that I am having please?
DB: MySQL 5
I would really appreciate some advice on the correct avenue to take with a project I am working on.
What I have:
*I have a database that maintains a large table of articles. (similar to wordpress).
*I intend to relate a given article to another set of data. This set of data will be fairly huge.
*There maybe various sets of data that will be related.
* Is it better to contain these various large sets of data within the same database of articles, which will have a large set of traffic on it.
* Is it better to create different databases (on the same server) that relate by a primary key to the main database with the articles.
I didn't want to plough ahead with this without getting some advice or opinion from the community.
I really appreciate anyone who takes the time to offer some thought on the matter.
I don't think a separate database on the same server gains you anything, while adding extra complexity. At the most you should just need a separate table within the same DB. If there is a one-to-one relationship between article and "extra data", it should not even hurt to have it in the same table, as long as your queries which to not utilize that extra data do not select all columns ("*") and thus retrieve bytes you don't need.
If there is a one-to-many or many-to-many relationship, then you'll want a second table with a foreign key relation, or in the latter case a third table to define the relationships.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation