How to use arrays in MySQL

user_7773919 0
Viewed 43

I have created two tables in my WordPress database. One which holds company info (wp_companies) and the other contains products types (wp_product_types). wp_product_types is basically just a list of all the product types that a company can assign to them selves.

Database demos:

wp_companies

company_name | member_type | products
-------------------------------------
Google       | full        |
-------------------------------------
Crunchyroll  | full        |

wp_product_types

product_name    |
-----------------
Car Insurance   |
-----------------
House Insurance |
-----------------
Life Insurance  |

With the above demo tables lets say Google offers Car Insurance and House Insurance and Crunchyroll will offer all three product types. If I was able to I would simply add an array of product types into the products column in wp_companies but as far as I'm aware you can't use arrays like this in MySQL.

So my question is how can I assign multiple product types to a company?

wordpress mysql database
1 Answers

Because you cant use arrays in MySQL, I would use another table(e.g. wp_products):

company_name | product_name
---------------------------
Google       | Car Insurance  
Google       | House Insurance
Crunchyroll  | Car Insurance
Crunchyroll  | House Insurance
Crunchyroll  | Life Insurance

In this table you can apply multiple Products to one company

In addition: you can also give your company and products unique ids, which will make informations like company name/product name less redundant.

user_133950770
Related