1

Having two tables one that holds articles and another that holds custom fields for those articles with a relation of one article to many custom fields.

How efficient is it to do a join for each field in order to get the whole data in one go?

Or is there a better way of doing this that would allow the custom fields to be used in where clause?

I am considering this or altering the table schema to accommodate the custom fields.

transilvlad
  • 13,974
  • 13
  • 45
  • 80
  • I did what you describe in the past, and have a Article table and a ArticleCustomProp Table, both have a IDArt and a relation of one to many. If you want to filter Article table by custom properties you can simple use an IN clause, i see no problem here... If you need i give you and SQL Example of the query – ericpap Apr 07 '14 at 19:02
  • Please. That would be appreciated. – transilvlad Apr 07 '14 at 19:06
  • possible duplicate of [Entity-Attribute-Value Table Design](http://stackoverflow.com/questions/11779252/entity-attribute-value-table-design) – Marcus Adams Apr 07 '14 at 19:07
  • Nop it is not. EAV is not always the answer. – transilvlad Apr 07 '14 at 19:12

1 Answers1

0

If you need to filter articles by a custom property you can do:

SELECT * FROM Article WHERE IDArticle IN(SELECT IDArticle FROM ArticleCustomProp WHERE CustomFieldValue='value')

If you need to get and Article with all its custom properties you can do:

SELECT * FROM Article A LEFT JOIN ArticleCustomProp CP ON A.IDArticle=CP.IDArticle
WHERE A.IDArticle=15

One problem that you have here is field type. In your ArticleCustomProp you can have:

  • IDArticle (INT)

  • CustomPropertyName (STRING)

  • CustomPropertyValue (?)

The problem is what type to give to CustomPropertyValue field. If you need to mix multiple types values you have to find a generic way to save data to this field, regardless the data type. Hope it helps you. Let me know if i can assist you with anything else.

Community
  • 1
  • 1
ericpap
  • 2,917
  • 5
  • 33
  • 52
  • How would you process (display) the second one when the data from the Article table would be replicated many times? – transilvlad Apr 07 '14 at 23:20
  • What do you mean with replicated? you got more tan one Article table? – ericpap Apr 08 '14 at 12:57
  • If you want to display 30 articles on a single page including the custom fields in a table format. How would you do that due to the fact the second query will duplicate the data in the columns for each custom result. – transilvlad Apr 08 '14 at 18:43
  • If you directly show the info from the database query the data for articles will be duplicated for each article depending on the number of properties. You can avoid that showing the data on a master detail report but you need somo coding. If you have custom variable properties for each article you can't show the info as columns for each article, because the number of properties change. – ericpap Apr 09 '14 at 03:14