CODEWITHSUNDEEP
mysqlarraysjson
Pangamma
Pangamma

Reputation: 807

How to store a 2D array in a MYSQL row?

This might not even be possible to do. I have one table that contains items. (item_id, item_name, description, json for deserializing into an actual object)

My second table contains packages. (package_id,package_name,price,description,item_list(2D array)) The 2D array needs to match item_id values to quantities.

Normally, I would simply serialize a JSON array to the item list spot. I worry about item_id values being removed or changed though. I cannot use a foreign key to have a change "cascade" or "delete" child values. Is there a way to store a kind of sub-table in a mysql row?

Let me know if my problem description is not clear. I want to make sure it is followable for anyone else who is trying to find an answer. I might include some images later if it will help.

Solution: No, there isn't a way to store a 2D array in a MySQL row. You have to use a bridge table. Oh well, at least there is a solution. A solution always exists.

Upvotes: 3

Views: 6486

Answers (3)

cocheci
cocheci

Reputation: 375

I was able to store a 2D array as a JSON object by using the index of the second dimension as a key. Here's an example of a 12 x 4 array:

{"0": "[0,0,0,0,0,0,0,0,0,0]", "1": "[0,0,0,0,0,0,0,0,0,0]", "2": "[0,0,0,1,0,1,0,1,0,0]", "3": "[0,0,0,1,1,1,1,1,0,0]"}

Upvotes: 0

user2009750
user2009750

Reputation: 3197

Well you can always store 2D array in a column of a database but there is a problem with it, i.e retrieving data from mysql you'll have to parse that column and that column of yours will contain multiple values. When you have a multivalued attribute in your table it must be migrated to another table to normalize your database design.

In your case you are looking at many-to-many relation ship i.e a package may have many items and an item may be taken by many packages. To solve many-to-many relation you have to introduce a new table usually called a Bridge table. that bridge table will take Primary Keys from both item and package tables as Foreign Keys. The reason you add this table is to remove redundancy and that is one of many things normalization is offering.

Upvotes: 3

benathon
benathon

Reputation: 7633

What you are looking for is called a many-to-many relationship. Usually you create an extra table that creates a map between the two tables. Here is one example:

Many-to-many relationships examples

Upvotes: 2

Related Questions