MySQL Article Database Structure?

Question

I wanted to create an article database for my users I was wondering how should my MySQL database structure look like?

Here is how my database table structure look like so far.

CREATE TABLE users_articles (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

Answer 1

Presumably you will store your articles separately from your users (to satisfy 3NF). To that end, I'd start with something like:

Users:
    UserId int primary key.
    Other user-specific data (name, address, affiliations, ...).

Articles:
    ArticleId int primary key.
    UserId references Users(UserId).
    ArticleText varchar(big-enough-to-hold-article).

The data types for the primary keys are in your hands (they don't affect the 3NF aspect).

Whether you wish to split the article text into paragraphs or add keywords to articles and so on is expansion from that. This is where you should be starting from.

These are the things that come to mind immediately that I'd be looking at beyond the basic structure given above.

• Keywords or search terms for articles, kept in another two tables, one to hold the keywords themselves and the other to hold a many-to-many relationship between keywords and articles.
• Synopses of the articles, can simply be another column in the Articles table.
• Articles that end up needing more than the maximum space allotted, in which case the article text can be split out to another table with a foreign key reference to Articles(ArticleId) and a sequence number to order the article pieces.

Answer 2

The answer really depends on the spec of your entire application rather than just the article table.

Looking at the create statement in the question, it looks like it could be a join table, many users have many articles (many to many). In that case you may want to use only the user_id and article_id and make them the primary key together, but then where are the user and article tables and what information do you want to store in those tables?

1. Do you have at least a rough spec of how the entire application will work?
2. Are there other data elements that will relate to the articles?
3. Do you need to consider the possibility of expanding the scope of your application in the future?

This article on Database Normalization may help you further.

Answer 3

The article's text could be made searchable by using MySql's support for full-text indexing and searching But understanding the trade-offs of using such indexes is not necessarily a beginners topic.