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:

  1. Would this be a reasonable data structure, or is there any better way to organize this data?
  2. If So, How would I go about implementing this idea of "array of pointers" to elements.

Possible Solution: http://tutsbook.com/mysql-foreign-keys/

  • What you need to do is create a foreign key to the elements table. This is a core function of any RDBMs (MySQL). This is what puts the R in RDBMs. – StanleyJohns Jun 7 '13 at 23:28
  • Ah, that's a viable approach, too, and possibly even more correct if each element is only "pointed to" by one container. I may have assumed this scenario was less simplistic that it actually is. – Michael - sqlbot Jun 8 '13 at 0:06
  • From the image, I would assume the foreign key to be on Elements and referencing the Container table. Or a junction table, as Michael's answer. – ypercubeᵀᴹ Jun 8 '13 at 8:43
  • Thanks, I was looking at the Foreign key as a possible solution. However I'm kinda curious how I would do to query Containers and get all the Container data (Name, Address, etc.) + all the Element data (Name, time,etc.) that the container points to. – Sosavpm Jun 8 '13 at 23:44
  • Actually I just found out. I'm adding what I ended up doing to the question. Thanks everyone! – Sosavpm Jun 9 '13 at 0:18

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.