Using hstore, json or joined tables in Doctrine 2?
|February 4, 2014||Posted by forumadmin under TechQns||
I am working on system for online stores. It should be common database for one or more websites. I am using PHP + Doctrine 2 (previously work with MySQL but have no problem with migration to PostgreSQL).
There is a list of products in the store. Every product has title, URL address, description, image,…
Every product can be published at one or more of these websites (for example Store 1, Store 2, Store 3).
I need to save it into one common database table (because of the store, orders and easier administration) but the products attributes can differ from one store to another. It means the product with ID 1 can be named like “Product A” at Store 1, “Product B” at Store 2 and “Product C” at Store 3. There could be SEO or translation reasons.
I need to search the product (and its variants) by its URL address.
Now I am considering the best way to make it.
1) Hstore data type in PostgreSQL. This is great function but when I think about it more and more I am not sure if it’s the best solution and there is no problem with it in the future development. The biggest con is that you have to use native query language when you want to find an item by value. It makes often bugs on developer side. And the second one is necessity of using PostgreSQL and impossible migration to another database (MySQL, MariaDB,..)
2) Json data type in PostgreSQL. I think is almost the same situation like hstore and there is no big difference.
3) Using joined table for “translations”. Probablby Gedmo Doctrine Extensions. I am afraid of high number of SQL queries and demands for this solution. There could be tens of thousands of products (and hundreds of thousands of its variants). When I join 2 tables with 100k items and search by url it can be quite slowly. This is the biggest problem I think about.
I prefer hstore but I am afraid of possible problems because this is step in the dark for me. And I am not sure with “translations” because of load despite we plan to use own server for the system.
I will be really grateful you for tips and experiences.
|Asked By – cubic||Read Answers|