"Photo field" in "article_table" or independent table for photo?

Hi all;

I have a table for articles with several fields ,each article can have photo/photos ,is it good that have a field for photo in article_table or i make another table for photo/photos and why?

thanks

From stackoverflow

• if it's always exactly one image, then it's a matter of design. if the count can vary, then you must put it in a separate table, because otherwise you're in for trouble querying and updating the data.

  Kaveh : yes it can be vary,one article have no photo and another maybe have 6 photos

  Kaveh : and photo table how many fields must have?

• Since you mention "photos" plural, I'll assume you can have multiple photos per article.

  In that case, you'd want an association table.

  Something like:

  ARTICLE
  --------------
  ID(pk)    NUMBER NOT NULL,
  AUTHOR_ID NUMBER NOT NULL,
  TITLE     VARCHAR NOT NULL,
  CONTENT   CLOB NOT NULL
  
  ARTICLE_PHOTO
  -----------------
  ARTICLE_ID NUMBER NOT NULL,
  PHOTO_ID NUMBER NOT NULL
  
  (ARTICLE_ID, PHOTO_ID) is the PK, and both ARTICLE_ID and PHOTO_ID are FKs
  
  PHOTO
  --------------------
  ID(pk) NUMBER NOT NULL,
  PHOTO  BLOB NOT NULL
  

  Kaveh : yes but as i wrot photo/photos may be an article have no photo and another have several photos therefore can i use your design for this condition?

  Kaveh : and ARTICLE_PHOTO is needed? can't i put ID(pk) of photo table as a FK in ARTICLE table?

  Kaveh : for photo field i put the name of file and if an article has several photos i want that separate them with(,) in its culumn is this ok?

  Wade Williams : Yes, you can use this design for the condition of having either no photos with an article or several. Yes, the association table ARTICLE_PHOTO is needed. If you just put the PHOTO ID into the ARTICLE TABLE, you can only have one photo per article, unless you're going to have multiple rows for a single article in the ARTICLE table.

  Wade Williams : "article has several photos i want that separate them with(,) in its culumn is this ok?" - No, that's not OK at all. That's very poor database design. This article should help: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

  Kaveh : thanks,i read that article and very helpfull,but suppose that i put 2 fileds in article table one for one main photo that i put only one photo and the other for if there are some other photos for that article and in that field i separate with comma,do you think that this is not ok?

  just somebody : no it's not ok. you're trying to get yourself in trouble.

  Kaveh : thanks , just somebody and Wade Williams ,yes i thought maybe at first we think that it is difficult but at the later it will be simple.

  Wade Williams : It's not so much a matter of simple versus complex. Poor database design leads to inconsistent data. For example, what if in your method, someone put a comma with no second photo after it? Or what if they put the same photo twice? There's just no way to ensure valid data with that design.

• Consider this -- each article can have many photos, each photo can appear in many articles.
  
  

  Wade Williams : Just curious, what tool is used to draw this?

  Damir Sudarevic : Visio pro 2007 .