Table Design: CSV field values a bad idea? 1

[VBAjedi]

Hi all!

I'm designing what should be a simple table to track items that can have relationships with other items. Am I going to create headaches for later if I store a list of items each item is related to as a CSV list in a field (as opposed to creating another table to store those values as seperate records)? Example:

Item_Num     Item_Description    Related_Items_CSV
1            Some Item           3,4,11,13
2            Another Item        4,6,8,9
etc. . .

It comes down to whether it is going to be more work to parse that field looking for a value, or link to a seperate table that exists for the sole purpose of storing those values, as follows:

Item_Num    Related_Item
1           3
1           4
1           11
1           13
2           4
etc. . .

Thanks for any input you care to share!


VBAjedi

 

[sleipnir214]

I very strongly recommend that you use the latter schema.

MySQL could not use indeces efficiently with the first idea, and there's no point running a relational database if you're not going to use the relational capabilities.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[VBAjedi]

I had a hunch I was going to get that advice!

;^)

I'm pretty new to database design, and I guess I'm just a little leery of creating a new table every time I run into something that doesn't quite seem to fit into one of my existing tables.

I'll trust your advice, though. Have a star!

Thanks,

VBAjedi

 

[sleipnir214]

[Puts on guru hat and fake beard]:
As your secret guru mentor, I now assign you the following mantra. Repeat it until you have attained a state of nirvana:

"My database will be relational."


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!