article table schema

Question

I need to create the typical crud app for "articles", is there a standard or sort of a best practice when it comes to this? I know every app and situation varies, but I mean generally.

Secondly, I'm confused a bit on how to handle multiple images associated with a particular article, I was thinking just img tags strewn through out the body of the article as opposed having an images table and referencing images and articles and all that. Just looking for some tips and suggestions on how to handle this kind of thing, as I'm sure most everyone here has done this multiple times.

My article definition as is:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL auto_increment,
  `issue_id` int(11) default NULL,
  `status` text,
  `updated_at` date default NULL,
  `body` text,
  `title` varchar(255) default NULL,
  `author` varchar(255) default NULL,
  `created_at` date default NULL,
  PRIMARY KEY  (`id`)
)

Answer 1

Articles table:

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `keyname` varchar(96) NOT NULL,
  `title` varchar(96) NOT NULL,
  `content` text NOT NULL,
  `tags` varchar(128) NOT NULL,
  `author` varchar(128) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime default NULL,
  PRIMARY KEY (`id`)
)

Article images:

CREATE TABLE `articles_images` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `article_id` int(10) unsigned NOT NULL,
  `url_thumb` varchar(512) NOT NULL,
  `url_big` varchar(512) NOT NULL,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `article_id` (`article_id`)
)

url_thumb - image thumbnail
url_big - big image

And you can check out Wordpress` database chema

Answer 2

If you just want to display the images and upload them with ftp the image tag thing is fine

If you want a web interface to upload them, saving the paths in the DB is very handy

You dont have to attach the images to the article per se, you could have a table that saves image paths and then use a join model or simply point to the images through an image tag

This allows you to use something as convenient as paperclip (plugin) to handle uploading and storage

Answer 3

Dublin Core is an informal standard set of attributes for documents and media. The attributes are:

• Title
• Creator
• Subject
• Description
• Publisher
• Contributor
• Date
• Type
• Format
• Identifier
• Source
• Language
• Relation
• Coverage
• Rights

There's a fair bit of thought from folks who've run document/media management applications for years behind this list. I think it's a good resource to scan down for ideas of what attributes your users might find valuable that you might miss at first (such as creator vs publisher vs contributer vs source). I wouldn't just dump all those into your application without thinking about if they're relevant.

I think you do want a table that associates images to articles. Otherwise if you need to know what images an article uses, such as for deleting an article and it's images, the only way to find the associated images is to parse the article body itself. This makes for awkward SQL using string processing functions, or forces processing in your app, both of which could be performance problems if you need to process a group of articles at once.

You could still just use image tags in the article body for laying out the images however, avoiding any more complicated custom markup system. Just write your model so that it parses out the image tags and updates the join table when you create/edit an article. A little nokogiri and attachment_fu (or their alternatives) should make this a quick job.