Re: news and article posts in one table

On 11/26/2011 09:45 PM, Paul M Foster wrote:

On Sat, Nov 26, 2011 at 01:26:49PM -0600, Tamara Temple wrote:

muad shibani<muad.shibani@gmail.com> wrote:

i wanna to create one table that contains both news and articles posts, they have similar columns like id, title, content, and date but they are differ in one column = the source of news or article post article has writers that have permanent names and pictures obtained from another table called writers that supposed to be left joined with the news table, while news posts simply have a source as text like AFP or Reuters and so one. How I can solve this ?

How you store things in tables can sometimes get a little tricky. One way to approach this is with normalized tables and using joins in your query like you are doing. To make this work, in your main entries table, have a field that indicates what the entry type is. If you are doing one select that gets both articles and news stories, having that extra field can help you distinguish what type it is, and which fields contain data in each record. (cf: Wordpress wp_posts table for an example of how this is done. They store posts, pages, and attachments in a single table this way. I can't say if this is a better arrangement than keeping them in separate tables.)

I've had to hack this table. It's a prime example of bad design. Take a long look at the records of this table in an active blog, with a survey of each of the fields and their values. You'll see what I mean. Paul

The Drupal approach to this problem is to have a common table for all "nodes" (our generic content object thingie), and then dependent tables for type-specific stuff. So (over-simplifying): node: id, title, type, created time, updated time, published (1 or 0) field_body: node_id, delta, value field_picture: node_id, delta, url field_source: node_id, delta, url to reuters or whatever field_writers: node_id, delta, writer name, url to writer picture // etc. That way, you can have the basic information all in one table and then specific fields can be shared by some, all, or just one node type, and all can be multi-value. It does mean loading up a full object is multiple queries, but really, MySQL is fast. You don't need to over-optimize your query count, and this gets you a well-normalized database. If you know in advance exactly what your types are going to be (in Drupal they're user-configurable), you could simplify it to something like: node: id, title, type, body. created time, updated time, published (1 or 0) node_article: node_id, writer name, writer picture url node_news: node_id, url to reuters or whatever And you can still select on whatever you need. With a LEFT JOIN, you can even get back all data on all articles of both types, and just have lost of nulls in the result set for the off-record fields. --Larry Garfield