First I’d like to acknowledge that similar questions have been asked before, but I haven’t found a question with this specific set of details asked.

I’m currently feeling quite nerdy and I was thinking about how to best implement a way to store information about products and their nutritional values.

Starting off, we could create a table for the products to store their attributes:

TABLE product {
    id INT,
    title CHAR,
    price DECIMAL,
}

Then, we can chose between several approaches regarding storing the products nutritional values (if it does have any, which it may not). We could simply create a table like this:

TABLE nutritional_values {
    id INT,
    energy_kj DECIMAL,
    energy_kcal DECIMAL,
    fat_gram DECIMAL,
    // additional fields
}

Now, lets say that we do chose this approach – keeping all the nutritional values of a product in a single table and relate them by linking them with foreign keys.

If we choose this approach, first I’d like to know your opinion on how we can link these tables. We should consider that a product may not have any nutritional values. This is because it may not be a food item, or it may be due to the fact that not all food items are required by law to have nutritional values on their packaging label.

With this in mind, which is the better way to link the tables together? Should we introduce the foreign key in the product table, and link to the nutritional_values table? If so, we’d have to allow NULL values since a record for any specific product may not exist. The alternative is to keep the foreign key in the nutritional_values table. With this approach, we don’t have to do anything specific. A nutritional_values record may not exist for a specific product.

Additionally, we may may need to store NULL values in fields where a specific nutritional value does not exist. I am guessing this could be a bit tedious since we need to handle the non-existing values in our server when retrieving an items nutritional data.

Now, keeping all the nutritional values in a single table may not be the best approach. We could opt to use a Many-to-Many relationship between the product table and a nutritional_value table, and break it up with an intermediary table. This is how I picture it:

TABLE product {
    id INT,
    // additional fields
}

TABLE nutrition {
    id INT,
    description CHAR
}

TABLE nutritional_value {
    product_id INT,
    nutritional_value_id INT
    value_per_100_grams, (INT, DOUBLE or DECIMAL? ????)
}

If you were to design such a database, how would you craft the table and set the relationships up? What are the strong- and weak points of each approach? I am not only interested in optimizing the performance of the database, but also on it’s ease of use and maintainability.

Thanks for reading and please let me know if you have any thoughts on this matter. Have a great day! 🙂

I am currently unable to make up my mind regarding which option to implement.