CODEWITHSUNDEEP
mysqldatabasedatabase-designnosqlentity-attribute-value
RS7
RS7

Reputation: 2361

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

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?)

Upvotes: 0

Views: 338

Answers (2)

il0ng
il0ng

Reputation: 194

EAV modelling is considered an sql anti-pattern and for a host of valid reasons although it is workable and fairly common place in certain sectors ie clinical systems. However it goes against a number of principles that relational databases are based on (hence the term anti-pattern) and adds complexity/overhead to queries whilst making it difficult to maintain the relational context of the data.

Perhaps a few years ago there was little choice but to implement this pattern in MySQL and indeed I have worked on a system that chose this approach. However 12 months ago we switched to a schemaless backend (mongoDB), which is a natural fit for storing records with variable attributes.

IMHO - If you're considering implementing an EAV pattern in MySQL consider a schemaless DB architecture first.

Upvotes: 0

tereško
tereško

Reputation: 58444

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'

Upvotes: 2

Related Questions