Methods of storing an array 2

[ddvlad]

Hello,

I am new to MySQL and, among the various documentation I have read, I have not come across any means of storing arrays as fields. For example, what would be a wise method of storing an array of integers as a field? The only way I could think of is storing a varchar of space-separated integers that would be converted in-program. However, I do not want to set off on a wrong path, so I ask for your help.

Thanks in advance,
Vlad.

--
People here are not only very knowledgeable, but also polite and helpful. My sincere congratulations to everyone.

 

[sleipnir214]

To what end are you trying to store multiple values in a single field?

To my experience, it is always more efficient to store multiple values for a single record in a field in a related table. The database engine can index the data better, and changes to the data are much easier.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ddvlad]

This particular example is based on a bookmark collection (just something to get me started). A particular bookmark should ideally have as many categories as needed. I already have a tree-like hierarchy for categories, so that one can customise them to one's needs. Would having a separate table (containing only category codes) for each bookmark work? Or am I not getting something here?

Thanks for your very fast response.

--
People here are not only very knowledgeable, but also polite and helpful. My sincere congratulations to everyone.

 

[sleipnir214]

Then use three tables.

One "bookmarks" table records a URL and its comment.

One "categories" table records a category.

An additional table that relates the other two tables. This table can have as few as two columns: one for a unique identifier in the bookmarks table and one for a unique identifier in the categories table.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[ddvlad]

Wow! I never thought of that, but then again you can plainly see I am the ultimate newbie prototype. Thank you for your valuable suggestion. Have a nice day.

--
People here are not only very knowledgeable, but also polite and helpful. My sincere congratulations to everyone.

 

[johnwm]

There is an excellent introduction to relational databases by Paul Litwin available as a free download here:

http://www.alphabetcitydataworks.com/fundamentals.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller