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
A:
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.
just somebody
2009-11-17 07:34:52
yes it can be vary,one article have no photo and another maybe have 6 photos
Kaveh
2009-11-17 07:39:51
and photo table how many fields must have?
Kaveh
2009-11-17 07:41:31
+1
A:
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
Wade Williams
2009-11-17 07:45:52
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
2009-11-17 07:56:18
and ARTICLE_PHOTO is needed? can't i put ID(pk) of photo table as a FK in ARTICLE table?
Kaveh
2009-11-17 08:02:12
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?
Kaveh
2009-11-17 08:12:23
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
2009-11-17 08:15:07
"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
Wade Williams
2009-11-17 08:17:30
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?
Kaveh
2009-11-17 12:28:55
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.
Kaveh
2009-11-17 13:34:12
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.
Wade Williams
2009-11-20 07:30:32
A:
Consider this -- each article can have many photos, each photo can appear in many articles.
Damir Sudarevic
2009-11-17 13:19:06