Page 1 of 1

Ideal storage of simple one to many relationship

Posted: Sat May 06, 2006 2:37 pm
by Nathaniel
I have a couple small scripts for a FAQ/help topics type system... basically needs three things: the question, the answer, and a "related topics" (labels) function. For the labels, the simplest solution would be to have links to search the db for keywords in the topic they're reading now - "How do I make cheese?" would link to a search for "cheese", for example.

When it comes to administration, I'm the only one who will be managing the system and the simplest way to add labels would be to just comma seperate each label. That has led to a one-to-many table structure: I have tables help_topics and help_topic_labels.

My question is am I overcomplicating things :), and should I just have a "labels" field in help_topics, store the CSV as a text field, and explode() on a comma to get the array of labels I need? Or is there some reason I'm missing that two tabled one-to-many relationships are encouraged? Would that/those reason(s) be applicable in this case?

- Nathaniel

Posted: Sat May 06, 2006 4:51 pm
by timvw
- Now imagine that at a given day you want to rename the 'beautiful women' label to 'nice chicks'.. With a row for each label the UPDATE query is pretty easy... How would the updating of the field with comma's go?

- Imagine that you search for all labels that have the word 'internet'. With a row for each label the SELECT where LIKE would be relatively simple. How would the query for the field with comma's look like?

- Imagine that you have a cd collection, and you want to label it 'cure, the'.. How would you handle the , in the value?

(If you're using MySQL and you insist on the CSV column you propably want to look at the FIND_IN_SET and MAKE_SET string functions...)

Posted: Sat May 06, 2006 7:27 pm
by Nathaniel
Well, I was fulltext searching AGAINST(question, answer), not searching with LIKE by label. I should have mentioned that. The labels are more like "useful keywords to search for." So I suppose that nulls #2. Your points one and three are certainly valid...

If I wanted an easily expandable solution, I'd clearly go with the seperate table. It makes for more code, however, and I don't see how I will ever need to expand the keywords system. Even if I did, I can only make a system so expandable... I'd rather keep it simple and port everything over to a seperate table in the future than make things more complicated now...

Hmmm.

Posted: Tue May 09, 2006 1:11 pm
by GM
Personally, in cases like this I prefer to make a system as expandable as possible. timvw has given three pretty good reasons why you should do it with two tables. Plus that putting more than one value into a database field goes against normalisation rules, which is generally "a bad thing".

Posted: Tue May 23, 2006 8:07 am
by WDPEjoe
Basically, what it comes down to is that it's just as simple to code it either way, but coding it in two tables offers the big prize of extendability/flexibility (I know they are two different concepts but close enough in this context). Also, it's a step towards database normalization, which I've found is useful.