While building a site I came across the following issue:
I want to be able to have multiple containers and within each container have ids pointing to multiple items that lie on another table. Scheme:
I am using MySQL and would like to stick with it. However I am open to other ideas.
My questions are:
- Would this be a reasonable data structure, or is there any better way to organize this data?
- If So, How would I go about implementing this idea of "array of pointers" to elements.
Possible Solution: http://tutsbook.com/mysql-foreign-keys/
Best Answer
You are conceptualizing this from something other than a relational database perspective, but it sounds like what you're looking for is a junction table (which also has several other names, all describing pretty much the same construct).
This structure allows multiple "this" to reference multiple "that" with unlimited flexibility with regard to which or how many of "this" references which or how many of "that"... you create a third table that is conceptually "between" the existing "this" and "that" tables. The juction/map/link table often has just two columns, "this_id" and "that_id" which combine to make up its primary key... and you create the "pointers" by inserting rows in that third table containing each this_id/that_id pair that reflects the way the records should be related.
You typically then would retrieve the records with a join.