0

I am currently trying to design a database model to store all the possible nutrition data, but for many products there isn't as much data available as for the others, as a result many field could end up as NULL values (I do not want to use any placeholder/default values as I will need to be able to separate which ones are actually missing)

I found that PostgreSql might be a good solution for me, as I do not want to build a EAV (Entity-Attribute-Value) tables, because I have had my experience with it, it doesn't scale very well. (If "products" could have anywhere from 0 to 200 attributes, it would quickly get into tens of millions rows).

PostgreSql has composite types and you can also store them as an array, which allows me to store key value pairs easily within a single column, as a result it is easy to lookup everything related to the "product", I can easily do a join between tables and have the data available.

Now the question is:

Is it a good solution, what kind of obstacles could I face, is there a better solution to my problem?

Would having separate tables for example:

CREATE TABLE products (
    proteins_id FOREIGN KEY,
    lipids_id FOREIGN KEY,
    minerals_id FOREIGN KEY,
    vitamins_id FOREIGN KEY)

each foreign key referencing a table with possibly up to 50 null values in a row

OR

CREATE TYPE custom_type (
   field_1 INTEGER,
   field_2 INTEGER
)

CREATE TABLE products (
    proteins custom_type[],
    lipids custom_type[],
    minerals custom_type[],
    vitamins custom_type[]
)

I understand that the optimal solution might be somewhere in-between those two solution by finding a balance of some sort, but I really need some pointers on that, thank you guys! :)

EDIT: Plus another things to consider: The tables might need to be expanded quite often and it would be great if the process were least painful.

I also face the same kind of problem with other tables such as (food) "recipes" as the number of ingredients can be very varying plus I need to store the measures/quantity of the components.

  • Have you considered using a jsonb column for this? That shouldn't be your first choice, but it is very useful if you actually need that kind of flexibility. You also shouldn't be too afraid of NULL values, they're cheap to store. – Mad Scientist May 8 '17 at 17:49
  • About 80% of blog posts are very negative about NULL values, I sort of consider them acceptable, it's more of a problem actually that I can't guess every single column name I need to add, but thanks, I will read about jsonb type right away :) – Aleksei Maide May 8 '17 at 17:52
2

What you are describing is called a subtype. A subtype is a common data structure used for things that are very similar but have some differences. For example, and object Named Clothing can be used to describe many types of personal coverings but T-Shirts and Shorts have very different properties. With a Subtype, you store all the common properties in one table and have a related table of the properties that are different. That main table will have a FK to a Type table that will denote which of the subtype tables this object is.

Entitty: Clothing ClothingID ClothingTypeID -> ClothingType Name Size Color

Type Entitty: ClothingType ClothingTypeID Name

Now for the subtype tables. You will have one for each value in the Type table.

Entity: TShirt ClothingID (Subtypes always have the same primary key value as the parent object) SleeveLength ColorType

Entity: Shorts ClothingID HasPockets LegLength IsSwimSuit

  • i thought about your solution for about 30 minutes until realised that this should actually do the trick as creating postgresql type does exactly the same thing, it creates a new table under the hood, thanks for your insight ;) – Aleksei Maide May 8 '17 at 20:45
1

If you want to model an easy-to-use database for "varying amount of relations" between tables (in the RELATIONAL model), then a ONE-TO-MANY relationship is what you should consider first (just to get "your foot in the door"). Eg if your reasoning is that each PRODUCT contains one or more (chemical) COMPOUNDS, then all you need are 2 tables. You also do NOT have to store NULLs - you just insert as many COMPOUNDS as you want/need for each PRODUCT.

However, chances are that you find some compounds that are contained in MORE than ONE product - in which case you will end up with a MANY-TO-MANY relationship between PRODUCT and COMPOUND. Thus, you need to use an intersection table. This will actually do the trick. No storing of NULLs! Just PRODUCTS, COMPOUNDS, and COMPOUNDSINPRODUCT (bad name, I know! I'm sure you will find a better one).

EXAMPLE

-- ---------------------------------------------------------------------------
--  table PRODUCTS:  
--  store all products you want to track (one entry per product)
-- ---------------------------------------------------------------------------
TABLE:PRODUCTS 
pid  name
1    first product
2    second product
3    third product
4    fourth product

-- ---------------------------------------------------------------------------
--  table COMPOUNDS
--  store all known compounds (one entry per compound)
--  NOTE: you can just use a number as cid, the letters are just for
--  adding "clarity"
-- ---------------------------------------------------------------------------
TABLE:COMPOUNDS
cid  name
L1   lipid_1 (this is supposed to be the ACTUAL name of the lipid here!)
M1   mineral_1 (this is supposed to be the ACTUAL name of the mineral here!)
P1   protein_1 (this is supposed to be the ACTUAL name of the protein here!)
V1   vitamin_1 (this is supposed to be the ACTUAL name of the vitamin here!)
L2   lipid_2 (this is supposed to be the ACTUAL name of the lipid here!)
M2   mineral_2 (this is supposed to be the ACTUAL name of the mineral here!)
P2   protein_2 (this is supposed to be the ACTUAL name of the protein here!)
V2   vitamin_2 (this is supposed to be the ACTUAL name of the vitamin here!)

-- ---------------------------------------------------------------------------
--  table: "compounds in product" CIP (or: COMPOUNDMAP 
--  store known/relevant compound information
--  the combinations of pid and cid are UNIQUE!
-- ---------------------------------------------------------------------------
TABLE:CIP 
pid  cid
1    L1    -- product 1 contains L1
1    M1    -- product 1 contains M1
1    M2    -- product 1 contains M2
2    L2    -- product 2 contains L1
2    V1
3    P2
3    P1
3    V1
4    P2

When modelling it this way, you can always add on more compounds, (or compounds that are contained in a product - in case future research shows that a product contains more compounds than we thought). And: you don't need JSON, TYPEs, or arrays.

You can also combine this layout with the SUBTYPEs as @Joshua Guttman suggested. However, I think that you will get quite far with this approach.

  • Hey, thanks for the response, but this is exactly the thing I am trying to avoid, its called Entity Value Attribute and is considered an anti-pattern, the problem with this kind of solution is that it doesn't scale well, I have used a similar solution in other projects and it doesn't work out very well in the end... – Aleksei Maide May 8 '17 at 20:29
  • The critical word in your comment seems to be: similar. Notice that there are no "attribute values" stored in the CIP table. What it contains is just mappings. Also, the COMPOUNDS table does not contain references to PRODUCTS. Not sure where you detect the EAV pattern. Anyway - what I would like to ask you is: how do you deal with (or resolve) many-to-many relationships in an ERD (entity relationship diagram)? – stefan May 9 '17 at 4:39
  • I am sorry if i offended you, it might just be my own ignorance, where do you actually store the values then? If i stored the values in the last table describing relations, it would be EAV, right? – Aleksei Maide May 9 '17 at 5:41
  • You have not offended me at all :-) ! I thought that you had discovered something that I was not aware of. That's why I was asking about the M:Ms in ERDs. Also, I found your question (and your comments) quite inspiring. I have upvoted @Joshua Guttman's answer, because I thought it was closer to what you need than what I had suggested. I did not know that PostgreSQL's arrays are "self-resizing". So - when I saw that you wanted to use arrays I thought that this would lead to loads of NULLs being stored. – stefan May 9 '17 at 7:13
  • As for the EAV ( :-) ) - I found a description here: mikesmithers.wordpress.com/2013/12/22/… – stefan May 9 '17 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.