What database system to use? (EAV with FK?)

Question

I'm working on an app and I was wondering what sort of database would be best for me.

I'm trying to model listings but attributes/structure vary depending on locale. I'd like to compare, search, etc these listings/attributes across the different locale specific structures. There is always the option of creating a table for each locale but it'd be nice to refer to something like price = 100 instead of price_us = 100 OR price_de = 100 OR etc..

I've read a lot of the questions here regarding EAV in MySQL and it seems that it might not be an ideal solution for me (number of attributes; overly complex).

Is there anything out there that gives me the that flexibility but also something like FK constraints? (limiting to certain attributes or values?)

Answer 1

Well .. i would go with something like :

Products           Locales              Prices
----               -----------          -----------
product_id PK      locale_id PK         product_id FK
name               title                locale_id FK
descriptions                            amount
                          if needed >>  currency_id FK     

Seems like a sensible structure. For the Prices table the PRIMARY KEY would be composite.

As for selecting product with all the data:

SELECT
    Procucts.product_id
    Products.name
    Price.amount
FROM Products
    LFFT JOIN Prices USING(price_id)
    LEFT JOIN Locales USING(locale_id)
WHERE Locale.title = 'uk'