Ideal storage of simple one to many relationship

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Nathaniel
Forum Contributor
Posts: 396
Joined: Wed Aug 31, 2005 5:58 pm
Location: Arkansas, USA

Ideal storage of simple one to many relationship

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...)
User avatar
Nathaniel
Forum Contributor
Posts: 396
Joined: Wed Aug 31, 2005 5:58 pm
Location: Arkansas, USA

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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".
WDPEjoe
Forum Newbie
Posts: 4
Joined: Sat May 06, 2006 4:14 pm

Post 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.
Post Reply