Table design

[ddanbe]

I have a table with nutrient data(in Dutch) it looks like this

My DB design skills are very low, I only know Mr. Codd is lurking 'round the corner somwhere.
Could anyone give me some hints on how to set up my tables or should I just usr one table?
Thanks in advance for any answer.

[diafol]

I see nothing wrong with this. All data pertains to the primary key.

[ddanbe]

I agree, but what with the yellow lines with categories like meat, fish, vegetables etc.
Should I turn those into an extra column or should they become seperate tables?

[pty]

Should I turn those into an extra column or should they become seperate tables?

They should definitely be stored in a separate table (say, food_category) and you should add an appropriately-named foreign key to the table you posted (let's called that food).

In SQL, you'd use a statement like this to create the tables with the foreign-key relationship in place, note the references line:

create table food_category (
        id     serial primary key,
        name   varchar(32) not null unique
);

create table food (
        id          serial primary key,
        category_id int references food_category(id) not null,
        name        varchar(32) not null unique,
        calories    numeric,
        cholesterol numeric,
        sugar       numeric
);

[diafol]

Ah - I didn't get what that meant. OK, you have two main options, depending on the how the data relates.

You'll need a new table called "categories" or similar with fields like...

id (PK, int, autonumber)
category_name (varchar)
...possibly others...

If your "nutrients" can belong to just one category each, then a simple extra field in the nutrients table will be fine -

id (PK, int, autonumber)
nutrient_name (varchar)
calorie (int)
...etc...
category_id (FK, int) - this is known as a Foreign Key and will link/relate to a record in the categories table.

This is pretty straightforward - so instead of having a column in nutrients with the repeated text "VLEES" or "VIS", you store 1 or 2 (or whatever the id of that category is.

However, if your nutrients can beplaced in more than one category, you'll need a "link table" as well as a categories table, but you won't need an extra column in the nutrients table. The link table, lets call it "nutrient_categories" for now, will look like this:

nutrient_id (FK, int)
category_id (FK, int)

So it will just store 2 integers per record. You can, additionally make this whole thing (two fields) a compound primary key, which ensures no duplicates e.g. (1,7) cannot be stored if (1,7) already exists.

Although this looks much more complicated than expected, it is more efficient, less prone to input errors and easily maintainable (updatable - e.g. changing the name of a category from VIS to MONSTER, heh heh). It is, believe it or not, much easier to extract data and place items into groups too.

With the first scenario (1 category to a product) - you could do this -

SELECT n.*, c.category_name FROM nutrients AS n 
    INNER JOIN categories AS c 
        ON n.category_id = c.id 
    ORDER BY c.category_name

With the second scenario (multiple categories to a product) - you could do this -

SELECT n.*, c.category_name FROM nutrients AS n 
        INNER JOIN nutrient_categories AS nc 
            ON n.id = nc.nutrient_id
        INNER JOIN categories AS c
            ON nc.category_id = c.id
        ORDER BY c.category_name

Off top of my head.

//EDIT

@pty - writing when you posted!

[pty]

Yes, you can definitely normalise further, like with the nutrients approach @diafol suggests, but you need to know the data before making a judgement on whether it's worth it.

There are advantages and disadvantages in cases like that.

[ddanbe]

Thanks, guess I can work it out for myself now. :)